nested "if" formula problem.

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))
 
D

David Heaton

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))

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

David
 
G

Guest3731

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

David


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!
 
P

Pete_UK

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
 
D

Dana DeLouis

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

Just another option...

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

= = = = = = =
Dana DeLouis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top