Microsoft Excel 2010 - complete product...

I am trying to get cell G3 to return a specific value if the value of F3 is greater then 4 different set values. I have found a few formulas but I keep just getting the 400 value returned for all. This is the formula I have tried using but it is not completely working: =IF(F3>=120,400,IF(F3>=240,800,IF(F3>=480,1200,IF(F3>=600,1500,0))))

Example:

if the value of F3 is greater then or equal to 120 but less then 240 it is to return a value of 400, if the value of F3 is greater then or equal to 240 but less then 480 it is to return a value of 800, if the value of F3 is greater then or equal to 480 but less then 600 it is to return a value of 1200, if the value of F3 is greater then or equal to 600 it is to return a value of 1500.

Try starting at the big numbers and work to the small numbers,

so the logic cascades down.Something like this: (untested)

=IF(F3>=600,1500,IF(F3>=480,1200,IF(F3>=240,800,IF(F3>=120,400,0)))

By way of a quick explanation, Excel stops processinng at the first TRUE statement.

In your formula, the first statement will evaluate true when any value greater that 120 is entered so processing stops and you get 400,

MIKE

Thanks Mike that worked perfectly.

Excel IF functions are evaluated from left to right and the evaluation stops as soon as any clause returns TRUE. Excel doesn't check every clause and then decide what to do because more than one clause could be TRUE and Excel wouldn't know what to do. The way your IF function is written, any value >=120 is going to return 400 because the first clause will be TRUE and Excel won't check anything else.

The way around that is to start with your highest value and work your way down.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Great explanation DD.

Ask Your Question

Weekly Poll