#### Function if() - Is there a bug???

Hello!

***If I use If()
=IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82)

The formula box shows the results below:
logical_test = {FALSE;TRUE;TRUE}
value if true = 0
Value if false = 0.8
={0.8;0;0}
=0.8

But I have a problem!!!
My cell shows = 0  (and this is not due to any kind of formatting)

***Now I have the same problem is the logical test is (as shown in th
formula box)
{TRUE;FALSE;FALSE}
={0;0;0.8}
=0.8

My cell actually shows =0

***However if my logical test is (as shown in the formula box)
{TRUE;FALSE;TRUE}
={0;0.8;0}
=0.8

Then my cell will finally shows the right number =0.8

Am I missing something here? :confused:
How come the formula box gives me the correct result but doesn't sho
it the right way on my cell?

Benoi

--
Message posted from http://www.ExcelForum.com

 0
2/3/2004 11:06:05 PM
excel.misc 78881 articles. 5 followers.

8 Replies
532 Views

Similar Articles

[PageSpeed] 37

Hi
have you entere your formulas as array formulas (CTRL+SHIFT+ENTER).
from your results you may have entered them just with a single 'ENTER'.
These kind of formulas have to be array entered.
Frank

> Hello!
>
> ***If I use If()
> =IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82)
>
> The formula box shows the results below:
> logical_test = {FALSE;TRUE;TRUE}
> value if true = 0
> Value if false = 0.8
> ={0.8;0;0}
> =0.8
>
> But I have a problem!!!
> My cell shows = 0  (and this is not due to any kind of formatting)
>
> ***Now I have the same problem is the logical test is (as shown in
the
> formula box)
> {TRUE;FALSE;FALSE}
> ={0;0;0.8}
> =0.8
>
> My cell actually shows =0
>
> ***However if my logical test is (as shown in the formula box)
> {TRUE;FALSE;TRUE}
> ={0;0.8;0}
> =0.8
>
> Then my cell will finally shows the right number =0.8
>
> Am I missing something here? :confused:
> How come the formula box gives me the correct result but doesn't show
> it the right way on my cell?
>
> Thank you for your help,
> Benoit
>
>
> ---
> Message posted from http://www.ExcelForum.com/

 0
frank.kabel (11126)
2/3/2004 11:14:49 PM

Yes I tried with and without arrays and in both cases I have the same
problem.

---
Message posted from http://www.ExcelForum.com/

 0
2/3/2004 11:39:05 PM
Hi
try an alternative solution:
=SUMPRODUCT((N82:N84<>"N/A")*(O82:O84))/SUMPRODUCT((N82:N84<>"N/A")*(M8
2:M84))*S82
though I'm not quite sure what you want to achieve with your original
formula

HTH
Frank

=IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82)
>
> Yes I tried with and without arrays and in both cases I have the same
> problem.
>
>
> ---
> Message posted from http://www.ExcelForum.com/

 0
frank.kabel (11126)
2/3/2004 11:55:27 PM
If you select your cell, hit F2 (to edit) and F9 to show what it evaluates to,
what do you see in the formula bar?

What do you see in the Cell?

If it shows as 0.8 in the formula bar, but 0 in the cell, try widening the
column.

If the cell is formatted as General, excel will let you do pretty much do
anything to the columnwidth.  (But if you format it as (say) Number, 1 decimal
point, you'll get ####'s when the column is too skinny.)

"hochedez <" wrote:
>
> Hello!
>
> ***If I use If()
> =IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82)
>
> The formula box shows the results below:
> logical_test = {FALSE;TRUE;TRUE}
> value if true = 0
> Value if false = 0.8
> ={0.8;0;0}
> =0.8
>
> But I have a problem!!!
> My cell shows = 0  (and this is not due to any kind of formatting)
>
> ***Now I have the same problem is the logical test is (as shown in the
> formula box)
> {TRUE;FALSE;FALSE}
> ={0;0;0.8}
> =0.8
>
> My cell actually shows =0
>
> ***However if my logical test is (as shown in the formula box)
> {TRUE;FALSE;TRUE}
> ={0;0.8;0}
> =0.8
>
> Then my cell will finally shows the right number =0.8
>
> Am I missing something here? :confused:
> How come the formula box gives me the correct result but doesn't show
> it the right way on my cell?
>
> Thank you for your help,
> Benoit
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com
 0
ec35720 (10082)
2/4/2004 1:56:05 AM
I can duplicate your results *exactly* ... BUT ... only when the formula is
*not* array entered !

Are you *sure* that you have correctly entered by using CSE <Ctrl> <Shift>
<Enter> ?
Is your formula enclosed in curly braces {} after you used CSE?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23T0MHuq6DHA.2628@TK2MSFTNGP10.phx.gbl...
Hi
have you entere your formulas as array formulas (CTRL+SHIFT+ENTER).
from your results you may have entered them just with a single 'ENTER'.
These kind of formulas have to be array entered.
Frank

> Hello!
>
> ***If I use If()
> =IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82)
>
> The formula box shows the results below:
> logical_test = {FALSE;TRUE;TRUE}
> value if true = 0
> Value if false = 0.8
> ={0.8;0;0}
> =0.8
>
> But I have a problem!!!
> My cell shows = 0  (and this is not due to any kind of formatting)
>
> ***Now I have the same problem is the logical test is (as shown in
the
> formula box)
> {TRUE;FALSE;FALSE}
> ={0;0;0.8}
> =0.8
>
> My cell actually shows =0
>
> ***However if my logical test is (as shown in the formula box)
> {TRUE;FALSE;TRUE}
> ={0;0.8;0}
> =0.8
>
> Then my cell will finally shows the right number =0.8
>
> Am I missing something here? :confused:
> How come the formula box gives me the correct result but doesn't show
> it the right way on my cell?
>
> Thank you for your help,
> Benoit
>
>
> ---
> Message posted from http://www.ExcelForum.com/

 0
ragdyer1 (4060)
2/4/2004 3:08:34 AM
Thank you all for your help. I still haven't find the solution to m
problem and this is getting really time consuming!!! I probably gonn
have to rewrite the whole thing, but it is a huge spreadsheet
auditing tool - used by many people and it would be a mess if I need t
do that :(

*First here is what F9 shows: {0;0;0.8} in my result cell
And the cell shows 0.0 (while I would expect 0.8)

My formula again: =IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82)

Here is what I am trying to achieve with this formula:
- I have a table where people have to enter either a percentage o
"n/a"(N82:N84) in response to some questions (this are the only cell
people interact with).

- For each of these numbers (or "n/a") corresponds a score(O82:O84)
This score is the result of  the percentage entered (N82:N84) * the ma
score (M82:M84) you can get (which is either equal to (L82:L84) or  "-
in case you entered "n/a")

- (L82:L84) is the max score for each question. It's a straigh
number.

- Finally, S82 is the max total score you can get and is calculate
with : =Q82*(SUM(M82:M84)/SUM(L82:L84))  (where Q2 is just a weight)

To make it simple, I just want Excel to adapt is calculation to th
"n/a" questions.

I hope i'm being clear.
I could use CSE for each of this calculation (and indeed I did try) bu
it doesn't solve my problem (see the first mail).

Thank you so much if you can help!!!

-----
PS: if this help, the O column is calculated with:
082=IF(N82="N/A","-",N82*L82)
and the M column with:
M82=IF(N82="n/a","-",L82

--
Message posted from http://www.ExcelForum.com

 0
2/4/2004 4:29:20 PM
Frank,

BTW the formula

=SUMPRODUCT((N82:N84<>"n/a")*(O82:O84))/SUMPRODUCT((N82:N84<>"n/a")*(M82:M84))*S82

works well as long as I have no "n/a" in my columns...
You pretty much guessed what I am trying to do... If I could get thi
sumproduct to work, it might be a solution...

Cordially,
Benoi

--
Message posted from http://www.ExcelForum.com

 0
2/4/2004 5:14:40 PM
Hi Benoit
I think not the "n/a" is the problem but the entry of "-" in column M.
Try the following
=SUMPRODUCT(--(N82:N84<>"n/a"),(O82:O84))/SUMPRODUCT(--(N82:N84<>"n/a")
,(M82:M84))*S82

If this helps o.k. if not you may send me your spreadsheet and I'll
have a direct look at it
Frank

> Frank,
>
> BTW the formula
>
>
=SUMPRODUCT((N82:N84<>"n/a")*(O82:O84))/SUMPRODUCT((N82:N84<>"n/a")*(M8
2:M84))*S82
>
> works well as long as I have no "n/a" in my columns...
> You pretty much guessed what I am trying to do... If I could get this
> sumproduct to work, it might be a solution...
>
> Cordially,
> Benoit
>
>
> ---
> Message posted from http://www.ExcelForum.com/

 0
frank.kabel (11126)
2/4/2004 5:42:49 PM

Similar Artilces:

Basic Question on autocomplete function
Hi everyone, my first time on the forum and my first thread. I apologize if this is already located on the forum here. I tried searching, but came up with many unrelated threads so I thought I'd give this a shot. I am trying to auto complete a link to another worksheet by corner dragging a formula over a ROW in one worksheet FROM data in the COLUMN of another worksheet. In other words: Worksheet 1: E9, F9, G9, should point to (respectively), Worksheet 2: C2, C3, C4 The auto complete formula function does not recognize what I'm trying to do. And I have to do this with t...

Custom xpath function, ResolveFunction called but not Invoke...
Hi... We have an XsltContext object on our code to support some custom functions. I just went to add one and oddly I find XsltContext.ResolveFunction() is being called but Invoke() isn't. ResolveFunction returns return new XPathExtensionFunction(name, 2, 4, new XPathResultType[] { XPathResultType.String, XPathResultType.String, XPathResultType.String, XPathResultType.Boolean }, XPathResultType.Boolean); where class XPathExtensionFunction : IXsltContextFunction. From what's returned, I see that the ReturnType property is called a couple times but it's never followed up wit...

Can anybody reproduce this bug?
I use a web page that allows me to generate a comma delimited file that opens up in Excel. When this is generated the worksheet tab appears with an invalid name - it contains a ] symbol. I am unable to rename this tab - right click & rename tells me that the name is invalid and will not alloe me to change the name. If I save the file to disk however, I am then allowed to change the name to something valid. Excel 2k3 -- Thanks "He had had his immortality thrust upon him by an unfortunate accident with an irrational particle accelerator, a liquid lunch and a pair of rubber bands....

entering a function after dialog is displayed
I was wondering how I call a function after the main dialog is displayed. I am not using any buttons or hitting any keys at this time. I can probably use a time to wait before entering the function, but I was wondering if there is a better way. Z.K. Z.K. wrote: > I was wondering how I call a function after the main dialog is displayed. I > am not using any buttons or hitting any keys at this time. I can probably > use a time to wait before entering the function, but I was wondering if > there is a better way. Z.K. Have the dialog use PostMessage() to post a mess...

EXCEL BUG (in a graphic) !?????????
Hi all, I have a problem I cannot solve.. I think it is an EXCEL BUG !! Could somebody have a quick lok at my spreadsheet.. it should take only few minutes... I explain briefly my problem : I have 2 series on the same graph. I want to change the shape of specific points : put a big green square when there is a "B" flag, or put a Big red square when there is a "S" flag (see the spreadhsset enclosed) Everything is fine for the second serie but the first one generate completely random markerstyle and color instead of a red or green square ! I am calling the same function fo...

COUNT function and errors within it
I have 2 spreadsheets - "A" and "B". "A" is the master spreadsheet and all cells are locked. Information displayed on "A" is enterd via "B" "A" has 5 columns with formulas in and 10 data columns that display information entered from "B". 1 of the 5 formula columns in "A" has a "COUNT" formula that counts the 10 data columns when a value is recorded from when the user enteres data from "B". The 10 data columns on "A" have had: "=spreadsheetA!A1", "=spreadsheetB!B1"...

Expressions in function arguments
Is there a way to make this statement legal? =IRR((C5-B5;C6-B6;C7-B7;C8-B8)) It looks like expressions as arguments are not allowed. ";" is used instead of "," due to regional settings. I don't suppose this would be satisfactory?: =IRR(C5:C8-B5:B8) -- Jim Rech Excel MVP "Allan" <allan.goss@gmail.com> wrote in message news:74029d99.0410060754.76b70ff4@posting.google.com... | Is there a way to make this statement legal? | =IRR((C5-B5;C6-B6;C7-B7;C8-B8)) | | It looks like expressions as arguments are not allowed. | | ";" is used instead of ...

how to call javascript function in vbs ?
Hi, After load page with Set ie = CreateObject("InternetExplorer.Application") ie.navigate "http://www.random_web.com" i want call function javascript in this page, how to ? Thx On Mar 30, 2:52=A0pm, at <a...@noemail.fr> wrote: > Hi, > > After load page with > > Set ie =3D CreateObject("InternetExplorer.Application") > > ie.navigate "http://www.random_web.com" > > i want call function javascript in this page, how to ? > > Thx Set ie =3D CreateObject("InternetExplorer.Applicati...

Adding a Field to a Function
This function gives the ,father,mother.age,sex to a horse if he has no name or if named shows his Name, I want to add one field to both Names the field is called "Extra" Thanks for any help ..................Bob Function funGetHorseName(lngInvoiceID As Long, lngHorseID As Long) As String Dim recHorseName As New ADODB.Recordset, strAge As String, strName As String recHorseName.Open "SELECT * FROM tblInvoice WHERE InvoiceID=" _ & lngInvoiceID & " AND HorseID=" & lngHorseID, CurrentProject.Connection, adOpenDynamic, adLockOptimistic If r...

Lookup functions #3
I have a file with two spreadsheets containing data. One contains a list of user defined entries, the other I want to summarise specific fields based upon the original entries in S/sheet 1. I am trying to use the lookup function to enter in S/sheet 2 the value driven by a field in s/sheet 1. Eg, S/sheet 1 has a client reference, names and other details. On S/sheet 2 I only want the client reference, name and one other field taken from fields in S/sheet 1. ie. Lookupif(A1="1")then return value in B2 Something messy like that! Something like =VLOOKUP(A1,Sheet1!A1:E20,2,F...

bug when setting the number of records to 250
if i set the views to display 250 records, i cannot move on the second page of the view (the paging does not work). This does not happen on 100 records. How can i fix it ? MS CRM 3.0 Hi George, Make sure you have more then 250 records. -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. uMar Khan :: Freelance Consultant Email Contact :: imumar at gmail dot com "George" wrote: > if i set the views to display 250 records, i cannot move on the second page > of the view (the paging does not work). This does not happen on 100 records. &...

ActiveX control functionality
I need to apply some tcp communication capability to an ActiveX control and I don't know if it will work. specifically, it should implement tcp server that listens on some port and do something when client connects. Is that possible? Thanks Gino In addition to my previous question, Suppose the Activex control works fine in my Html page (nomatter what its actual functionality), what happens to its functionality when the user closes the browser, or just jumps to another page? Does it stay "alive" by its own? Who is responsible to close the Activex control and is it posible...

Case function substitute
Does anyone have any semi-easy to follow VBA code they have written to create a Case function? I think I understand most of it, but seeing code I know functions properly will probably tie up some loose ends. thanks! Here's a small example: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Cells.Count > 1 Then Exit Sub On Error GoTo ws_exit: Set rng = Application.Intersect(Target, Me.Range("a:a")) If rng Is Nothing Then Exit Sub With Target Select Case LCase(.Value) Case I...

Refer a column in the 'sumif' function
Hi, I am trying to supply column values to the sumif function, but have not had any success yet. Here's what the macro coded- Range("B34").Select ActiveCell.FormulaR1C1 = _ "=SUMIF('LCY Balances'!C6,'By Type'!RC1,'LCY Balances'!C7)" if simply used in excel this would read as - =SUMIF('LCY Balances'!\$F:\$F,'By Type'!\$A34,'LCY Balances'!\$G:\$G) What I am trying to do is - to make the sumif function sum up values in sheet "By Type" in the column referred to by a number in row32 of the...

What is the return value of Access SQL Max function?
Hi I use Visual C++ MFC 4.2. I need to get the last support_code from the table so I make query like this: CString sSQL; sSQL.Format( "SELECT MAX( support_no ) AS \"last\" FROM history WHERE support_no LIKE \'%s*\'", sYear ); I have tested it in the MS Access and it worked fine. It should return Support Code in the form like this: "S08-00012" so it has S and - characters inside. Now. the problem is from the programming side. rs.Open( CRecordset::forwardOnly, ( sSQL ) ); CString sSupportNo; CString sNextCounter; if( !rs.IsEOF() ){ rs...

Need function suggestion
I need some help in getting Excel to automatically give me some data from another portion of the spreadsheet. Scenario: I have a schedule matrix made in Excel similar to this: A B C D 1 Emp | 9am | 10am | 11am | ------------------------------- 2 Bob | | 1 | 1 | ------------------------------- 3 Tom | 1 | 1 | | ------------------------------- For this example, I have cut the work day down to 9am to 11am, but it is normally 9am to 11pm. For each hour that the Employee works, a "1" is entered in for that hour...

display bugs with high screen resolution
Hi all, One of my customer is going to use MSCRM 1.2 on laptops. The users work with high resolution (1900*1400, 1680*1050...) Some views in MSCRM are curtailed (action menus, lists of values in picklists fields...), and the result is that certain values can't be selected. I couldn't find the right parameter (in IE or in advanced display properties) to solve that problem Thank you Lo�c 1064 by 768 pixels "Loïc" wrote: > Hi all, > > One of my customer is going to use MSCRM 1.2 on laptops. > The users work with high resolution (1900*1400, 1680*105...

functions #5
I have 2 columns. I want to add the two numbers together and have the total put in a third column.Also have a total at the bottom of the 1st column & 3rd column. Like this: 1 + 1= 2 1 + 1= 2 1 + 1= 2 -------------- 3 6 Any help? In C1 enter =A1 + B1 and drag/copy down to C3. In A4 enter =SUM(A1:A3) or highlight A1:A4 and hit the SUM(sideways M) button on Toolbar. Copy to C4 Adjust columns and cell references to suit. Gord Dibben Excel MVP On Sat, 2 Oct 2004 16:55:02 -0700, KissFreak <KissFreak@discussions.m...

command or function for POS
I am simulating a Point of Sale (POS) program in Excel 2003. Is there a command or function that can be used to open a Cash Register with a macro? ...

List of built in functions
Does anyone know where I can get a list of all built in functions in access along with a description of usage? On Mon, 21 Jan 2008 08:41:01 -0800, juliejg1 wrote: > Does anyone know where I can get a list of all built in functions in access > along with a description of usage? Did you look in VBA help? Search for function -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Your success with this will vary with the version of Access. A2007 will give the best results and A2003 has been updated also as long as you allow help to search the web. AXP and...

Needed Macro and Function Help
Using Excel 2003 I have 2 Workbooks. MY GOAL... WB1, worksheet "Input Data" needs to search for an entry in column B of WB2, worksheet "Data Sheet". If the value IS FOUND, then I need to replace the entire row with new data from WB1 into a destination location in WB2. If the invoice number IS NOT FOUND, then I need to add a complete new entry into WB2. I already have code for WB2 (although currently used in a macro and not a function) that will perform the search and tell me the row/column location an entry is found at, or not at all. MY NEEDS: 1) In ...

Bug in DRP with monthly payments??
I have an annoying problem with the debt reduction planner that has been around for at least a few versions. I am currently using v2006. If I go into the debt reduction planner, then select base my plan on: what I want to pay each month and enter anything other than the minimum required payment, I get some crazy results for the payment schedule chart. For example, it tells me I need at least \$2500 for debt payments. If I put that amount in something larger, say \$3000, then for the first month it applies \$20,000 to debt and the next month like \$10,000. Where does it come up with the...

Outlook XP / Preview Pane / Blank Email bug found
I have found a bug with the preview pane in Outlook XP. I have documented the bug on my site and put up some screenshots. Essentially, a long Cc: line in an email, if clicked, could cause the message body to disappear. Here is the URL: http://www.redwoodsoft.com/~dru/tech/outlook.html It only affects Outlook XP and I could find no service packs that address it. dru ...

Function to find expired information
I am trying to create a function which will let me know when a particular date has passed or expired. For example, a person has insurance which expires on 4/30/06 - I want something that will let me know on 4/31/06 that that person's insurance is expired. Maybe also create a macro that will automatically filter all the expired's. Is there anyway to do this? I tried VLOOKUP, I tried an IF function, but I'm just not sure exactly how to write this. Thanks a million! take a look at conditional formatting: Select the column with the dates Format>Conditional Formatting ...