Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
How can I create a formula for the following:
if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
Here's one way:
=D8*IF(A2<=25,0.1,IF(A2<=50,0.2,IF(A2<=100,0.4,0.5 ))) If you decide to have more conditions, then you might consider a lookup table. You can use B4, B5, B6 etc in the formula instead of the actual values I have posted. Hope this helps. Pete On Oct 28, 1:19 am, JP wrote: How can I create a formula for the following: if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
You are writing <= when you mean = and vice versa. I hope that isn't spilling
over into your formulas... =IF(A1=100,D8*.5,IF(A1=50,D8*.4,IF(A2=25,D8*.2, IF(A2=0,D8*.1)))) You say 100-200, but the above will multiply D8 by 0.5 if A2 is 200. Is that possible? There's some confusion in your verbal description re the boundary values. You say 25-50, and then 50-100. I've assumed that 50 belongs in the 2nd category rather than the 1st. If that's not the case, change all of the = to Another possibility is =D8*CHOOSE(A2/25+1,.1, .2, .4, .4, .5) It's using these ranges, 0-24.99, 25-49.99, 50-74.99, 75-100, 100, where the multiplier for the 3rd and 4th ranges is the same. You could also put your bin boundaries and multipliers in table elsewhere on the sheet and use VLOOKUP, but that may be overkill for just 4 categories. On Sat, 27 Oct 2007 18:19:01 -0700, JP wrote: How can I create a formula for the following: if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
Your phrasing is a bit ambiguous...
If your ranges are actually: A2 =0 AND A2 <=25 A2 25 AND A2 <=50 A2 50 AND A2 <=100 A2 100 and... B4: 0.1 B5: 0.2 B6: 0.4 B7: 0.5 Then try this: =D8*INDEX(B4:B7,MATCH(1,FREQUENCY(A2,25*{1,2,4}),0 )) Does that help? If no, can you clarify the specifications? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "JP" wrote in message ... How can I create a formula for the following: if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
Another possibility:
=D8*INDEX(B4:B7,SUM(--(A2{0,25,50,100}))) Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... Your phrasing is a bit ambiguous... If your ranges are actually: A2 =0 AND A2 <=25 A2 25 AND A2 <=50 A2 50 AND A2 <=100 A2 100 and... B4: 0.1 B5: 0.2 B6: 0.4 B7: 0.5 Then try this: =D8*INDEX(B4:B7,MATCH(1,FREQUENCY(A2,25*{1,2,4}),0 )) Does that help? If no, can you clarify the specifications? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "JP" wrote in message ... How can I create a formula for the following: if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
Thank you so much for your response. I have a few questions and would like
to changes the ranges. M22 will hold the range values, and K24 will hold the constant. So how does this look =IF(M22=200,K24*K27,IF(M22=100,K24*I27,IF(M22=5 0,K27*H27,IF(M22=25,K27*G27,IF(M22<25,K27*0))))) New Ranges: 0-24.99 *0 25-49.99 *G27 (.1) 50-99.99 *H27 (.2) 100-199.99 *I27 (.4) (Greater than) 200 *K27 (.5) Did I use the correctly? "Myrna Larson" wrote: You are writing <= when you mean = and vice versa. I hope that isn't spilling over into your formulas... =IF(A1=100,D8*.5,IF(A1=50,D8*.4,IF(A2=25,D8*.2, IF(A2=0,D8*.1)))) You say 100-200, but the above will multiply D8 by 0.5 if A2 is 200. Is that possible? There's some confusion in your verbal description re the boundary values. You say 25-50, and then 50-100. I've assumed that 50 belongs in the 2nd category rather than the 1st. If that's not the case, change all of the = to Another possibility is =D8*CHOOSE(A2/25+1,.1, .2, .4, .4, .5) It's using these ranges, 0-24.99, 25-49.99, 50-74.99, 75-100, 100, where the multiplier for the 3rd and 4th ranges is the same. You could also put your bin boundaries and multipliers in table elsewhere on the sheet and use VLOOKUP, but that may be overkill for just 4 categories. On Sat, 27 Oct 2007 18:19:01 -0700, JP wrote: How can I create a formula for the following: if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a formula with conditions
Try this:
=K24*CHOOSE(SUM(--(M22={0,25,50,100,200})),0,G27,H27,I27,K27) Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "JP" wrote in message ... Thank you so much for your response. I have a few questions and would like to changes the ranges. M22 will hold the range values, and K24 will hold the constant. So how does this look =IF(M22=200,K24*K27,IF(M22=100,K24*I27,IF(M22=5 0,K27*H27,IF(M22=25,K27*G27,IF(M22<25,K27*0))))) New Ranges: 0-24.99 *0 25-49.99 *G27 (.1) 50-99.99 *H27 (.2) 100-199.99 *I27 (.4) (Greater than) 200 *K27 (.5) Did I use the correctly? "Myrna Larson" wrote: You are writing <= when you mean = and vice versa. I hope that isn't spilling over into your formulas... =IF(A1=100,D8*.5,IF(A1=50,D8*.4,IF(A2=25,D8*.2, IF(A2=0,D8*.1)))) You say 100-200, but the above will multiply D8 by 0.5 if A2 is 200. Is that possible? There's some confusion in your verbal description re the boundary values. You say 25-50, and then 50-100. I've assumed that 50 belongs in the 2nd category rather than the 1st. If that's not the case, change all of the = to Another possibility is =D8*CHOOSE(A2/25+1,.1, .2, .4, .4, .5) It's using these ranges, 0-24.99, 25-49.99, 50-74.99, 75-100, 100, where the multiplier for the 3rd and 4th ranges is the same. You could also put your bin boundaries and multipliers in table elsewhere on the sheet and use VLOOKUP, but that may be overkill for just 4 categories. On Sat, 27 Oct 2007 18:19:01 -0700, JP wrote: How can I create a formula for the following: if A2 is <= 25 then * D8 by B4, and if A2 is <=25 but =50 then *D8 by B5, and if A2 is<=50 but =100 then * D8 by B6? So essentially if A2 is 0-25 then multiply D8 (a constant) by .10 and if A2 is 25-50, then multiply D8 by .2 and if A2 is 50-100, then multiply D8 by .4 and if A2 is 100-200, then multiply D8 by .5 Thanks for any help. JP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a formula that meets 2 different conditions... | Excel Discussion (Misc queries) | |||
Formula With Several Conditions | Excel Worksheet Functions | |||
How do I create a formula that has circular conditions? | Excel Discussion (Misc queries) | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions |