NESTED IF FUNCTIONS
 

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)*(Tes t_Cupon))))))
--
/s/ Gene

NESTED IF FUNCTIONS
 

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_Cupo n)),"not
defined"))

Biff

"EuGene C. White" wrote in message
...
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)*(Tes t_Cupon))))))
--
/s/ Gene

NESTED IF FUNCTIONS
 

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((D 443)="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

--
/s/ Gene


"Biff" wrote:

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_Cupo n)),"not
defined"))

Biff

"EuGene C. White" wrote in message
...
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)*(Tes t_Cupon))))))
--
/s/ Gene

NESTED IF FUNCTIONS
 

EuGene C. White wrote:
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((D 443)="F",SUM((Test_Full_Price)*0.855),IF((D443)="N ",0)*IF((D443)="",0)))

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.

NESTED IF FUNCTIONS
 

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_Cl ub_Price,0))+Test_Cupon)*T
est_Bought

Cheers
PS: Coupon?

--
macropod
[MVP - Microsoft Word]


"EuGene C. White" wrote in message
...
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((D 443)="F",SUM((Test_Full_Pr
ice)*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

--
/s/ Gene


"Biff" wrote:

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

"EuGene C. White" wrote in
message
...
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_Cupo
n)),IF((D443)="CC",SUM((Test_Club_Price)*(Test_Bou ght)+((Test_Bought)*(Test_
Cupon))))))
--
/s/ Gene