IF conditions returning a % sum

[VonHelson]

So I'm creating a spreadsheet to track commission pay-out across multiple pools of business. I want to minimize the risk of my staff doing the % sum themselves but I cant figure out how to use the =IF function. The problem I have is that the commission is different based upon what is sold. For example, if Product A is sold = 1.5% commission and if product B is sold = 3% commission.

I've created a list of products using data validation with the dropdown spread across cells F6 - F57. Reading up I believe that it would be an IF function so =IF(F6=ProductA but I cant figure out how to get the formula to then say "If product A is selected in cell F6, divide the figure in E6 by 1.5% and show the results in G6. Would I then need a new formula for each product to say If product B is selected in cell F6, divide the figure in E6 by 3% and show the results in G6.

Any assistance would be greatly appreciated

 

[AlanY]

see if you can get this works for you

Book1
	A	B	C	D	E	F	G
1	Product	Commission
2	A1	1.00%
3	A2	1.50%
4	A3	2.00%
5	A4	2.50%			Sale	Product	Commission
6	A5	3.00%			1000	A13	70
7	A6	3.50%
8	A7	4.00%
9	A8	4.50%
10	A9	5.00%
11	A10	5.50%
12	A11	6.00%
13	A12	6.50%
14	A13	7.00%
15	A14	7.50%
16	A15	8.00%
17	A16	8.50%
18	A17	9.00%
19	A18	9.50%
20	A19	10.00%
21	A20	10.50%
22	A21	11.00%
23	A22	11.50%
24	A23	12.00%
25	A24	12.50%
26	A25	13.00%
Sheet1

Cell Formulas
Range	Formula
G6	=E6*VLOOKUP(F6,A2:B26,2,FALSE)

 

[VonHelson]

see if you can get this works for you

	A	B	C	D	E	F	G
1	Product	Commission
2	A1	1.00%
3	A2	1.50%
4	A3	2.00%
5	A4	2.50%			Sale	Product	Commission
6	A5	3.00%			1000	A13	70
7	A6	3.50%
8	A7	4.00%
9	A8	4.50%
10	A9	5.00%
11	A10	5.50%
12	A11	6.00%
13	A12	6.50%
14	A13	7.00%
15	A14	7.50%
16	A15	8.00%
17	A16	8.50%
18	A17	9.00%
19	A18	9.50%
20	A19	10.00%
21	A20	10.50%
22	A21	11.00%
23	A22	11.50%
24	A23	12.00%
25	A24	12.50%
26	A25	13.00%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G6 =E6*VLOOKUP(F6,A2:B26,2,FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I see what your going for but the problem I've got is that the spreadsheet is to be used by our finance team to reconcile pay-outs. As such we need to track the sheet on a monthly basis. What I've created looks like this -

Product Product Pool Commission £20,000.00 Pool1 £300.00 £38,000.00 Pool3 £1140.00 <tbody> </tbody>
In this example, the product value in cell 1 is £20,000.00. The product is tied to the business pool1 which carries a commission rate of 1.5% etc In the product pool column I've created a dropdown box to cover our 5 pools of business so I need to figure out how to automate the commission calc based on those 5 pools so our finance guys can pull the commission off their daily reporting and refer back to the tracker sheet in order to reconcile the amounts and approve the pay-out.

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[AlanY]

may be I didn't quite get your requirements.

Formula in G6 (and copy down to G7 etc) will lookup the product pool in F6 and read up the % from the commission table in $A$2:$B$6 and multiple the product in E6 to yield the commission in G6.

Book1
	A	B	C	D	E	F	G
1	Product	Commission
2	Pool1	1.50%
3	Pool2	2.00%
4	Pool3	2.50%
5	Pool4	3.00%			Product	Product Pool	Commission
6	Pool5	3.50%			£20,000.00	Pool1	£300.00
7					£38,000.00	Pool4	£1,140.00
Sheet1

Cell Formulas
Range	Formula
G6	=E6*VLOOKUP(F6,$A$2:$B$6,2,FALSE)