Need help on nested IF function

[fiats3]

So I have these 2 defined formulas:

OnetoFive

=IF(adf!$Q$2:$Q$20000>90,"91-100",IF(adf!$Q$2:$Q$20000>80,"81-90",IF(adf!$Q$2:$Q$20000>70,"71-80",IF(adf!$Q$2:$Q$20000>60,"61-70",IF(adf!$Q$2:$Q$20000>50,"51-60",FALSE)))))

SixtoTen

=IF(adf!$Q$2:$Q$20000>40,"41-50",IF(adf!$Q$2:$Q$20000>30,"31-40",IF(adf!$Q$2:$Q$20000>70,"71-80",IF(adf!$Q$2:$Q$20000>20,"21-30",IF(adf!$Q$2:$Q$20000>10,"11-20",IF(adf!$Q$2:$Q$20000>0,"0-10","not Found"))))))


And I have a master formula =IF(SixToTen,SixToTen,OneToFive)

But it is returning #Value!

Any ideas on how to get this formula to work? I'm trying to create 10 conditions for bandwidth classification, where if a value 'X' is found, then return a label defining the data range in the cell with the formula (such as "71-80").

Thanks in advance!

 

[rmtimmah]

Are you referencing to a cell in your "Master Formula" eg =if(A1=SixToTen,SixToTen,OneToFive)

 

[fiats3]

Are you referencing to a cell in your "Master Formula" eg =if(A1=SixToTen,SixToTen,OneToFive)

Actually, no need to do that since the named formula already defines a cell range $Q$2:$Q$20000.

 

[konew1]

Are you are trying to compare the entire range Q2:Q20000 to a single value?
What result do you want to return if Q2 contains 10 and Q3 contains 90?

 

[xenou]

And I have a master formula =IF(SixToTen,SixToTen,OneToFive)

One thought that comes to mind is that SixToTen can equal "Not Found" - your IF function needs to evaluate to TRUE or FALSE - or am I reading the formula wrong?

 

[xenou]

Note: probably I am missing something obvious, but when I break out a piece of your formula, I don't really understand what its doing...

=IF(adf!$Q$2:$Q$20000>90,"91-100","Huh?")

What does it mean for a range of numbers to be greater than 90? How is this evaluated by Excel? I thing I've been here once before but forgot my lesson...sigh.

AB

 

[barry houdini]

I assume you're just evaluating each entry row by row. Referring to the whole range can work but it's not necessary. Also if you're using two formulas just to get round the nested formula limit then I suggest you use a different type of formula, i.e. in row 2 copied down

=IF(AND(adf!Q2>0,adf!Q2<=100),CEILING(adf!Q2,10)-9&"-"&CEILING(adf!Q2,10),"not found")