Excel IF formula help needed

Question

I am trying to create a complicated IF formula in excel that involves multiple IF/THEN assumptions.

I'm typing my formula in CELL H5

=IF(D5="YES",(IF(E5>199,0.25,0))+(IF(F5>199,0.25,0))+(IF(G5>199,0.25,0)),(IF(E5>999,1,0))+(IF(F5>999,1,0))+(IF(G5>999,1,0)))

*using this calculation, if D5=YES:

E5=500

F5=1000

G5=0;

the result will be 0.50

*using this calculation if D5 = NO:

E5=500

F5=1000

G5=0; 

the result will be 1.00

This is how the formula above works...and it works well for that.

But I need the result if D5 = NO to actually be 1.50 using a sliding scale where:

IF E5 is less then 200, VALUE = 0.00

IF E5 is between 200-999, VALUE = 0.50

IF E5 is greater than 999, VALUE = 1.00

How do I do this?  Any ideas?

Thanks kindly!

Answer 1

The "sliding scale" you describe can be achieved with this formula:

=LOOKUP(E5,{0,200,1000},{0,0.5,1})

You can plug this into your existing formula like this:

=IF(D5="YES",(IF(E5>199,0.25,0))+(IF(F5>199,0.25,0))+(IF(G5>199,0.25,0)),(LOOKUP(E5,{0,200,1000},{0,0.5,1}))+(IF(F5>999,1,0))+(IF(G5>999,1,0)))

Answer 2

Thanks for your help!  The answer teylyn provided (above) is exactly what I needed.

Answer 3

Did you mean so

Answer 4

For your formula, would this be of interest?

=IF(D5="Yes",0.25*COUNTIF(E5:G5,">199"), COUNTIF(E5:G5,">999"))

For your sliding scale, you could take advantage of the increments being the same:

(COUNTIF(E6:G6,">=200")+COUNTIF(E6:G6,">999"))/2)

Answer 5

The "sliding scale" you describe can be achieved with this formula:

=LOOKUP(E5,{0,200,1000},{0,0.5,1})

You can plug this into your existing formula like this:

=IF(D5="YES",(IF(E5>199,0.25,0))+(IF(F5>199,0.25,0))+(IF(G5>199,0.25,0)),(LOOKUP(E5,{0,200,1000},{0,0.5,1}))+(IF(F5>999,1,0))+(IF(G5>999,1,0)))

Thanks, teylyn.  I also needed to have that for F5 & G5 and just replaced the other calcs for F5 & G5 with your LOOKUP function...WORKED GREAT!  Truly appreciated!  Have a safe a happy new year!