Need help on nested IF function

fiats3

Board Regular
So I have these 2 defined formulas:

OnetoFive

SixtoTen

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").

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

rmtimmah

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

fiats3

Board Regular
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

Well-known Member
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

MrExcel MVP

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

MrExcel MVP
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...

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

MrExcel MVP
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

Replies
4
Views
195
Replies
3
Views
534
Replies
6
Views
349
Replies
2
Views
164
Replies
6
Views
756

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,951
Messages
5,834,545
Members
430,295
Latest member
amdis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back