Forum Discussion
264 nested IF statements -- alternative?
OK. I've modified your data.xlsx to make, I trust, how to do this clear. Here's the basic Input and Output area
I usually follow a convention of using yellow background in any cell that calls for entry by the user, and have followed that convention here. Other than the "Raw Score" cell, I've also used Data Validation to ensure that only acceptable values are entered. In the two cases, there are only two choices of appropriate text. In the others, a whole number is required, within the acceptable range.
Then, in the gray areas, the calculations are done, in both cases using INDEX and MATCH. The tables for those retrievals are on a separate "Behind the Scenes" tab. It looks like this:
There are formulas in the column of four category identifiers. And a final formula in the gray background area, where the column heading is calculated, simply by concatenating the strings above. I do this in the simplest way, using the "&" symbol. It could be done other ways, but really not necessary here. I also should note that some folks would combine all these formulas into a single one; that's entirely possible, but for your sake in understanding all the steps, I've chosen to break it into separate steps.
I also did not take the trouble to verify all the outputs when changing the inputs on that primary screen. You should do that.
Let me know if you have questions.
Thank you so much for all of this! I went over all the of the formulas and I can clearly see how the INDEX MATCH formula works here. This is incredibly useful.
You are most welcome. It was a good learning experience for me to. If that surprises you, it shouldn't. I strongly suspected from the start that a single table was possible, but I've never had to construct a process with so many intervening steps required to get to the desired bit of data--in this case, the "t-score"--after beginning with all those variables.
Part of the learning for you, I hope, is how a good design is one of the most important starting points; in particular, a well designed database. Just knowing how to do VLOOKUP formulas--as you were doing coming in--wasn't sufficient, even if it might have been possible to construct an elaborate way to individually name each table and reference each table with a LOOKUP buried in an IF or IFS or other shell.
Take the time to educate yourself on all of the different functions and capabilities of Excel...a resource like ExcelJet is highly recommended. In the old days, when printed manuals accompanied software, I used to read, and then re-read the Lotus 1-2-3 manuals every year, always discovering an easier way to do something, something I'd been doing that worked but was far less efficient, like having 264 tables and making them work, but not realizing that re-thinking, using a hitherto unknown function, would make it possible to get to the same end product far more effectively, reliably.
Thanks for sharing that this was a learning experience for you too. I don't remember learning this kind of stuff in my college Excel class, though I'm not sure it would have stuck anyway since the fabricated data/scenarios weren't relevant to me, making it harder to grasp the value of different functions. The multiple steps did complicate it a bit. Even after understanding more about INDEX and MATCH, I still couldn't figure out the additional steps to make it all work. You've introduced me to a lot of different functions and how they can be used in combination. I will be sure to check out the ExcelJet resource.
Excel didn't exist when I was in college (1959-1963). In fact, computers were only barely available; I remember a couple of geeks (not including me) having access to something or other in the early 60s. I had the good fortune to work for IBM for several years, a summer job in 1969, full-time from 1970-1973. During that time I taught myself the languages Basic and APL, had access to a teletype terminal which connected with a huge computer in the basement of the IBM facility where I worked.....
One of the big learnings for me through all of this was that I learn better by experimenting--sometimes calling it "playing"--being willing to make mistakes, realizing I'm not going to break anything.
All of that being exposed to computers before they were ubiquitous left me comfortable with the technology, far more so than most of my contemporaries, even though most of my career didn't involve being an IT person.....but I've just enjoyed what Excel can do, as well as other programs, and have enough comfort with tech in general that I almost always can figure things out as much as I need to. There are folks here in this forum who can run circles around me with advanced Excel techniques, and I enjoy reading their solutions to complex problems. But I particularly like helping folks like you learn to stretch your wings and try new things.