Help with nested IF formula

[xx8josh9xx]

First post on here. I tried to look for help on IF AND but I think my specific formula was to complicated for me.


I need help with cell Z15.

My formula is:
IF(F14=1,100, IF(F14=2,50, IF(F14>=5, IF(AND(W3>=3499.99,(I15+J15+K15+L15+O15+P15+Q15+R15+S15+T15+U15+V15+W15+X15)*-0.5,0))))

Im trying to say if W3 is>3999.99 then to do the (I15+J15+K15+L15+O15+P15+Q15+R15+S15+T15+U15+V15+W15+X15)*-0.5,0), but if its not then to output 0,

so if F14=1 (outputs 100) or 2 (out puts 50). If F14 = 5 then to take that row x 50%. only if W3 is above 3499.99 though, if its below 3499.99 then i want it to output 0


Thanks for your help and time to look at this.

[FDibbins]

Hi and welcome to the forum

Not sure if this is what you want, give it a try...

=IF($F$14=1,100, IF($F$14=2,50, IF($F$14>=5, IF(W3>=3499.99,SUM(I15:L15,O15:X15)*-0.5,0))))

Seeing as you only have 1 condition to test, you dont need to use AND() (or OR() )

[xx8josh9xx]

Yup, you are a genius, it works, but, when placing a 3 or 4 in cell F14 it reports back false. How can i change false to 0 . I only need 1,2,5 to output a number, any other number should output 0


Also I learned what the $ signs mean. Thanks to google. I dont ever remember learning that in excel class. (Must have skipped that day in school haha, nice trick, saves some work)

I'm sure mine was one of the simple ones today, I was looking around the forms, looking to see if could help others. Yup, my basic excel doesn't help anyone on here. HAHA

Thanks for your time and help again.

[FDibbins]

OK sorry, try this...
=IF($F$14=1,100, IF($F$14=2,50, IF($F$14>=5, IF(W3>=3499.99,SUM(I15:L15,O15:X15)*-0.5,0),0)))

or, this slightly shorter version....
=IF($F$14<=2,100/$F$14,IF($F$14>=5, IF(W3>=3499.99,SUM(I15:L15,O15:X15)*-0.5,0),0))

[xx8josh9xx]

Thanks again

[FDibbins]

you're welcome and thanks for the feedback