formula help
 

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks

Hi

You cannot let A2 = A2 - 30000 or whatever. A2 = A2.
To solve your problem, you need to use another cell, in which a formula may
insert a value, and then let A2 = the value of that cell - 30000/1000 etc
--
ve_2nd_at. Randburg, Gauteng, South Africa


"dom" wrote:

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks

can you get more detailed

"Kassie" wrote:

Hi

You cannot let A2 = A2 - 30000 or whatever. A2 = A2.
To solve your problem, you need to use another cell, in which a formula may
insert a value, and then let A2 = the value of that cell - 30000/1000 etc
--
ve_2nd_at. Randburg, Gauteng, South Africa


"dom" wrote:

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks

This might help

http://www.mcgimpsey.com/excel/variablerate.html

it looks like you are calculation commissions

--
Regards,

Peo Sjoblom

(No private emails please)


"dom" wrote in message
...
can you get more detailed

"Kassie" wrote:

Hi

You cannot let A2 = A2 - 30000 or whatever. A2 = A2.
To solve your problem, you need to use another cell, in which a formula
may
insert a value, and then let A2 = the value of that cell - 30000/1000 etc
--
ve_2nd_at. Randburg, Gauteng, South Africa


"dom" wrote:

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks

You will also need to watch your evaluations. You have overlaps with 30,000
and 45,000 fitting into 2 categories each as written in your example below.
It may not do what you expect. You need to drop the equals (=) on the 2nd and
3rd, or raise the bar by 1, i.e., =30,001 and =45,001. If you have
additional tiers beyond 100,000 they will have to be added. You can only go
so far with 1 formula. How many other tiers do you have?

If you type the sales figure (?) in A1 and put this formula in A2, it
produces the commisions you listed for 30,000, 40,000 and 50,000.

=IF(A1<30001,420,IF(A1<45001,(((A1-30000)/1000)*7.25)+420,(((A1-45000)/1000)*6)+528.75))

Roy
--
(delete .nospam)




"dom" wrote:

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks

two more tiers if possible

"Roy Wagner" wrote:

You will also need to watch your evaluations. You have overlaps with 30,000
and 45,000 fitting into 2 categories each as written in your example below.
It may not do what you expect. You need to drop the equals (=) on the 2nd and
3rd, or raise the bar by 1, i.e., =30,001 and =45,001. If you have
additional tiers beyond 100,000 they will have to be added. You can only go
so far with 1 formula. How many other tiers do you have?

If you type the sales figure (?) in A1 and put this formula in A2, it
produces the commisions you listed for 30,000, 40,000 and 50,000.

=IF(A1<30001,420,IF(A1<45001,(((A1-30000)/1000)*7.25)+420,(((A1-45000)/1000)*6)+528.75))

Roy
--
(delete .nospam)




"dom" wrote:

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks

By nesting 1-2 more if's, it should work. What are the additional tiers and
examples of commissions for them?

Roy
--
(delete .nospam)




"dom" wrote:

two more tiers if possible

"Roy Wagner" wrote:

You will also need to watch your evaluations. You have overlaps with 30,000
and 45,000 fitting into 2 categories each as written in your example below.
It may not do what you expect. You need to drop the equals (=) on the 2nd and
3rd, or raise the bar by 1, i.e., =30,001 and =45,001. If you have
additional tiers beyond 100,000 they will have to be added. You can only go
so far with 1 formula. How many other tiers do you have?

If you type the sales figure (?) in A1 and put this formula in A2, it
produces the commisions you listed for 30,000, 40,000 and 50,000.

=IF(A1<30001,420,IF(A1<45001,(((A1-30000)/1000)*7.25)+420,(((A1-45000)/1000)*6)+528.75))

Roy
--
(delete .nospam)




"dom" wrote:

Hi,
I need help with this formula
A1 = value x
IF A1 <= 30000 Then A2=420.00
IF A1 =30000, but <=45000 then A2=(A2-30000/1000)*7.25+420.00
IF A1=45000, but <100000 then A2=(A2-45000/1000)*6+528.75
And so on

Could you get me past these steps?
If I type 30000 I should get 420 for an answer
If type 40000 my answer should be 492.50
If type 50000 my answer should be 558.75
Thanks