U
ub67
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?
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?