Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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,A1<1000),A1*.02,"other unspecified result") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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,A1<1000),A1*.02,"other unspecified result") |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I multiply numbers? | Excel Discussion (Misc queries) | |||
multiply a row of numbers by % | Excel Worksheet Functions | |||
how do i multiply entire columns of numbers by a % | Excel Discussion (Misc queries) | |||
How do Multiply whole numbers by decimals | Excel Worksheet Functions | |||
Formula between to numbers is true multiply | Excel Worksheet Functions |