Hi I have this formula for multiple If condition how ever if my data is 85% the outcome is always 1.5 Should be 1.75 also with 92.54% outcome is 1.75 should be 2.25

100.00% =3
97.00%-99.00% =2.75
94.00%-96.00% =2.5
91.00%-93.00% =2.25
90.00% =2
77.00%-89.00% =1.75
64.00%-76.00% =1.5
51.00%-63.00% =1.25
40.00%-50.00% =1
27.00%-39.00% =0.75
14.00%-26.00% =0.5
1.00%-13.00% =0.25
0.00% =0

My Formula

=IF('TR&OD 2023'!D3=100%, "3",
IF('TR&OD 2023'!D3>=99%, "2.75",
IF('TR&OD 2023'!D3>=96%, "2.5",
IF('TR&OD 2023'!D3>=93%, "2.25",
IF('TR&OD 2023'!D3=90%, "2",
IF('TR&OD 2023'!D3>=89%, "1.75",
IF('TR&OD 2023'!D3>=76%, "1.5",
IF('TR&OD 2023'!D3>=63%, "1.25",
IF('TR&OD 2023'!D3>=50%, "1",
IF('TR&OD 2023'!D3>=39%, ".75",
IF('TR&OD 2023'!D3>=26%, ".5",
IF('TR&OD 2023'!D3>=13%, ".25",
IF('TR&OD 2023'!D3>=0%, "0")))))))))))))

CodePudding user response：

It looks like there is a problem with the way the formula is written.

The issue is that the formula is checking for values that are equal to the upper bound of each range, but it should be checking for values that are greater than or equal to the lower bound and less than the upper bound.

For example, the current formula checks for values that are equal to 90%, but it should be checking for values that are greater than or equal to 77% and less than 89%.

Here is the corrected formula:

=IF('TR&OD 2023'!D3>=100%, "3",
IF('TR&OD 2023'!D3>=97%, "2.75",
IF('TR&OD 2023'!D3>=94%, "2.5",
IF('TR&OD 2023'!D3>=91%, "2.25",
IF('TR&OD 2023'!D3>=77%, "1.75",
IF('TR&OD 2023'!D3>=64%, "1.5",
IF('TR&OD 2023'!D3>=51%, "1.25",
IF('TR&OD 2023'!D3>=40%, "1",
IF('TR&OD 2023'!D3>=27%, ".75",
IF('TR&OD 2023'!D3>=14%, ".5",
IF('TR&OD 2023'!D3>=1%, ".25",
IF('TR&OD 2023'!D3>=0%, "0")))))))))))))