CODEWITHSUNDEEP
excelexcel-formula
Undfted Spearit
Undfted Spearit

Reputation: 3

How can I write a formula to return a certain number if a result falls within a certain range of numbers?

I am attempting to create a performance report card for my team, but I can't for the life of me write a formula that will return the number rating based on the range the employees result falls in. So, 0-1.49 should equal 1.00, 1.50-2.49 = 2.00, 2.50-3.49 = 3.00, 3.50-4.49 = 4.00, and 4.50 or higher = 5.00.

=IFS(AND(E38>.0149,E38<.0250),2,IF(AND(E38>.0350,E38<.0249),3,IF(AND(E38>.0349,E38<.0450),4,IF(E38>.0449,5,1))))

I keep getting an error stating "there are too few arguments for this function". I am not sure where to go from here. Please Help!

Upvotes: 0

Views: 102

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

I am sharing in answers only for the reason to show that we can avoid using Nested IF()s function and rather use lookup functions with approximate match, which work better and faster, will be dynamic as well.

enter image description here

• Using LOOKUP() Function --> Applicable from Excel 2007+ onwards.

Formula used in cell F38

=IF(E38="","",LOOKUP(E38,$C$32:$E$36))

• Using VLOOKUP() Function --> Applicable from Excel 2007+ onwards.

Formula used in cell G38

=IF(E38="","",VLOOKUP(E38,$C$32:$E$36,3))

• Using XLOOKUP() Function --> Applicable from Excel 2021+ onwards.

Formula used in cell H38

=IF(E38:E42="","",XLOOKUP(E38:E42,C32:C36,E32:E36,"",-1))

• Using Nested IF() Function --> Applicable from Excel 2007+ onwards.

Formula used in cell I38

=IF(E38="","",IF(E38<=1.49,1,IF(E38<=2.49,2,IF(E38<=3.49,3,IF(E38<=4.49,4,5)))))

• Using Nested IFS() Function --> Applicable from Excel 2019+ onwards.

Formula used in cell J38

=IFS(E38="","",E38<=1.49,1,E38<=2.49,2,E38<=3.49,3,E38<=4.49,4,E38>4.49,5)

• Using Nested SWITCH() Function --> Applicable from Excel 2019+ onwards.

Formula used in cell K38

=SWITCH(TRUE,E38="","",E38<=1.49,1,E38<=2.49,2,E38<=3.49,3,E38<=4.49,4,5)

Upvotes: 0

Related Questions