| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)))) )))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) )))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) )))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) )))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) )))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) )))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) )))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OR Nesting? | Excel Discussion (Misc queries) | |||
| NESTING FORMULA | Excel Worksheet Functions | |||
| Nesting Problem | Excel Worksheet Functions | |||
| IF - Nesting... almost got it - need a bit of help | Excel Worksheet Functions | |||
| nesting sum if and | Excel Worksheet Functions | |||
Hybrid Mode
