Need help on nested IF function

fiats3

Board Regular
Joined
Nov 1, 2004
Messages
151
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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

rmtimmah

Board Regular
Joined
May 14, 2007
Messages
153
Are you referencing to a cell in your "Master Formula" eg =if(A1=SixToTen,SixToTen,OneToFive)
 

fiats3

Board Regular
Joined
Nov 1, 2004
Messages
151
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
Joined
Oct 17, 2007
Messages
2,287
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
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top