Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function (Possibly) to Apply a Value Depending on Value
In column D, I have units. I have to apply values to the number of units. If
the number is between 0-50 units, I apply 0, if between 51-200, I would need to apply .05, if between 201-500, i would need to apply .10 and so on. The numbers go above 10,000. Is this possible? I'm terrible at nested IFs. I tried and kept getting errors and didn't see any similar questions. Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function (Possibly) to Apply a Value Depending on Value
Set up a 2-column table somewhere like this:
0 0 51 0.05 201 0.1 501 0.2 and so on for your other values. Suppose this occupies cells X1 to Y8. Then you can use this formula: =VLOOKUP(D1,X$1:Y$8,2) to return the appropriate factor depending on the value in D1. Copy down as required. Hope this helps. Pete On Aug 22, 1:04*am, Toria wrote: In column D, I have units. I have to apply values to the number of units. If the number is between 0-50 units, I apply 0, if between 51-200, I would need to apply .05, if between 201-500, i would need to apply .10 and so on. *The numbers go above 10,000. Is this possible? I'm terrible at nested IFs. *I tried and kept getting errors and didn't see any similar questions. Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function (Possibly) to Apply a Value Depending on Value
"Toria" wrote:
In column D, I have units. I have to apply values to the number of units. If the number is between 0-50 units, I apply 0, if between 51-200, I would need to apply .05, if between 201-500, i would need to apply .10 and so on. The numbers go above 10,000. Is this possible? I'm terrible at nested IFs. I tried and kept getting errors and didn't see any similar questions. I don't know that "apply" means: add to something (what?), multiply, divide, etc? I will ignore that and simply demonstrate how to return the "applied" factor (0, 0.05, 0.10, etc). Also, I see no numerical progression of tiers, 0-50, 51-200, 201-500 etc. I don't know what the next tiers would be, other than the next one starts with 501. Moreover, it is unclear what the next "applied" factor would be: 0.15 (adding 0.05 for each tier), or 0.20 (doubling for each tier), or something else altogether. Consequently, I would eschew any algebraic formulation of this. Instead, I would suggest a lookup table in one form or another. That is the most flexible method, albeit not always the most efficient. It is certainly better than using nested IFs, especially for anything more than 8 tiers. If the number of tiers is small (managable), you might consider a LOOKUP() expression of the form LOOKUP(D1,{0,51,201,...},{0,0.05,0.10,...}), where you fill in the "..." with the remaining numbers. But if the number of tiers is large, you might consider a lookup table in an out-of-the-way range of cells in the workbook. For example, if X1:X20 contains the tier lower bounds 0, 51, 201 etc, and Y1:Y20 contains the corresponding "applied" factors 0, 0.05, 0.10 etc., you could use a LOOKUP() expression of the form LOOKUP(D1,X1:Y20). Hope that helps. If not, I suspect you need to provide more specifics. For example, what are __all__ the tiers breakpoints (0, 51, 201 etc), and what are __all__ the "applied" factors (0, 0.05, 0.10 etc); or what are the rules for determining those breakpoints and corresponding factors? And are the number of units in column D always integral values (whole numbers)? Also, what does "apply" mean? What do you "apply" it to, and how? Provide some numeric examples to demonstrate your expectations. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function (Possibly) to Apply a Value Depending on Value
Hi Joe,
Sorry about the lack of info. "Apply" just meant to return that value, not to perform any kind of calculation. I don't know the higher tiers either. That's a question for my boss. Thank you so much for your reply. This is awesome, and you're right, much better than many nested IFs. I'm going to work on this in the morning. "JoeU2004" wrote: "Toria" wrote: In column D, I have units. I have to apply values to the number of units. If the number is between 0-50 units, I apply 0, if between 51-200, I would need to apply .05, if between 201-500, i would need to apply .10 and so on. The numbers go above 10,000. Is this possible? I'm terrible at nested IFs. I tried and kept getting errors and didn't see any similar questions. I don't know that "apply" means: add to something (what?), multiply, divide, etc? I will ignore that and simply demonstrate how to return the "applied" factor (0, 0.05, 0.10, etc). Also, I see no numerical progression of tiers, 0-50, 51-200, 201-500 etc. I don't know what the next tiers would be, other than the next one starts with 501. Moreover, it is unclear what the next "applied" factor would be: 0.15 (adding 0.05 for each tier), or 0.20 (doubling for each tier), or something else altogether. Consequently, I would eschew any algebraic formulation of this. Instead, I would suggest a lookup table in one form or another. That is the most flexible method, albeit not always the most efficient. It is certainly better than using nested IFs, especially for anything more than 8 tiers. If the number of tiers is small (managable), you might consider a LOOKUP() expression of the form LOOKUP(D1,{0,51,201,...},{0,0.05,0.10,...}), where you fill in the "..." with the remaining numbers. But if the number of tiers is large, you might consider a lookup table in an out-of-the-way range of cells in the workbook. For example, if X1:X20 contains the tier lower bounds 0, 51, 201 etc, and Y1:Y20 contains the corresponding "applied" factors 0, 0.05, 0.10 etc., you could use a LOOKUP() expression of the form LOOKUP(D1,X1:Y20). Hope that helps. If not, I suspect you need to provide more specifics. For example, what are __all__ the tiers breakpoints (0, 51, 201 etc), and what are __all__ the "applied" factors (0, 0.05, 0.10 etc); or what are the rules for determining those breakpoints and corresponding factors? And are the number of units in column D always integral values (whole numbers)? Also, what does "apply" mean? What do you "apply" it to, and how? Provide some numeric examples to demonstrate your expectations. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function (Possibly) to Apply a Value Depending on Value
Pete,
Brilliant yet I wish I would have thought of it. I don't know why I was stuck on the IF route. Thank you so much!! "Pete_UK" wrote: Set up a 2-column table somewhere like this: 0 0 51 0.05 201 0.1 501 0.2 and so on for your other values. Suppose this occupies cells X1 to Y8. Then you can use this formula: =VLOOKUP(D1,X$1:Y$8,2) to return the appropriate factor depending on the value in D1. Copy down as required. Hope this helps. Pete On Aug 22, 1:04 am, Toria wrote: In column D, I have units. I have to apply values to the number of units. If the number is between 0-50 units, I apply 0, if between 51-200, I would need to apply .05, if between 201-500, i would need to apply .10 and so on. The numbers go above 10,000. Is this possible? I'm terrible at nested IFs. I tried and kept getting errors and didn't see any similar questions. Thank you! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function (Possibly) to Apply a Value Depending on Value
You're welcome, Toria - thanks for feeding back.
Pete On Aug 22, 2:20*am, Toria wrote: Pete, Brilliant yet I wish I would have thought of it. *I don't know why I was stuck on the IF route. Thank you so much!! "Pete_UK" wrote: Set up a 2-column table somewhere like this: * * 0 * *0 * 51 * *0.05 201 * *0.1 501 * *0.2 and so on for your other values. Suppose this occupies cells X1 to Y8. Then you can use this formula: =VLOOKUP(D1,X$1:Y$8,2) to return the appropriate factor depending on the value in D1. Copy down as required. Hope this helps. Pete On Aug 22, 1:04 am, Toria wrote: In column D, I have units. I have to apply values to the number of units. If the number is between 0-50 units, I apply 0, if between 51-200, I would need to apply .05, if between 201-500, i would need to apply .10 and so on.. *The numbers go above 10,000. Is this possible? I'm terrible at nested IFs.. *I tried and kept getting errors and didn't see any similar questions. Thank you!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum.If function but depending on 3 different columns? | Excel Worksheet Functions | |||
match function? I don't know what I can apply | Excel Worksheet Functions | |||
Need help with complicated Vlookup, or possibly other function | Excel Worksheet Functions | |||
Function or number depending on input | Excel Worksheet Functions | |||
HELP with function, possibly code! | Excel Worksheet Functions |