Nesting

  • Thread starter Thread starter Donna
  • Start date Start date
D

Donna

I need to add another level (if over 600,000 then 900) to this formula and
when I do I get an error that the formula is over the level of nesting
allowed. Got any ideas?

=IF(E13>550000,800,IF(E13>500000,700,IF(E13>450000,600,IF(E13>400000,500,IF(E13>350000,400,IF(E13>300000,300,IF(E13>250000,200,IF(E13>200000,100,0))))))))
 
With
E13: (a value)

....and...assuming your example is a simplified version of what you really
want...

Try this:

With this list in A1:B10
200,000 0
250,000 100
300,000 200
350,000 300
400,000 400
450,000 500
500,000 600
550,000 700
600,000 800
999,999 900

This formula finds E13 in Col_A and returns the corresponding Col_B value :
F13: =INDEX(B1:B10,MATCH(1,FREQUENCY(E13,A1:A9),0))

BUT.....If your example is exactly what you want...
try this:
F13: =MIN((MAX(CEILING(E13/50000,1)-4,0))*100,900)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
You're welcome, Donna.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)
 
If I understand what you're asking...you want the lower limits to be
inclusive.
So...200,000 would return: 100....instead of 0.

If that's true, for 50k increments, try this:
F13: =MIN((MAX(INT(E13/50000)-3,0))*100,900)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Yes, that works. What does the -3 accomplish?

Ron Coderre said:
If I understand what you're asking...you want the lower limits to be
inclusive.
So...200,000 would return: 100....instead of 0.

If that's true, for 50k increments, try this:
F13: =MIN((MAX(INT(E13/50000)-3,0))*100,900)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Click to expand...
 
Yes, that works. What does the -3 accomplish?

This section of the formula: INT(E13/50000)
calculates how many whole multiples of 50,000's
are in cell E13's value.

125,000 has 2...(actually 2.5, the INT truncates the fractions)
150,000 has 3.

But...you want to start calculating beginning with 200,000.

200,000 has 4...so by subtracting 3 we effectively discard
values below 200,000

(The MAX function makes sure the values don't go negative.)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Back
Top