If problem

[proctk]

Hi below is an if statement that I cnnot get to work for some reason.

the value returned from C71/C70 will be a decimal value.

that way it is know it only retuns 1 or 0

=IF(C71/C70>=1,"1",IF(C71/C70>=1.15,"2",IF(C71/C70>=1.3,3,IF
(C71/C70>="1.45",4,0))))

 

[Norie]

What's in C71 and C70?

What's the logic behind the formula?

Note that if the calculation is >=1 then the formula will immediately stop on the first If and the other parts of it won't get evaluated.

 

[proctk]

c71 = 20 and c70 = 12

answer is 1.6666667

 

[will_simpson]

As Norie said, its the order of the formula.

If you want the answer to be "4" then reverse the order of the checks.

=IF(C71/C70>="1.45",4,IF(C71/C70>=1.3,3IF(C71/C70>=1.15,"2",IF
(C71/C70>=1,"1",0))))

You are also aware thatyou are returning a text value for 1 and 2 and numeric 0,3 and 4s?

The formula above will give
<0 >> 0 (numeric)
0-0.999999999999999 >> 0 (numeric)
1-1.149999999999999 >> 1 (text)
1.15-1.299999999999 >> 2 (text)
1.3-1.4499999999999 >> 3 (numeric)
1.45 and above >> 5 (numeric)

 

[will_simpson]

Also, try using a vlookup, set your lookup range in 2 columns.

In the first column, the values over which you want to return a different result and then in the second column what yuo want to return, making sure the values in the first column are in ascending order.

Then use =vlookup(C71/C70,look_up_range,2,1) the 1 at the end means the the lookup will look for the next highest match and give you the effect of >= in the if.