formula question

[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.

[pinmaster]

Sharon

Try:

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

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

HTH
JG

[Sharon D.]

The formula by Pinmaster didn't work. Anyone else have an idea?

[pinmaster]

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

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

Regards
JG

[Sharon D.]

The answer wouldn't always be $51.00. The answer would vary according to what the percentage is being calculated on.

[Niek Otten]

=MIN(51,A1*.034)

--
Kind regards,

Niek Otten

"Sharon D." <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> Sharon D.
> ------------------------------------------------------------------------
> Sharon D.'s Profile:
> http://www.excelforum.com/member.php...o&userid=29841
> View this thread: http://www.excelforum.com/showthread...hreadid=495470
>

[pinmaster]

I know, the formula will only return 51 if A1 is over 1500, if under 1500 say 1125 it would return 38.25, isn't that what you want???

JG

[Sharon D.]

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.

[pinmaster]

Let me guess.....you haven't tried it??????

The formula will not always return 51, only when the amount is over 1500.

[JR]

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.

"Sharon D." wrote:

>
> 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.
>
>
> --
> Sharon D.
> ------------------------------------------------------------------------
> Sharon D.'s Profile: http://www.excelforum.com/member.php...o&userid=29841
> View this thread: http://www.excelforum.com/showthread...hreadid=495470
>
>

[Peo Sjoblom]

Why would she trust you when you were wrong concerning the MIN formula?

--

Regards,

Peo Sjoblom

"JR" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Sharon D." wrote:
>
> >
> > 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.
> >
> >
> > --
> > Sharon D.
> > ------------------------------------------------------------------------
> > Sharon D.'s Profile:
http://www.excelforum.com/member.php...o&userid=29841
> > View this thread:
http://www.excelforum.com/showthread...hreadid=495470
> >
> >

[JR]

As I stated before "Peo", I must have typed it in wrong when I tested it. My
appologies for being human.

"Peo Sjoblom" wrote:

> Why would she trust you when you were wrong concerning the MIN formula?
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "JR" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> > "Sharon D." wrote:
> >
> > >
> > > 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.
> > >
> > >
> > > --
> > > Sharon D.
> > > ------------------------------------------------------------------------
> > > Sharon D.'s Profile:
> http://www.excelforum.com/member.php...o&userid=29841
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=495470
> > >
> > >
>
>
>

[Sandy Mann]

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
[email protected]
[email protected] with @tiscali.co.uk

"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Why would she trust you when you were wrong concerning the MIN formula?
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "JR" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>> "Sharon D." wrote:
>>
>> >
>> > 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.
>> >
>> >
>> > --
>> > Sharon D.
>> > ------------------------------------------------------------------------
>> > Sharon D.'s Profile:
> http://www.excelforum.com/member.php...o&userid=29841
>> > View this thread:
> http://www.excelforum.com/showthread...hreadid=495470
>> >
>> >
>
>

[pinmaster]

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

[pinmaster]

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

JG

[Sandy Mann]

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

Sandy
[email protected]
[email protected] with @tiscali.co.uk


"pinmaster" <[email protected]> wrote
in message news:[email protected]...
>
> Oops....she started this one first then she started a new one, same
> question and basically similar answers were given.
>
> JG
>
>
> --
> pinmaster
> ------------------------------------------------------------------------
> pinmaster's Profile:
> http://www.excelforum.com/member.php...fo&userid=6261
> View this thread: http://www.excelforum.com/showthread...hreadid=495470
>

[[email protected]]

"Sharon D." wrote:
> The answer wouldn't always be $51.00.
> The answer would vary according to what
> the percentage is being calculated on.

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

[JR]

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?



"Sandy Mann" wrote:

> Thank you JG. I never thought about the fact that some people start
> multiple threads.
> --
> Regards
>
> Sandy
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "pinmaster" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Oops....she started this one first then she started a new one, same
> > question and basically similar answers were given.
> >
> > JG
> >
> >
> > --
> > pinmaster
> > ------------------------------------------------------------------------
> > pinmaster's Profile:
> > http://www.excelforum.com/member.php...fo&userid=6261
> > View this thread: http://www.excelforum.com/showthread...hreadid=495470
> >
>
>
>