IF Statment with Multiple Ranges

**MROMAR** · 08-30-2012, 12:31 PM

I need a forumla which would return three diffrent values depending on which range an actual result falls into.

I have a min goal value in cell C25
Expected goal in D25
and Max goal in E25

The result is in F25

If F25 falls between C25-D25 then it needs to return B25*B18
If F25 falls between D25-E25 then it needs to return B25*B19
If F25 is greater than E25 then it needs to return B25*B20

Its confused the heck out of me, any help would be appreciated.

**twiggywales** · 08-30-2012, 12:50 PM

lol here we go again,

=IF(AND(F25>=C25,F25<D25),B25*B18,IF(AND(F25>D25,F25<=E25),B25*B19,IF(F25>E25,B25*B20)))

however what happens when it is exactly right or lever than C25?

---------- Post added at 05:50 PM ---------- Previous post was at 05:41 PM ----------

i would try this

=IF(AND(F25>=C25,F25<D25),B25*B18,IF(AND(F25>D25,F25<=E25),B25*B19,IF(F25>E25,B25*B20,"LOWER")))

that takes care of the under,

as for when it is exact what you need to do is put an = symbol nxt to the < or > in the section you need.
(F25>=C25,F25<=D25) FOR FIRST EQUATION
(F25>=D25,F25<=E25) FOR SECOND EQUATION

i hope this helps as i wont get access to the computer for the next few hours.

again i must say that nest ifs are hard to follow. im currently gonna try and learn to not use them, this is 3 deep and that will be my max from now on lol.

**MROMAR** · 08-30-2012, 01:43 PM

You are a genius, everything you do works perfectly.

One more question if you don't mind, when a result does not fall in between any of the ranges it returns a FALSE. Now I need one cell to be contingient on if one of the other cells returns a FALSE.

In Cell G27 =IF(AND(F27>=C27,F27<D27),B27*$B$18,IF(AND(F27>D27,F27<=E27),B27*$B$19,IF(F27>E27,B27*$B$20)))

I need G27 to return a FALSE if G28 is false, even if the forumla in G27 returns a value

**twiggywales** · 08-30-2012, 02:57 PM

Without seeing your book its hard to say, if you remove the comma and lower part then that will return a false value is not in the ranges you stated above.

If you have a list of cells where the false may cookie the formula would be something like

=if(or(a1,a2,a3)="false","false",@)

That @ symbol is just where your result you want to see is.

The formula checks if any cells a1 a2 a3 on this case equal false and then returns false of they do or shows wanted cells if not. Sorry I can't check more the roughly but this is off my iPhone so no excel or decent chance to check

**MROMAR** · 08-30-2012, 02:59 PM

I was able to figure it out, building on your previously formula, honestly dude your a life saver though.

=IF(G28=FALSE,FALSE,IF(AND(F27>=C27,F27<D27),B27*$B$18,IF(AND(F27>D27,F27<=E27),B27*$B$19,IF(F27>E27,B27*$B$20))))