Commissions and lookup table
You're welcome, and thanks for the feed-back.
--
Regards,
RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------
"ub67" wrote in message
...
Can I just say - -you are AWESOME!!!
Thank you!
"Ragdyer" wrote:
First, change your column headings on Sheet2 to show *only* the lower
amounts:
0, 500.01, 1500.01, 3500.01
Then try this in D2 of Sheet1:
=C2*INDEX(Sheet2!$A$1:$E$3,MATCH(B2,Sheet2!$A$1:$A $3,0),MATCH(C2,Sheet2!$A$1
:$E$1))
And copy down as needed.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ub67" wrote in message
...
Need some help please!
I sell items on consignment and collect commission on a sliding scale.
I
have different categories for different customers. For example, I have
'John' set up for me to collect a percentage based on Table A. 'Pete'
is
set
up for Table B.
In my workbook, I have sheet 1 as follows:
Consignor Consignor ID Sale amount Commission
Amount
John A 1400.00
?
Pete B 1800.00
?
Sheet 2 has the following info:
Consignor ID $ 0 - 500 $500.01-1500 $1500.01-3500
$3500.01+
A 25% 20% 18%
15%
B 20% 20% 17%
10%
I thought that I could do a lookup formula where the commission amount
would
be calculated something like:
Look at the consignor id, then in sheet 2 find the percentage to
multiply
to
the sale amount in sheet 1.
The end result would be that I earn a commission from John of $ 280 and
from
Pete $ 306.
Any thoughts?
|