| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Failing with formula
Hi,
I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Hi Charlie
Try his formula: =ROUNDUP(B8/5000,0) Regards, Per "Charlie" skrev i meddelelsen ... Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Hey Charlie
You could do something like this: =IF(AND(B81,B8<5001),1,IF(AND(B85000,B8<10001),2 ,"Outside Range")) This has one IF statement nested within another to handle your two listed requirements, and if over 10000 then will display text 'Outside Range'. Nest further IF statements if you need to. Regards, Lee You are limited to 7 nested IF statements, not sure if this is still the case in the latest Excel version, so if you need to go beyond 7 levels of checking then another approach is probably needed. "Charlie" wrote: Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Nice :-)
Wasn't sure if the 'and so on' was 5000 increments. "Per Jessen" wrote: Hi Charlie Try his formula: =ROUNDUP(B8/5000,0) Regards, Per "Charlie" skrev i meddelelsen ... Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
=CEILING(B8,5000)/5000
If this post helps click Yes --------------- Jacob Skaria "Charlie" wrote: Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Note that Lee's formula will give "Outside Range" for a B8 value of 5000, or
of 5001, or of any value between 5000 and 5001. You may wish to think carefully about how you define your limits. Some of the alternative methods suggested may be better. -- David Biddulph "Lee Diggins" wrote in message ... Hey Charlie You could do something like this: =IF(AND(B81,B8<5001),1,IF(AND(B85000,B8<10001),2 ,"Outside Range")) This has one IF statement nested within another to handle your two listed requirements, and if over 10000 then will display text 'Outside Range'. Nest further IF statements if you need to. Regards, Lee You are limited to 7 nested IF statements, not sure if this is still the case in the latest Excel version, so if you need to go beyond 7 levels of checking then another approach is probably needed. "Charlie" wrote: Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Hi David
I agree that the other suggestions are far better. I can only get 'Outside Range' displayed for < 0 or 10000, I cannot replicate your results for the 5000 - 5001 values. Don't want this to go off topic here as this is Charlies post, but, I did get a problem with the value of 1 - ooops, so here's the updated: =IF(AND(B80,B8<5001),1,IF(AND(B85000,B8<10001),2 ,"Outside Range")) Regards, Lee "David Biddulph" wrote: Note that Lee's formula will give "Outside Range" for a B8 value of 5000, or of 5001, or of any value between 5000 and 5001. You may wish to think carefully about how you define your limits. Some of the alternative methods suggested may be better. -- David Biddulph "Lee Diggins" wrote in message ... Hey Charlie You could do something like this: =IF(AND(B81,B8<5001),1,IF(AND(B85000,B8<10001),2 ,"Outside Range")) This has one IF statement nested within another to handle your two listed requirements, and if over 10000 then will display text 'Outside Range'. Nest further IF statements if you need to. Regards, Lee You are limited to 7 nested IF statements, not sure if this is still the case in the latest Excel version, so if you need to go beyond 7 levels of checking then another approach is probably needed. "Charlie" wrote: Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Hi,
Thanks, that worked a treat :) Charlie "Per Jessen" wrote: Hi Charlie Try his formula: =ROUNDUP(B8/5000,0) Regards, Per "Charlie" skrev i meddelelsen ... Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Failing with formula
Yes, you're right, Lee. I was misreading the formula, but in that case
5000.9 is giving 1, which may or may not be what the OP wanted, so again the OP needs to be careful with defining the limits. -- David Biddulph "Lee Diggins" wrote in message ... Hi David I agree that the other suggestions are far better. I can only get 'Outside Range' displayed for < 0 or 10000, I cannot replicate your results for the 5000 - 5001 values. Don't want this to go off topic here as this is Charlies post, but, I did get a problem with the value of 1 - ooops, so here's the updated: =IF(AND(B80,B8<5001),1,IF(AND(B85000,B8<10001),2 ,"Outside Range")) Regards, Lee "David Biddulph" wrote: Note that Lee's formula will give "Outside Range" for a B8 value of 5000, or of 5001, or of any value between 5000 and 5001. You may wish to think carefully about how you define your limits. Some of the alternative methods suggested may be better. -- David Biddulph "Lee Diggins" wrote in message ... Hey Charlie You could do something like this: =IF(AND(B81,B8<5001),1,IF(AND(B85000,B8<10001),2 ,"Outside Range")) This has one IF statement nested within another to handle your two listed requirements, and if over 10000 then will display text 'Outside Range'. Nest further IF statements if you need to. Regards, Lee You are limited to 7 nested IF statements, not sure if this is still the case in the latest Excel version, so if you need to go beyond 7 levels of checking then another approach is probably needed. "Charlie" wrote: Hi, I need to create a formula the will give the following results: IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000 the result is 2 and so on. Any help would be appreciated. Many thanks Charlie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Failing IF Statement | Excel Discussion (Misc queries) | |||
| Data query failing | Excel Worksheet Functions | |||
| COUNTA failing? | Excel Discussion (Misc queries) | |||
| My formulas are failing! | Excel Discussion (Misc queries) | |||
| Link 'failing' | Links and Linking in Excel | |||
Linear Mode
