If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If between 2 numbers, multiply
I know this will be simple, but I just can't get my head around it. I need a
formula that will calculate a percentage: If A1 500 but 1000 then A1*.02 See what I mean? if it's between those 2 numbers then it gets multiplied by ..02. Thank you in advance for your help. G. |
#2
|
|||
|
|||
If between 2 numbers, multiply
Gee... wrote:
I know this will be simple, but I just can't get my head around it. I need a formula that will calculate a percentage: If A1 500 but 1000 then A1*.02 See what I mean? if it's between those 2 numbers then it gets multiplied by .02. Thank you in advance for your help. G. =IF(AND(A1500,A11000),A1*.02,"other unspecified result") |
#3
|
|||
|
|||
If between 2 numbers, multiply
I need a bit more help. The "Something Else" is another whole set of
numbers. I tried this but it says my formula has an error: IF(AND(A2=0,A2=100,A2*0),(AND(A2=101,A2=500,A2 *.02),(AND(A2=501,A2=1500,A2*.018),(AND(A2=1501 ,A2=2500,A2*.015),(AND(A2=2501,A2=4500,A2*.012) ,(AND(A2=4501,A2=5000,A2*.01))) "Roger Govier" wrote: Hi =IF(AND(A1=500,A1=1000),A1*0.2,"Something else") I assume you want to include 500 and 1000 in your calculation. If not remove the two = signs. Replace Something Else with whatever you want to see if condition is not met. -- Regards Roger Govier "Gee..." wrote in message ... I know this will be simple, but I just can't get my head around it. I need a formula that will calculate a percentage: If A1 500 but 1000 then A1*.02 See what I mean? if it's between those 2 numbers then it gets multiplied by .02. Thank you in advance for your help. G. |
#4
|
|||
|
|||
If between 2 numbers, multiply
Hi
that won't work as you have not closed the AND functions with a closing parenthesis, and you would need to repeat the IF's IF(AND(A2=0,A2=100),A2*0,IF(AND(A2=101,A2=500) ,A2*.02,IF(AND ..... It is much easier to understand and maintain a lookup table rather than having nested formulae like this. (There is a limit of 7 levels of nesting in XL2003 and lower) On another sheet, in columns A and B enter 0 0 100 0.20 500 0.18 1500 0.15 2500 0.12 4500 0.10 5000 Mark this range of cells and in the Name box (just to left of column A) type myTable and press Enter On your main sheet the formula then becomes =IF(A1="","",A1*VLOOKUP(A1,myTable,2,1)) -- Regards Roger Govier "Gee..." wrote in message news I need a bit more help. The "Something Else" is another whole set of numbers. I tried this but it says my formula has an error: IF(AND(A2=0,A2=100,A2*0),(AND(A2=101,A2=500,A2 *.02),(AND(A2=501,A2=1500,A2*.018),(AND(A2=1501 ,A2=2500,A2*.015),(AND(A2=2501,A2=4500,A2*.012) ,(AND(A2=4501,A2=5000,A2*.01))) "Roger Govier" wrote: Hi =IF(AND(A1=500,A1=1000),A1*0.2,"Something else") I assume you want to include 500 and 1000 in your calculation. If not remove the two = signs. Replace Something Else with whatever you want to see if condition is not met. -- Regards Roger Govier "Gee..." wrote in message ... I know this will be simple, but I just can't get my head around it. I need a formula that will calculate a percentage: If A1 500 but 1000 then A1*.02 See what I mean? if it's between those 2 numbers then it gets multiplied by .02. Thank you in advance for your help. G. |
#5
|
|||
|
|||
If between 2 numbers, multiply
I'm still running into the same problem...can you tell me how to write that
formula with multiple return possibilities? Thank you. g "Glenn" wrote: Gee... wrote: I know this will be simple, but I just can't get my head around it. I need a formula that will calculate a percentage: If A1 500 but 1000 then A1*.02 See what I mean? if it's between those 2 numbers then it gets multiplied by .02. Thank you in advance for your help. G. =IF(AND(A1500,A11000),A1*.02,"other unspecified result") |
#6
|
|||
|
|||
If between 2 numbers, multiply
That did it!!
Thanks so much! g "Roger Govier" wrote: Hi that won't work as you have not closed the AND functions with a closing parenthesis, and you would need to repeat the IF's IF(AND(A2=0,A2=100),A2*0,IF(AND(A2=101,A2=500) ,A2*.02,IF(AND ..... It is much easier to understand and maintain a lookup table rather than having nested formulae like this. (There is a limit of 7 levels of nesting in XL2003 and lower) On another sheet, in columns A and B enter 0 0 100 0.20 500 0.18 1500 0.15 2500 0.12 4500 0.10 5000 Mark this range of cells and in the Name box (just to left of column A) type myTable and press Enter On your main sheet the formula then becomes =IF(A1="","",A1*VLOOKUP(A1,myTable,2,1)) -- Regards Roger Govier "Gee..." wrote in message news I need a bit more help. The "Something Else" is another whole set of numbers. I tried this but it says my formula has an error: IF(AND(A2=0,A2=100,A2*0),(AND(A2=101,A2=500,A2 *.02),(AND(A2=501,A2=1500,A2*.018),(AND(A2=1501 ,A2=2500,A2*.015),(AND(A2=2501,A2=4500,A2*.012) ,(AND(A2=4501,A2=5000,A2*.01))) "Roger Govier" wrote: Hi =IF(AND(A1=500,A1=1000),A1*0.2,"Something else") I assume you want to include 500 and 1000 in your calculation. If not remove the two = signs. Replace Something Else with whatever you want to see if condition is not met. -- Regards Roger Govier "Gee..." wrote in message ... I know this will be simple, but I just can't get my head around it. I need a formula that will calculate a percentage: If A1 500 but 1000 then A1*.02 See what I mean? if it's between those 2 numbers then it gets multiplied by .02. Thank you in advance for your help. G. |
Thread Tools | |
Display Modes | |
|
|