My formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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 ?)
 
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
 
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)
Click to expand...
....

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}))
 
Harlan Grove said:
No need to include the 4th argument to VLOOKUP when it's TRUE. What's
gained?
Click to expand...

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.
Click to expand...

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
 
JE McGimpsey wrote...
Clarity & specificity?
Click to expand...
....

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?
Click to expand...

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.
 
Harlan Grove said:
If clarity of code were important, we wouldn't be using spreadsheets.
Click to expand...

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'.
Click to expand...

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.
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.
Click to expand...

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.
Click to expand...

<g>
 
Back
Top