Simple IF statement question

[maggiegreen]

This may seem pathetically simple compared to other questions on this board, but I am a novice in need of help.

I have a spreadsheet that has the following type of information:
G4: 5.5% H4: 60% J4: $150,000
and in K4 I wrote this IF statement:
=IF(H4>1%,(J4*G4)*H4) and that works just fine for Row 4. And I copied the formula to other rows and it works OK if the % number in column H is greater than 1%. But the variable in this case is that sometimes the value in the H column will be 0% and when that happens, my IF statement returns a FALSE value. What I need to have happen is when the H column has a 0% value, to only multiply the values in the J and G columns and NOT multiply by the H column (because if it is 0% anything times 0 = 0 and that is not what I want).

Sooo - I suspect I need to nest another IF statement in here, but that is beyond my ability to work out on my own. Let me say what the numbers mean, just in case I'm approaching this from the wrong direction:
J4 is the net sale amount. G4 is the commission rate on the sale amount. H4 is the discount rate (that is applied only sometimes). I need K4 to show me the total payment due to the salesperson: sale amount times commission rate (times discount, if the discount exists).

I thank you in advance for your generous help with my perplexing issue!

[Vas]

Try

=IF(H4>1%,(J4*G4)*H4,(J4*G4))

[Asala42]

Howdy!

G4: 5.5% H4: 60% J4: $150,000
and in K4 I wrote this IF statement:
=IF(H4>1%,(J4*G4)*H4) and that works just fine for Row 4. And I copied the formula to other rows and it works OK if the % number in column H is greater than 1%. But the variable in this case is that sometimes the value in the H column will be 0% and when that happens, my IF statement returns a FALSE value. What I need to have happen is when the H column has a 0% value, to only multiply the values in the J and G columns and NOT multiply by the H column (because if it is 0% anything times 0 = 0 and that is not what I want).

What happens when H2 = 1% exactly?

Can you get away with:

=IF(H4=0,1,H4)*(G4*J4)

Adam

(misread the post thus the edit)

[ This Message was edited by: Asala42 on 2002-05-22 05:48 ]

[sann0638]

The formula is:

=IF(H4>1%,(J4*G4)*H4,J4*G4)

Your original equation is evaluated on True, and the second is evaluated on False.

[Ian Mac]

On 2002-05-22 05:45, Asala42 wrote:
Howdy!

What happens when H2 = 1% exactly?

Can you get away with:

=IF(H4=0,1,H4)*(G4*J4)

Adam

(misread the post thus the edit)

[ This Message was edited by: Asala42 on 2002-05-22 05:48 ]

What does happen when H4=1%?

If your looking to do the calculation if the value not = 0

then you could use:

=((H4<>0)*J4*G4*H4)+((H4=0)*J4*G4)

No if needed. With reference to what you said about something times 0 is 0.

I'll explain. Excel sees TRUE or FALSE as a 1 or 0. Therefore you can use this to test cells like the above formula, only one of the following - H4<>0 or H4=0 will return TRUE i.e. 1 so which ever formula that follows FALSE or 0 will return 0. The other one will Multiply by 1 and return a result.

I think that's clear.

Credit to Aladin for bringing this infomation to my attention in a number of posts a while back.

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-22 06:01 ]

[Vas]

There's another aspect to it.
What's easiest to understand?

=IF(H4>1%,(J4*G4)*H4,(J4*G4))

or

=((H4<>0)*J4*G4*H4)+((H4=0)*J4*G4)

I think the first version definitely is the clearest. Maybe should be written as:

=J4*G4*IF(H4>1%,H4,1)

[Aladin Akyurek]

=J4*G4*(IF(H4,H4,1)

which is almost like Adam's .

I assumed that this formula is copied down without locking/freezing of any of the cells involved.

[maggiegreen]

Exactly what I needed. Thank you so, so much.

I do have another question, but I'll start a new post for that.

-Maggie