Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-06-2016, 02:00 PM
TDWolsey TDWolsey is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Jan 2016
Posts: 2
TDWolsey is on a distinguished road
Post 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
File Type: jpg SOLOM Screencap.JPG (85.3 KB, 12 views)
Reply With Quote
  #2  
Old 01-06-2016, 03:16 PM
gebobs gebobs is offline Windows 7 64bit Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 832
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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
File Type: xlsx Score.xlsx (8.0 KB, 3 views)
Reply With Quote
  #3  
Old 01-06-2016, 11:31 PM
TDWolsey TDWolsey is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Jan 2016
Posts: 2
TDWolsey is on a distinguished road
Default Nested If

Thanks, gebobs,
Problem solved. Your solution worked perfectly. I really appreciate your help.
Best, DeVere

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

Tags
nested

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
nested if and averageA windseaker Excel 0 08-14-2015 04:13 PM
using if and nested vlookup wentworth16238 Excel 5 06-14-2015 12:59 PM
Nested Countif Function bdavidson22 Excel 0 12-19-2014 12:36 PM
Nested Vlookup Help asluder2000 Excel 2 11-14-2014 11:00 PM
Nested IFs JimS378 Excel 7 05-03-2011 08:20 PM


All times are GMT -7. The time now is 10:37 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft