Is it possible to nest more than 7 functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know You cannot enter, or nest, no more than seven levels of functions
within a function. I need 8, is there any possible solution to get 8?

Thanks
 
More than likely. If you post details, I (or someone else) may be able to be
of assistance. Otherwise, "probably" is about the best you're going to get
out of me.

Some discussion here - post back w/details if it does not help you.
http://cpearson.com/excel/nested.htm
 
Here is what I have

=IF(AND(E5>91000,E5<99000),"1-50 &
1-40",IF(AND(E5>139600,E5<147000),"4-40's",IF(AND(E5>182000,E5<198000),"2-40's
&
2-50's",IF(AND(E5>176501,E5<181999),"3-50’s",IF(AND(E5>161000,E5<167500),"3-40's
& 1-50",IF(AND(E5>167501,E5<176500),"3-40's & 1-50 OR
3-50's",IF(AND(E5>147001,E5<154500),"4-40's OR 1-40 & 2-50's","No good")))

This is what I want

=IF(AND(E5>91000,E5<99000),"1-50 &
1-40",IF(AND(E5>139600,E5<147000),"4-40's",IF(AND(E5>182000,E5<198000),"2-40's
&
2-50's",IF(AND(E5>176501,E5<181999),"3-50’s",IF(AND(E5>161000,E5<167500),"3-40's
& 1-50",IF(AND(E5>167501,E5<176500),"3-40's & 1-50 OR
3-50's",IF(AND(E5>147001,E5<154500),"4-40's OR 1-40 &
2-50's",IF(AND(E5>182000,E5<195000),"3-50's,","No good"))))))))

Thanks
 
Here is what I have

=IF(AND(E5>91000,E5<99000),"1-50 &
1-40",IF(AND(E5>139600,E5<147000),"4-40's",IF(AND(E5>182000,E5<198000),"2-40's
&
2-50's",IF(AND(E5>176501,E5<181999),"3-50’s",IF(AND(E5>161000,E5<167500),"3-40's
& 1-50",IF(AND(E5>167501,E5<176500),"3-40's & 1-50 OR
3-50's",IF(AND(E5>147001,E5<154500),"4-40's OR 1-40 & 2-50's","No good")))

This is what I want

=IF(AND(E5>91000,E5<99000),"1-50 &
1-40",IF(AND(E5>139600,E5<147000),"4-40's",IF(AND(E5>182000,E5<198000),"2-40's
&
2-50's",IF(AND(E5>176501,E5<181999),"3-50’s",IF(AND(E5>161000,E5<167500),"3-40's
& 1-50",IF(AND(E5>167501,E5<176500),"3-40's & 1-50 OR
3-50's",IF(AND(E5>147001,E5<154500),"4-40's OR 1-40 &
2-50's",IF(AND(E5>182000,E5<195000),"3-50's,","No good"))))))))

Thanks
Click to expand...

VLOOKUP would be a much simpler construct.

It would also let you more easily see that you have some overlapping ranges. In
your desired formula, you have the following:

....IF(AND(E5>182000,E5<198000),"2-40's & 2-50's" ...
....IF(AND(E5>182000,E5<195000),"3-50's," ...



You could set up a table like:

0 No Good
91,000 1-50 & 1-40
99,001 No Good
139,600 4-40's
147,001 4-40's OR 1-40 & 2-50's
154,501 No Good
161,000 3-40's & 1-50
167,501 3-40's & 1-50 OR 3-50's
176,501 3-50's
182,000 2-40's & 2-50's
182,000 3-50's


But you'd have to figure out what you want to do at the end where you have
overlapping ranges.

And then use a formula like:

=VLOOKUP(E5,tbl,2)


--ron
 
bit of a sledgehammer no?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Appears to me that "LOOKUP" could be used, with "No Good" being returned
for the "gaps" in the sequence.
 
Back
Top