nested "if" formula problem.

  • Thread starter Thread starter Guest3731
  • Start date Start date
G

Guest3731

Hi, group!

I can't get a nested if formula to do what I want & I could use some
assistance if possible.

I've got numbers in 3 columns, E, F, G. I want to put a formula in
column H such that it shows the difference between E and the sum of F
& G (E - (F + G)). However, I want it to have a minimum (0), and a
maximum (800,000). Below is what I'm trying. I get an answer of 0 in
column H, despite having a value of 1,200,000 in column E and 100,000
in each of F & G - where I would expect a maximum value in D (800,000)
(1,200,000 - (100K +100K) = 1,000,000, but maxes out at 800,000).

Any clues?

=IF((E4-(F4+G4)>800000),800000,IF((E4-(F4+G4))>0,(E4-(F4+G4)),0))
 
Hi, group!

I can't get a nested if formula to do what I want & I could use some
assistance if possible.

I've got numbers in 3 columns, E, F, G.  I want to put a formula in
column H such that it shows the difference between E and the sum of F
& G (E - (F + G)).  However, I want it to have a minimum (0), and a
maximum (800,000).  Below is what I'm trying.  I get an answer of 0 in
column H, despite having a value of 1,200,000 in column E and 100,000
in each of F & G - where I would expect a maximum value in D (800,000)
(1,200,000 - (100K +100K) = 1,000,000, but maxes out at 800,000).

Any clues?

=IF((E4-(F4+G4)>800000),800000,IF((E4-(F4+G4))>0,(E4-(F4+G4)),0))
Click to expand...

your formula works fine for me.
using the same scenario as you gave the formula returns 800,000

David
 
your formula works fine for me.
 using the same scenario as you gave the formula returns 800,000

David
Click to expand...


Wow, thank you very much for both (extremely rapid) responses.
Unfortunately I am something of an idiot here and realized after the
fact that the reason my formula wasn't working was because I had
recently added a column, which threw off a cell-reference in E4 in a
hard-to-detect way. But I am very glad to know also about the Max/Min
trick - very nice!
 
You're welcome.

The trick to remember with MAX and MIN is that although you want the
result to have a maximum value of 800,000, you actually put this
inside a MIN function, so that if the calculation exceeds this then
the lower value will be taken, and similarly for the minimum value of
0 going into a MAX function.

Pete
 
=MAX(MIN(800000,E4-F4-G4),0)

Just another option...

=MEDIAN(0, E4-(F4+G4), 800000)

= = = = = = =
Dana DeLouis
 
Back
Top