CODEWITHSUNDEEP
excel
TO2022
TO2022

Reputation: 7

IF function in Excel - giving errors

I want to writte an If function with the following condition:

 80% > value then 0 point
 85% > value >= 80% then 1 point
 90% > value >= 85% then 2 points
 95% > value >= 90% then 3 points
 97.5% > value >= 95% then 4 points
 value >= 97.5% then 6 points

This is what I wrote, but I got an error for cases < 97.5%. I don't know where I did wrong. Please help

   "   =if(value >=97.5%,6,IF(AND(value >=95%,value <97.5%,4,IF(AND(value >=90%,value <95%),3, IF(AND(value >=85%,value <90%),2,IF(AND(value >=80%,value <85%),1,0)))))) "

Upvotes: 0

Views: 57

Answers (1)

Gravitate
Gravitate

Reputation: 3062

By restructuring your IFs, you can do away with the need to check the upper bounds of each band, and therefore the ANDs which are less efficient.

Please see the formula below where your value is in cell A1:

=IF(A1 >= 97.5%, 6,
    IF(A1 >= 95%, 4,
        IF(A1 >= 90%, 3,
            IF(A1 >= 85%, 2,
                IF(A1 >= 80, 1, 0)
            )
        )
    )
)

P.S. You can add new lines in the Excel formula bar by holding Alt and pressing the Enter key. This allows you to format your formulas to make them easier to read.

Easy Updates With Reference Table

The solution above is fine, if it is a one off formula that won't change often. However, if you have the formula repeated in numerous places and may need to change the cut offs or points associated with them often, it will quickly become labourious and error prone to find and update all the formulas.

The answer to this is to keep the cut offs and points in a separate reference table (which could be kept on a hidden sheet) and use a formula to look up the number of points that should be awarded. That way, you only have to update the table, and not each formula.

enter image description here

=INDEX($E$2:$E$7,MATCH($A$1,$D$2:$D$7,1))

Upvotes: 2

Related Questions