|
formula
I am trying to create an IF formula that if a column is <16 but 49 then
multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
you have over lapping values. if <8 but 15 overlaps with less than 8. can
you clarify or did you not notice "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
try this idea where you work from the bottom down.
=e5*if(q550,1,if(q516,.75,if(q516,.5,if(q57,?, 0)))) -- Don Guillett SalesAid Software "Kim" wrote in message ... I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
I tried changing to to 7 or less but I got the same result "False".
"jnu" wrote: you have over lapping values. if <8 but 15 overlaps with less than 8. can you clarify or did you not notice "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5)))
"Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
Kim" wrote in message
... =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) Q516<49 is the wrong syntax for an if function try: =IF(Q550,E5,IF(and(Q516,Q5<49),E5*0.75,IF(and(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0))))--HTHSandyIn Perth, the ancient capital of with @tiscali.co.uk"Kim" wrote in ...I tried changing to to 7 or less but I got the same result "False". "jnu" wrote: you have over lapping values. if <8 but 15 overlaps with less than 8.can you clarify or did you not notice "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and49. Can someone help? |
formula
Sorry I did not work though you original formula. If Q5 has 8 or 15 then it
will be missed because we are testing for 8 and < 8 but not =8 and likewise we are testing for <15 and 16 but not =15. Try: =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q5=8<=15,E5*0 .5,IF(Q5<8,0)))) You may wish to move the "=" part to another part of the formula depending on your requirements. -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Kim" wrote in message ... I'm still getting a "false" for the range from 8 to 15. It's not an error, it's just putting in a false. "Sandy Mann" wrote: Kim" wrote in message ... =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) Q516<49 is the wrong syntax for an if function try: =IF(Q550,E5,IF(and(Q516,Q5<49),E5*0.75,IF(and(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0))))--HTHSandyIn Perth, the ancient capital of with @tiscali.co.uk"Kim" wrote in ...I tried changing to to 7 or less but I got the same result "False". "jnu" wrote: you have over lapping values. if <8 but 15 overlaps with less than 8.can you clarify or did you not notice "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and49. Can someone help? |
formula
I think you have your less than (<) and greater than () symbols mixed up (in
your comments anyways). So this is what I assume you meant... If x is less then 8, then 0 If x is greater than 8, and less than 15, then 0.5*x If x is greater than 16, and less than 49, then 0.75*x if x is greater than 50, x Your formula has a couple of errors. First, "Q516<49" and "Q58<15" have no logical meaning in excel. I believe both will return FALSE no matter what the value of Q5 actually is. You can replace "Q516<49" with something like "AND(Q516,Q5<49)". So you "could" use the following formula... =IF(Q550,E5,IF(AND(Q516,Q5<49),E5*0.75,IF(AND(Q5 8,Q5<15),E5*0.5,IF(Q5<8,0)))) but this really isn't very efficient, and still has another problem. Second, you might get wrong results because the formula produces 0 if Q5 equals 49, 50, 15, 16, and 8. For ranges you typically want to use in one part of the formula and <= in the next, like this. =IF(Q550,E5,IF(AND(Q516,Q5<=50),E5*0.75,IF(AND(Q 58,Q5<=16),E5*0.5,IF(Q5<=8,0)))) but in your example the ranges all meet. So you really don't need the AND functions at all. So the formula could be. =IF(Q550,E5,IF(Q516,E5*0.75,IF(Q58,E5*0.5,0))) notice the second statement does NOT include "Q5<=50". This is because at this point in the formula Q5 must be less than or equal to 50, or else it would trigger the first test of "Q550". Notice also the last statement has been omitted if Q5 is not greater than 8, then it must be less than or equal to 8. You can make it slightly smaller still by extracting the E5 in front of the IF statement. =E5*IF(Q550,1,IF(Q516,0.75,IF(Q5=8,0.5,0))) This formula is pretty good and efficient, but many people (like myself) don't like nested IF's and avoid them like the plague. Logical values (TRUE and FALSE) are converted to 1's and 0's when you try to apply a mathatical operator to them. So, you can use the following formula (though it might be confusing to newer users). =E5*((Q58)/2+(Q516)/4+(Q550)/4) |
formula
Thanks, that worked. I guess I was trying to make it too difficult.
"jnu" wrote: try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5))) "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
Hi,
I guess it would be more efficient to do one of these: =LOOKUP(Q5,{0;8;16;50},{0;0.5;0.75;1})*E5 =VLOOKUP(Q5,{0,0;8,0.5;16,0.75;50,1},2)*E5 =INDEX({0;0.5;0.75;1},MATCH(Q5,{0;8;16;50}))*E5 =CHOOSE(MATCH(Q5,{0;8;16;50}),0,0.5,0.75,1)*E5 The fixed arrays can of course be replaced by range references. Regards, KL "Kim" wrote in message ... Thanks, that worked. I guess I was trying to make it too difficult. "jnu" wrote: try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5))) "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by .50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
|
formula
Great thanks, very argumented answer ;-)
KL "Don Guillett" wrote in message ... not really........ -- Don Guillett SalesAid Software "KL" wrote in message ... Hi, I guess it would be more efficient to do one of these: =LOOKUP(Q5,{0;8;16;50},{0;0.5;0.75;1})*E5 =VLOOKUP(Q5,{0,0;8,0.5;16,0.75;50,1},2)*E5 =INDEX({0;0.5;0.75;1},MATCH(Q5,{0;8;16;50}))*E5 =CHOOSE(MATCH(Q5,{0;8;16;50}),0,0.5,0.75,1)*E5 The fixed arrays can of course be replaced by range references. Regards, KL "Kim" wrote in message ... Thanks, that worked. I guess I was trying to make it too difficult. "jnu" wrote: try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5))) "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by ..50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
formula
Yeah, just checked: you were right. Apart from the fact the LOOKUP formula is 16% shorter, more user-friendly, has no redundant use of variables (error opportunities) and is about 35%-40% faster on 65,536 cells, there is no reason to think my options are more efficient.
KL "KL" wrote in message ... Great thanks, very argumented answer ;-) KL "Don Guillett" wrote in message ... not really........ -- Don Guillett SalesAid Software "KL" wrote in message ... Hi, I guess it would be more efficient to do one of these: =LOOKUP(Q5,{0;8;16;50},{0;0.5;0.75;1})*E5 =VLOOKUP(Q5,{0,0;8,0.5;16,0.75;50,1},2)*E5 =INDEX({0;0.5;0.75;1},MATCH(Q5,{0;8;16;50}))*E5 =CHOOSE(MATCH(Q5,{0;8;16;50}),0,0.5,0.75,1)*E5 The fixed arrays can of course be replaced by range references. Regards, KL "Kim" wrote in message ... Thanks, that worked. I guess I was trying to make it too difficult. "jnu" wrote: try this =IF(Q5<8,0,IF(Q5<16,E5*0.5,IF(Q5<50,E5*0.75,E5))) "Kim" wrote: I am trying to create an IF formula that if a column is <16 but 49 then multiple by .75 or if the number is <8 but 15 ten multiple by ..50, if greater than 50 100%, if less than 8 = 0. Here is the formula that I have created =IF(Q550,E5,IF(Q516<49,E5*0.75,IF(Q58<15,E5*0.5 ,IF(Q5<8,0)))) It is working for all instances except numbers that are betwen 16 and 49. Can someone help? |
| All times are GMT +1. The time now is 02:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com