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

[hochedez]

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?
How come the formula box gives me the correct result but doesn't sho
it the right way on my cell?

Thank you for your help,
Benoi

 

[Frank Kabel]

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

 

[hochedez]

Thanks for your help.

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

 

[Frank Kabel]

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)

 

[Dave Peterson]

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.)

 

[RagDyer]

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!
-------------------------------------------------------------------

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

 

[hochedez]

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!!!

 

[hochedez]

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

 

[Frank Kabel]

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