NESTED IF FUNCTIONS

  • Thread starter EuGene C. White
  • Start date
E

EuGene C. White

I can't get the following formula to work beyond the first part. After that
it returns "FALSE". What am I doing wrong?
=IF((D443)="F",SUM((Test_Full_Price)*(Test_Bought)+((Test_Bought)*(Test_Cupon)),IF((D443)="CC",SUM((Test_Club_Price)*(Test_Bought)+((Test_Bought)*(Test_Cupon))))))
 
B

Biff

As written, there is no value_if_false argument so it defaults to FALSE.

You have it written so that the second IF is an argument to the first SUM
function. Is that really what you want?

Maybe this:

=IF(D443="F",SUM((Test_Full_Price)*(Test_Bought)+(Test_Bought)*(Test_Cupon)),IF(D443="CC",SUM((Test_Club_Price)*(Test_Bought)+(Test_Bought)*(Test_Cupon)),"not
defined"))

Biff
 
E

EuGene C. White

Biff,

Thank you. That helped. I guess the answer to your question is no. It is
two different parts.

In cell J443, I have the followng formula, which works fine. What I am
trying to do is expand the functionality to include cupons and/or sales.

=IF((D443)="CC",SUM((Test_Full_Price)*0.855),IF((D443)="F",SUM((Test_Full_Price)*0.855),IF((D443)="N",0)*IF((D443)="",0)))

This formula checks for CC (for club card), F (for full price), N (for
non-club card items) and a blank cell. I am trying to build on that formula
to also include cupons and/or sales. Your original post was a start. I will
work with that one and go on.

Again,

Thank you very much,

Gene
 
J

joeu2004

EuGene said:
In cell J443, I have the followng formula, which works fine. What I am
trying to do is expand the functionality to include cupons and/or sales.
=IF((D443)="CC",SUM((Test_Full_Price)*0.855),IF((D443)="F",SUM((Test_Full_Price)*0.855),IF((D443)="N",0)*IF((D443)="",0)))
Click to expand...

You really did not learn anything. As Biff pointed out, if you want to
avoid surprising "FALSE" results, __always__ have a "value_if_false"
(3rd) parameter in the IF() function. There are two instances where
you might truly want FALSE. But even in those case, simply write FALSE
as the 3rd parameter, if it makes it easier for you to remember to
always write a 3rd parameter otherwise.

The above formula can be simplified as follows (correcting some
presumed typos):

=IF(D443="CC", Test_Club_Price*0.855, IF(D443="F",
Test_Full_Price*0.855, IF(D443="N", something, 0)))

Replace "something" with an appropriate value or expression. You wrote
zero where I wrote "something"; I presume it was intended to be a
placeholder. But if you truly want zero both when D433="N" and when
D433 is blank (or anything else), you could simplify the formula even
further as follows:

=IF(D443="CC", Test_Club_Price*0.855, IF(D443="F",
Test_Full_Price*0.855, 0))

The point is: it is better not to test for the "last" condition
(D443="" in your case), unless you want to provide an error indication
(yet-another "value_if_false" parameter). Simply provide a catch-all
value as the last "value_if_false" parameter to cover the case where
D443 is none of the previous interesting cases.

Some other lessons to learn:

1. Avoid unnecessary parentheses. It will minimize typos (incorrectly
balanced parentheses). You __never__ need to put parentheses around a
cell reference. Whether or not to put unnecessary parentheses around
an entire expression or sub-expression is a matter of taste (and the
subject of endless debate).

2. Only use SUM() to compute a sum of its parameters or a range; and
even then, it might be more readable to compute simple sums as
expressions of the form A+B. Expressions do __not__ need to be the
parameter of some function. You __can__ write arithmetic expressions
directly, as I have demonstrated above.
 
M

macropod

Hi Eugene,

From what you've described, I think your formula could be simplified down
to:
=(IF(D443="F",Test_Full_Price,IF(D443="CC",Test_Club_Price,0))+Test_Cupon)*T
est_Bought

Cheers
PS: Coupon?
 
Top