Microsoft Office Forums Nested IF
 Register FAQ Search Today's Posts Mark Forums Read

#1
01-06-2016, 02:00 PM
 TDWolsey Windows 10 Office 2010 32bit Novice Join Date: Jan 2016 Posts: 2
Nested IF

Hi, I searched a bit and didn't see this question, so here goes:

I want to create a nested IF formula that associates a raw score/number with a specific proficiency level; that is, if a score is 23, it should be assigned a proficiency level of 5. If the score is 22, it should be assigned a proficiency level of 4, and so on.

Here is the formula I used: =IF(K14>10,"1",IF(K14>15,"2",IF(K14>19,"3",IF(K14> 23,"4",IF(K14<26,"5")))))
No go.

So I reversed it: =IF(K14>26,"5",IF(K14>23,"4",IF(K14>19,"3",IF(K14> 15,"2",IF(K14<10,"1")))))
Still no go.

I attached a screen shot of the page. Any thoughts on how to create a cascading or nested IF (or maybe another) statement?

The green shaded areas include the overall score (tallied from each column using COUNTA) and the Proficiency level.

Thanks!
Attached Images
 SOLOM Screencap.JPG (85.3 KB, 12 views)
#2
01-06-2016, 03:16 PM
 gebobs Windows 7 64bit Office 2010 64bit Expert Join Date: Mar 2014 Location: Atlanta Posts: 832

Lots of ways to skin that cat. Here's one.

On the left is the lookup table with the lower bound of your intervals under Score and the corresponding Level to their right.

To the right, enter a score into the cell in E2 and it returns the level in E3.

This is the formula:
=VLOOKUP(E2,\$A\$2:\$B\$6,2)

What that does is look up the value of E2 in \$A\$2:\$B\$6 that is either equal to or lower and closest to it. It then returns the corresponding value it finds in Column 2 of that range.
Attached Files
 Score.xlsx (8.0 KB, 3 views)
#3
01-06-2016, 11:31 PM
 TDWolsey Windows 10 Office 2010 32bit Novice Join Date: Jan 2016 Posts: 2
Nested If

Thanks, gebobs,
Best, DeVere

Last edited by TDWolsey; 01-06-2016 at 11:33 PM. Reason: add detail.

 Tags nested

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post windseaker Excel 0 08-14-2015 04:13 PM wentworth16238 Excel 5 06-14-2015 12:59 PM bdavidson22 Excel 0 12-19-2014 12:36 PM asluder2000 Excel 2 11-14-2014 11:00 PM JimS378 Excel 7 05-03-2011 08:20 PM

All times are GMT -7. The time now is 07:23 AM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top