|
Nesting
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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
You can not use more than 7 IFs in one formula.
"Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
Thanks, Ron. The second formula works work for what I need.
"Ron Coderre" wrote: 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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
You're welcome, Donna.....I'm glad I could help.
Regards, Ron Microsoft MVP (Excel) "Donna" wrote in message ... Thanks, Ron. The second formula works work for what I need. "Ron Coderre" wrote: 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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
One other question. Can this formula work for 200,000 or more, 300,000 or
more, etc.? "Ron Coderre" wrote: You're welcome, Donna.....I'm glad I could help. Regards, Ron Microsoft MVP (Excel) "Donna" wrote in message ... Thanks, Ron. The second formula works work for what I need. "Ron Coderre" wrote: 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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
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) "Donna" wrote in message ... One other question. Can this formula work for 200,000 or more, 300,000 or more, etc.? "Ron Coderre" wrote: You're welcome, Donna.....I'm glad I could help. Regards, Ron Microsoft MVP (Excel) "Donna" wrote in message ... Thanks, Ron. The second formula works work for what I need. "Ron Coderre" wrote: 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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
Yes, that works. What does the -3 accomplish?
"Ron Coderre" wrote: 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) "Donna" wrote in message ... One other question. Can this formula work for 200,000 or more, 300,000 or more, etc.? "Ron Coderre" wrote: You're welcome, Donna.....I'm glad I could help. Regards, Ron Microsoft MVP (Excel) "Donna" wrote in message ... Thanks, Ron. The second formula works work for what I need. "Ron Coderre" wrote: 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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
Nesting
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) "Donna" wrote in message ... Yes, that works. What does the -3 accomplish? "Ron Coderre" wrote: 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) "Donna" wrote in message ... One other question. Can this formula work for 200,000 or more, 300,000 or more, etc.? "Ron Coderre" wrote: You're welcome, Donna.....I'm glad I could help. Regards, Ron Microsoft MVP (Excel) "Donna" wrote in message ... Thanks, Ron. The second formula works work for what I need. "Ron Coderre" wrote: 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) "Donna" wrote in message ... 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(E13550000,800,IF(E13500000,700,IF(E13450000 ,600,IF(E13400000,500,IF(E13350000,400,IF(E1330 0000,300,IF(E13250000,200,IF(E13200000,100,0)))) )))) |
| All times are GMT +1. The time now is 11:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com