Home » Forums » Open Source Learning Center » Question and Answer

Help needed with IF nesting functions

Submitted by ryno8 on 9 September, 2015 - 15:45

I want to create a formula that would calculate like this...

C145 between $9,999.99 - 14,999.98 =$0
C145 between $14,999.99 - $19,999.98 =$25
C145 between $19,999.99 - $24,999.98 =$50
C145 between $24,999.99 - $29,999.98 =$75
C145 between $29,999.99 - $34,999.98 =$100
C145 between $34,999.99 - $39,999.98 =$125
C145 between $39,999.99 - $49,999.98 =$150
C145 between $49,999.99 - $74,999.98 =$175
C145 between $74,999.99 - $99,999.98 =$200
C145 between $99,999.99 - $99,999,999.98 =$250

PLEASE HELP!!!

‹ =IF(ISNUMBER(SEARCH( ---- Trying to search multiple options to return the same value Automatic calculation of minimum or maximum value in a cell ›
»

Use the

Submitted by mec1981 on 24 September, 2015 - 07:13.

Use the following:

=if(C145>99999.98,250,if(C145>74999.98,200,if(C145>49999.98,175,if(C145>39999.98,150,if(C145>34999.98,125,if(C145>29999.98,100,if(C145>24999.98,75,if(C145>19999.98,50,if(C145>14999.98,25,if(C145>9999.98,0,""))))))))))

Any value for C145 that is less than 9999.99 will result in a blank cell with the above. if you wish these to also be 0 then just remove the final if statement and enter 0 in it's place i.e. 25,0)))))))))

Regards
Mark

»