Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
Creating a nested "IF" type result that requires more than 64 levels- is vlookup still the best option?
Posted on 2016-10-26
I'm creating a sheet that displays an organization's overall score (T1-T4) based off of up to 6 inputs (those inputs are coded as T,T-,P,P- and U). I was using nested IFs and had success until the number combinations required exceeded the levels (64). Here's a sample of the nesting lines at the 5 input level:
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T-",D7="
IF(AND(D5="T-",D6="T-",D7=
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Any help or suggestions would be great!
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T-",D7="
IF(AND(D5="T-",D6="T-",D7=
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Any help or suggestions would be great!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +2
8 Comments
Active 1 day ago
Can you explain, in words, the logic behind what you want to do and/or upload a sample workbook?
I have a table of proficiency outcomes based off of specific tasks. For example: Company A has tasks 1,2,3, and 4. The can score a rating of T,T-,P,P- and U. A combination of those scores creates an overall proficiency level (T1,T2,T3,T4). See attached table for partial combinations.
I'd like to create a spreadsheet that would allow me to input the task scores and have a proficiency level generated automatically. Nesting IF worked great...but I quickly exceeded the 64 levels and it was a bit tedious.
Capture.JPG
I'd like to create a spreadsheet that would allow me to input the task scores and have a proficiency level generated automatically. Nesting IF worked great...but I quickly exceeded the 64 levels and it was a bit tedious.
Capture.JPG
Active today
Make a list of the possible combinations (column A), and the result for each (column B).
TTTTT T1
TTTTT- T1
TTTT-T- T1
..
TTTPP T2
etc.
Then do a lookup for D5&D6&D7&D8&D9 on that.
TTTTT T1
TTTTT- T1
TTTT-T- T1
..
TTTPP T2
etc.
Then do a lookup for D5&D6&D7&D8&D9 on that.
Active today
@John Pyne
Do you also need to score the other combinations (sets of 2, 3, 5, 6)? Or is this limited to just sets of four?
Do you also need to score the other combinations (sets of 2, 3, 5, 6)? Or is this limited to just sets of four?
Active today
In that case, Ejgil Hedegaard probably posted the best solution. Use a different worksheet to past the strings and score value table(s).
Alternatively, you could populate a dictionary object in a function and invoke that function in a formula. The formula would be very simple (no If statements) and the VBA function would be relatively simple, too. If you have access to a digital version of the table data, it will save you a lot of typing.
Alternatively, you could populate a dictionary object in a function and invoke that function in a formula. The formula would be very simple (no If statements) and the VBA function would be relatively simple, too. If you have access to a digital version of the table data, it will save you a lot of typing.
Active today
Another option would be to close out each IF statement rather than nesting them and concatenate the results; if only result is Ture you wll get the result from that secton.
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Alternatively, rather than using AND, concatenate the values and compare to one string:
IF(D5&D6&D7&D8&D9="TTTTT",
IF(D5&D6&D7&D8&D9="TTTTT-"
Another option:
="T"&COUNTIF(D5:D9,"P*")+1
Might need some expanding for the U variants.....
IF(AND(D5="T",D6="T",D7="T
IF(AND(D5="T",D6="T",D7="T
Alternatively, rather than using AND, concatenate the values and compare to one string:
IF(D5&D6&D7&D8&D9="TTTTT",
IF(D5&D6&D7&D8&D9="TTTTT-"
Another option:
="T"&COUNTIF(D5:D9,"P*")+1
Might need some expanding for the U variants.....
Featured Post
Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses
Course of the Month6 days, 16 hours left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.