Nested "IF" statements (help comparing cells before calculating an answer)

[gsacorp]

Hi All,

I've got a single-tab, 30+ column, Excel sheet with numbers (product cost, shipping, retail, etc.). These cells are both manually and simple-formula populated.

What I am trying to do is compare the "original" retail price with the actual "sold" price, THEN before populating a "differential" cell, to compare the original "projected profit" to the "actual realized profit" ~ IF the "actual profit" is lower AND the "difference between the original and actual "sold" price is <=0, then return zero, otherwise calculate how much actual profit was lost:

I think the logic is:
D=original price
F=actual price
J=original profit
K=actual profit

1) Compare J & K (profit) on the same row
2) IF there is a difference between J & K, then I want the formula to look at other cells D & F (price) to calculate the difference between J & K as it relates to D & F as follows:
3) IF J=K then I want the formula to return a zero
3) IF J<K AND D>F then I want the formula to return a zero
4) IF J<=K AND D<=F then I want the formula to return the number
4) IF J>=K AND D>=F then I want the formula to a zero
5) IF J>=K AND D<=F then I want the formula to return the number

What I tried...
a) IF(X1>=Y1,Y1-X1,0), this works for the profit-only comparison (J & K) in the case of >=, it does not address the then necessary comparison of D & F at which point I'm out of game.

I hope this is clear.

TIA,

gsacorp

 

[Nogslaw]

This is not a difficult concept, and there are many people who can help you with nested IF statements, but I don't think your description is very clear. And some of your conditions are not described correctly. Your step 4's contradict each other. You have <= in one, and >= in the other. If they are =, which one do you want it to do?

It doesn't always work, but what I have found to be helpful when building nested IF statements is to do the following:

For each of your different scenarios, build an IF formula in its own cell. Then combine them into one formula.

For example, in your formula, the first criteria is IF(J1=K1, 0, DO SOMETHING ELSE IF J1<>K1)

So, what do you want to do if J1<>K1?


I'll give an example of a nested if:

Criteria:
1. If J1 = K1, 0
2. If J1 <= K1, then K1 - J1
3. If J1 > K1, then 0

Logic:
If Step 1 is TRUE, then return 0
If Step 1 is FALSE, then either step 2 or 3 must be TRUE.
If Step 2 is TRUE, then return the value of K1 - J1
If Step 2 is FALSE, then step 3 must be TRUE
If Step 3 is TRUE, return 0

Here is how you would write the formula for this:

=IF(J1 = K1, 0, IF(J1<=K1, K1 - J1, 0))

I hope this can help lead you in the right direction. Ask more questions if you are unable to figure it out.