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.

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

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.

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.

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

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.