My formula

I am trying to create a formula for my consignment business and I am having a
little trouble. Here is what I need the formula to do:

If item sells for 300 or less 25% times the sales price,

If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount
betweem 300.01-500.

If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the
remaining amount between 500.01-1000.

If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15%
of 500.01-1000, + 10% of the remaining amount over $1,000.

ex. I sell a $750 guitar at my store

I recieve 25% of first $300= $75
20% of 300.01-500= $40
15% of 500.01-750=$37.5
Total Commission= $152.5

I enter the following formula:
IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75))

This formula allows me to solve the total commission for items under $300,
and Items over $300, however, I can only assign 20% commission to items over
$300. How can I create multiple rules for a single cell?

Here's a solution that I used for calculatin income tax for salary sacrificing

Col A Col B Col C

0 0 25%
300 75 20%
500 115 15%
1000 190 10%

The formula in Col B at Row 2 is =B1+C1*(A2-A1). Copy this down. Col A, Col C and Cell B1 are manually entered.

In a different cell type in this formula =VLOOKUP(E22,$A$1:$C$4,2) + (E22-VLOOKUP(E22,$A$1:$C$4,1))*VLOOKUP(E22,$A$1:$C$4,3)

This isn't the most efficient way BUT it does allow for a visual table and it is easy to adjust in the future when the commission rules in your organisation change. You may also consider naming the ranges so your formula would look like this for example

=VLOOKUP(SalePrice,CommTable,2) + (SalePrice-VLOOKUP(SalePrice,CommTable,1))*VLOOKUP(SalePrice,CommTable,3)

Cheers!

Here's one try ..

In Sheet1 (Set up a reference table)
-----------
List in A2:A5 : 0, 300.01, 500.01, 1000.01
List in B2:B5 : 25%, 20%, 15%, 10%

Put in:
C3: =B2*(A3-0.01)
C4: =B3*(A4-A3)+C3
C5: =B4*(A5-A4)+C4

In Sheet2
----------
Assume your sales are listed in E22 down

Put in F22:

=VLOOKUP(E22,Sheet1!$A$2:$C$5,3,TRUE)+(E22-TRUNC(VLOOKUP(E22,Sheet1!$A$2:$C$5,1,TRUE)))*VLOOKUP(E22,Sheet1!$A$2:$C$5,2,TRUE)

Copy F22 down

This should return your total commissions in col F

Some sample test values / returns in E22:F28 are:

750 152.5
300 75
350 85
500 115
600 130
1000 190
1500 240

(seems ok ?)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"Mr.Consignment" wrote:

> I am trying to create a formula for my consignment business and I am having a
> little trouble. Here is what I need the formula to do:
>
> If item sells for 300 or less 25% times the sales price,
>
> If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount
> betweem 300.01-500.
>
> If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the
> remaining amount between 500.01-1000.
>
> If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15%
> of 500.01-1000, + 10% of the remaining amount over $1,000.
>
> ex. I sell a $750 guitar at my store
>
> I recieve 25% of first $300= $75
> 20% of 300.01-500= $40
> 15% of 500.01-750=$37.5
> Total Commission= $152.5
>
> I enter the following formula:
> IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75))
>
> This formula allows me to solve the total commission for items under $300,
> and Items over $300, however, I can only assign 20% commission to items over
> $300. How can I create multiple rules for a single cell?

Mr Consignment,

Try:

=IF(E22<=300,E22*25%,75)+IF(AND(E22>300,E22<=500),(E22-300)*20%,(E22>300)*40
)+IF(AND(E22>500,E22<=1000),(E22-500)*15%,(E22>500)*75)+(E22>1000)*(E22-1000
)*10%

or without any IF's but with one more function call try:

=MIN(E22,300)*25%+MAX(MIN(E22-300,200)*20%,0)+MAX(MIN(E22-500,500)*15%,0)+MA
X(E22-1000,0)*10%

HTH

Sandy
--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"Mr.Consignment" <[email protected]> wrote in message
news:[email protected]...
> I am trying to create a formula for my consignment business and I am
having a
> little trouble. Here is what I need the formula to do:
>
> If item sells for 300 or less 25% times the sales price,
>
> If item sells for300.01- 500, 25% of first 300 + 20% of the remaining
amount
> betweem 300.01-500.
>
> If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the
> remaining amount between 500.01-1000.
>
> If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500,
+15%
> of 500.01-1000, + 10% of the remaining amount over $1,000.
>
> ex. I sell a $750 guitar at my store
>
> I recieve 25% of first $300= $75
> 20% of 300.01-500= $40
> 15% of 500.01-750=$37.5
> Total Commission= $152.5
>
> I enter the following formula:
> IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75))
>
> This formula allows me to solve the total commission for items under $300,
> and Items over $300, however, I can only assign 20% commission to items
over
> $300. How can I create multiple rules for a single cell?

Max wrote...
>In Sheet1 (Set up a reference table)
>-----------
>List in A2:A5 : 0, 300.01, 500.01, 1000.01
>List in B2:B5 : 25%, 20%, 15%, 10%
>
>Put in:
>C3: =B2*(A3-0.01)
>C4: =B3*(A4-A3)+C3
>C5: =B4*(A5-A4)+C4
>
>In Sheet2
>----------
>Assume your sales are listed in E22 down
>
>Put in F22:
>
>=VLOOKUP(E22,Sheet1!$A$2:$C$5,3,TRUE)+(E22-TRUNC(VLOOKUP(E22,Sheet1!$A$2:$C$5,1,TRUE)))
>*VLOOKUP(E22,Sheet1!$A$2:$C$5,2,TRUE)
....

No need to include the 4th argument to VLOOKUP when it's TRUE. What's
gained?

This could be done with a single formula without ancillary cells.

=SUMPRODUCT(IF(C6>{300;500;1000;""},{300;500;1000;""},C6)-{0;300;500;1000},
IF(C6>{0;300;500;1000},{0.25;0.2;0.15;0.1}))

See

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

In article <[email protected]>,
"Mr.Consignment" <[email protected]> wrote:

> I am trying to create a formula for my consignment business and I am having a
> little trouble. Here is what I need the formula to do:
>
> If item sells for 300 or less 25% times the sales price,
>
> If item sells for300.01- 500, 25% of first 300 + 20% of the remaining amount
> betweem 300.01-500.
>
> If it sells for 500.01-1000, 25% of first 300, +20% of 300.01-500,+ 15%the
> remaining amount between 500.01-1000.
>
> If item sells for 1000 or more, 25% of first 300, +20% of 300.01-500, +15%
> of 500.01-1000, + 10% of the remaining amount over $1,000.
>
> ex. I sell a $750 guitar at my store
>
> I recieve 25% of first $300= $75
> 20% of 300.01-500= $40
> 15% of 500.01-750=$37.5
> Total Commission= $152.5
>
> I enter the following formula:
> IF(E22<300,SUM(E22)*25%,SUM((E22-300)*20%+75))
>
> This formula allows me to solve the total commission for items under $300,
> and Items over $300, however, I can only assign 20% commission to items over
> $300. How can I create multiple rules for a single cell?

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> No need to include the 4th argument to VLOOKUP when it's TRUE. What's
> gained?

Clarity & specificity?

Given that the majority of users don't even know that there *is* a
fourth argument, I usually include it explicitly to make it clear that I
intended the lookup to return an approximate match.

What's gained by leaving it out, other than a few keystrokes?

> This could be done with a single formula without ancillary cells.

That's a far better solution, but it could be significantly simplified...


=SUMPRODUCT(--(C6>{0,300,500,1000}),(C6-{0,300,500,1000}),{0.25,-0.05,
-0.05,-0.05})

See

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

Thanks for the experienced touches, guys !
Something to aspire to ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

JE McGimpsey wrote...
>"Harlan Grove" <[email protected]> wrote:
>>No need to include the 4th argument to VLOOKUP when it's TRUE. What's
>>gained?
>
>Clarity & specificity?
....

If clarity of code were important, we wouldn't be using spreadsheets.

As for specificity, if the default value is well-defined (which it is),
there's nothing gained by including the default value of the 4th arg.
Of course, you may be using a different definition of 'specificity'.

>What's gained by leaving it out, other than a few keystrokes?

Compatibility with other spreadsheets, FWLIW these days. Also, omitting
default arguments reduces file size. And, in extreme cases, it's
necessary to do so in very long formulas in order to remain under
Excel's formula length limit.

Besides, the more keystrokes there are, the more opportunities there
are for tpyos.

In article <[email protected]>,
"Harlan Grove" <[email protected]> wrote:

> If clarity of code were important, we wouldn't be using spreadsheets.

OK, you've got me there...

> As for specificity, if the default value is well-defined (which it is),
> there's nothing gained by including the default value of the 4th arg.
> Of course, you may be using a different definition of 'specificity'.

Specificity in this case only meaning completely specifying the
arguments. Yes the fourth argument's default case is well defined, but
for me it's a habit that prevents errors. It indicates that the choice
of approximate or exact match has been explicitly made. Makes
troubleshooting easier, too - I don't have to question whether the
argument was inadvertently left out, or if an approximate match was
intended.

> >What's gained by leaving it out, other than a few keystrokes?
>
> Compatibility with other spreadsheets, FWLIW these days. Also, omitting
> default arguments reduces file size. And, in extreme cases, it's
> necessary to do so in very long formulas in order to remain under
> Excel's formula length limit.

Valid points. In my case, however, I don't have any clients that know
enough about other spreadsheets that would make this a problem, nor do I
typically do enough VLOOKUPS to make the extra bytes an issue. I don't
remember if the TRUE/FALSE is tokenized or not...

I also try hard not to have very long formulae - they're hell to
maintain, especially 6 months or more after delivery.

> Besides, the more keystrokes there are, the more opportunities there
> are for tpyos.

<g>