formula question

  • Thread starter Thread starter Sharon D.
  • Start date Start date
S

Sharon D.

I need to do a formula as follows.

If the amount in column A is < $1500 calculate it by .034. If the
amount is > $1500 then they get .00 of only the amount over %1500.

Know how to do IF statements but can't seem to figure out how to
calculate only on the amount under a certain value and not on the
amount over that value.

So results should be $1500*.034 = $51.00
00-1700 *.00 = $00.00

Total Commission $51.00

Help!!!!
Thanks if you can do this.:confused: :rolleyes:
 
Sharon

Try:

=IF(A1<1500,A1*.034,1499*.034)

if A1 is under $1500 it will multiply that value by .034 if A1 is ove
$1500 it will only multiply the first $1499 by .034, change $1499 t
you max value.

HTH
J
 
If I understand you corectly, commission is only calculated on the firs
$1500, since commision on $1500 is $51 you could put:

=IF(A1<=1500,A1*.034,51)

Regards
J
 
The answer wouldn't always be $51.00. The answer would vary according
to what the percentage is being calculated on.
 
I know, the formula will only return 51 if A1 is over 1500, if unde
1500 say 1125 it would return 38.25, isn't that what you want???

J
 
I guess I haven't explained it very well. The answer will vary
depending on what amount the formula is being calculated against. The
answer will not always be $51.00. I need to have a formula that
calculates commission on the first $1500 and not any additional money
on anything over $1500. So if a person raises $1300. The percentage is
034 - the commission would be $44.20.

If a person raises 1500. the percentage is still .034 the commission
would be % $51.00.

If the person raises 1600, the percentage is .034 for the first $1500
and the commission is still $51.00 because he doesn't get paid on the
extra $100 over 1500.
 
Let me guess.....you haven't tried it??????

The formula will not always return 51, only when the amount is over
1500.
 
Sharon, the formula that Pinmaster gave you is EXACTLY what you need.
Anything 1500 or less gets multiplied by .034 and anything over 1500 shows
only the maximum commission, which is 1500*.034. Just type it in and test
it... trust me.
 
As I stated before "Peo", I must have typed it in wrong when I tested it. My
appologies for being human.
 
Peo,

A bit off topic I suppose but I cannot see a post of JR's where he is
referring to (presumably Niek's) MIN function either in the MS NG or the
Excelforum. Can you please give me an indication of where/when it was
posted. I have noticed other posts where people seem to be replying to
posts that I cannot see even when I "show all downloaded messages"
Personally I think that it is a conspiracy again me :-)

--
Regards


Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
It was a post on another tread by the OP, I suggested this formula to
her =IF(A1<1500,A1*.034,51) but she didn't believe me so she started
this tread.

Regards
JG
 
Oops....she started this one first then she started a new one, same
question and basically similar answers were given.

JG
 
Thank you JG. I never thought about the fact that some people start
multiple threads.
--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Sharon D. said:
The answer wouldn't always be $51.00.
The answer would vary according to what
the percentage is being calculated on.
Click to expand...

I assume your point is: 0.34 is not a constant.
So change the best suggested formula to:

=MIN(A1*A2, 1500*A2)

where A2 is the commission rate (34% in your
example). To make it even more flexible:

=MIN(A1*A2, A3*A2)

where A3 is the maximum amount that is subject
to commission (1500 in your example).
 
Sandy: Yes, it was in another thread. I tested the MIN function and it
didn't work...apparently I typed it in wrong (my mistake). I was trying to
get Sharon to just try one of the formulas because she kept saying they
wouldn't work and we all knew they would. Then, "Peo" sends..."Why would she
trust you when you were wrong concerning the MIN formula?" He/she is just
FULL of the Christmas spirit, huh?
 
Back
Top