If between 2 numbers, multiply

[Gee...]

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.

 

[Glenn]

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(A1>500,A1<1000),A1*.02,"other unspecified result")

 

[Gee...]

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]

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))

 

[Gee...]

I'm still running into the same problem...can you tell me how to write that
formula with multiple return possibilities?
Thank you.
g

 

[Gee...]

That did it!!
Thanks so much!
g

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))