Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula for expressing multiple greater/less than
I am trying to construct a formula that will be based on the result of
an adjoining cell. I need to point to specific cell depending on what the number is in that adjoining cell. So for example: If D1 = 0 or 1 - point to B21 If D1 = 2, 3, 4, or 5 - point to B22 If D1 = 6,7,8,9, or 10 - point to B23 and so on... Rather than listing all of those variables, I know I should be able to write it as equal to/greater than, but less than. I just don't know how to express as a formula. Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula for expressing multiple greater/less than
Well, "and so on..." implies that you have other conditions, so it
might be better to think about using a lookup table and a VLOOKUP formula, but for just the values you have quoted in your example, you can do this: =IF(D1<=1,B21,IF(D1<=5,B22,IF(D1<=10,B23,"and so on..."))) Note that in XL2003 and earlier there is a limit of 8 in the number of times you can put IFs together in this way - hence a table might be needed if you have more. Hope this helps. Pete On Jan 12, 4:57*pm, charlie wrote: I am trying to construct a formula that will be based on the result of an adjoining cell. I need to point to specific cell depending on what the number is in that adjoining cell. So for example: If D1 = 0 or 1 - point to B21 If D1 = 2, 3, 4, or 5 - point to B22 If D1 = 6,7,8,9, or 10 - point to B23 and so on... Rather than listing all of those variables, I know I should be able to write it as equal to/greater than, but less than. I just don't know how to express as a formula. Thanks in advance for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula for expressing multiple greater/less than
IF will give allow you to have upto 8 conditions. If you have more then
Enter 0 in A21, 2 in A22 and 6 in A23 then use this formula =VLOOKUP(D1,$A$21:$B$23,2,TRUE) You can enter more values A23... down and extend the range in the formula accordingly "charlie" wrote: I am trying to construct a formula that will be based on the result of an adjoining cell. I need to point to specific cell depending on what the number is in that adjoining cell. So for example: If D1 = 0 or 1 - point to B21 If D1 = 2, 3, 4, or 5 - point to B22 If D1 = 6,7,8,9, or 10 - point to B23 and so on... Rather than listing all of those variables, I know I should be able to write it as equal to/greater than, but less than. I just don't know how to express as a formula. Thanks in advance for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula for expressing multiple greater/less than
You haven't told us what to do if D1 is non-integer, or negative, or greater
than 10, but if none of those cases are possible you could use: =IF(D1<=1,B21,IF(D1<=5,B22,B23)) -- David Biddulph "charlie" wrote in message ... I am trying to construct a formula that will be based on the result of an adjoining cell. I need to point to specific cell depending on what the number is in that adjoining cell. So for example: If D1 = 0 or 1 - point to B21 If D1 = 2, 3, 4, or 5 - point to B22 If D1 = 6,7,8,9, or 10 - point to B23 and so on... Rather than listing all of those variables, I know I should be able to write it as equal to/greater than, but less than. I just don't know how to express as a formula. Thanks in advance for your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula for expressing multiple greater/less than
Pete - your answer was perfect for my purposes. I don't expect that
there will be more 5-6 potential IF statements, so this works great. Thanks to all for your responses and assistance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with formula for expressing multiple greater/less than
You're welcome, Charlie - thanks for feeding back.
Pete On Jan 12, 6:37*pm, charlie wrote: Pete - your answer was perfect for my purposes. I don't expect that there will be more 5-6 potential IF statements, so this works great. Thanks to all for your responses and assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to determine if multiple dates are "greater than" one date | Excel Worksheet Functions | |||
Expressing a number in words | Excel Discussion (Misc queries) | |||
Expressing a number in words | Excel Worksheet Functions | |||
Expressing percentages in Excel | Excel Discussion (Misc queries) | |||
expressing minutes negatively | Excel Discussion (Misc queries) |