In Excel, how do I combine three formulas that I've created as named ranges?

Question

I have a conditional statement that's too long for Excel's logical function limit of eight.

A simplified example of the formula I'm trying to write is the report card example. A score between 90 and 100 is an A, between 80 and 89 is a B, between 70 and 79 is a C, etc.

Only I've got 19 of these different ranges that I'd like to apply to one cell. So say I enter a raw number in cell A1. I'd like B1 for example to contain the formula and display the converted data point.

I've read that using Named Ranges is the way to get around the 8 function limit which is what I need to do. Here is a site that describes it: http://www.cpearson.com/excel/nested.htm

But I just can't seem to get it to work. Can someone help me out or point me in the right direction? Thank you.

Answer 1

I can help here, but I need you to send me more details on this
It seams you need something I am doing for 6 years

mail me here in Y! Answers

YES

I am the VBAXLMan here

Answer 2

A "workaround" solution might be to create a cell for each condition such that the cell is populated only if THAT ONE condition is true and is left blank if not.

Then you can scan those 19 cells for a value (use concatenate?) and result is your desired value.

So:
a1 you enter 43
b1: =if(a1>=90,"A","")
c1: =if(a1<90,if(a1>=80,"B",""),"")

etc across the cells (yes you can combine those)

Then in t1: =concatenate(b1,c1,....) and the value in t1 should be what you want (?)

clunky, yes. but gets you through Excel's frustrating limitations

Now... what can be done about only having 3 conditional formats?!

Answer 3

The example you give can be done using simple vlookups.

The next thing you want to learn is to use VBA to create user defined functions. They are not difficult to master and easier to debug errors.