MS Excel Nested Functions

[Aussie Barbie]

Hello,

I want to nest more than 7 nested functions (actually I woould like to do
about 14 or more. Is this possible?

I have listed a sample of what I have to date but cannot seem to get any
more. Can you help?

=IF($C$11<=10000,C24,IF($C$11<20001,D24,IF($C$11<35001,E24,IF($C$11<50001,F24,IF($C$11<75001,G24,IF($C$11<100001,H24,IF($C$11<150000,I24,IF($C$11>14999,J24))))))))

 

[sara.ridley]

It sounds like you need to use a vlookup function as you cannot use more than 7 nexted IFs

in a table in excel put the headings value and return value
then put 0 (Under Value), whatever value you have in C24 (under return value)
NEXT ROW 10000 (Under Value), whatever value you have in D24
etc for all values
then use VLOOKUP(C11,tablearray,2)

tablearray is the either the table name (named cells) or the cell range eg A1:B5

let me know how you get on

 

[Niek Otten]

Use the VLOOKUP function instead.; unlimited and much easier to write and extend

Look here for a tutorial:

http://www.contextures.com/xlFunctions02.html


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I want to nest more than 7 nested functions (actually I woould like to do
| about 14 or more. Is this possible?
|
| I have listed a sample of what I have to date but cannot seem to get any
| more. Can you help?
|
|
=IF($C$11<=10000,C24,IF($C$11<20001,D24,IF($C$11<35001,E24,IF($C$11<50001,F24,IF($C$11<75001,G24,IF($C$11<100001,H24,IF($C$11<150000,I24,IF($C$11>14999,J24))))))))
|
|
|

 

[Bob Phillips]

=INDEX(C24:K24,MATCH(C11,{0,10000,20000,35000,50000,75000,100000,150000},1))

you should be able to see where to increase this

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary''s Student]

Use a VLOOKUP() table rather than a nested IF

 

[Aussie Barbie]

Thanks heaps for all of your suggestions & assistance.

A big special thanks to Bob Phillips who had the solution solved in one go!
You're an Excel legend!!!

Aussie Barbie