# Need formula

#### genius

##### New Member
 I want to calculate the last four column (Mr. A,B,C,D) namely "COMM AMT TO BE DISTRIBUTED" as per the cells range. The following is the condition for calculation of the said amount 1) There are four employees (A,B,C&D) 2) I have calculated 1.5% of commission for column (1) & (2) in column (7) and (8) 3) As per the conidtion of employees (PRESENT AND ABSENT) I want to calculate the commission if an employee is absent and the others three are present the commission must be calculated among these three employees equally or if two are absent the commission must be calculated for rest of two equally if all absent zero will be calculated on the basis of absent. I have applied the formula in Mr. A,B,C,D column as you may see below but the last two employees are absent but there is still the commission please help the following is the formula =CHOOSE(LOOKUP(\$E17&\$F17&\$G17&\$H17,{"AA",1;"AP",2;"PA",3;"PP",4}),0,CHOOSE(COLUMNS(\$K17:K17),0, SUM(\$I17:\$J17)) ,CHOOSE(COLUMNS(\$K17:K17),SUM(\$I17:\$J17),0),SUM(\$I17:\$J17)/4) SALES FIGURES EMPLOYEES COMM % OF SALES COMM AMT TO BE DISTRIBUTED DATE COMMISSION % LADY SHOES GENTS SHOES MR A MR B MR C MR D LADY GENTS MR A MR B Mr C Mr D 1 2 3 4 5 6 7 8 9 10 11 12 01-01-2013 1.50% 5000 10000 P P A A 75 150 56.25 56.25 56.25 56.25 02-01-2013 1.50% 6000 12000 A A A A 90 180 0 0 0 0 03-01-2013 1.50% 15000 30000 A A P P 225 450 0 0 0 0 04-01-2013 1.50% 20000 40000 A A P P 300 600 0 0 0 0 05-01-2013 1.50% 25000 50000 P A P A 375 750 1125 0 #VALUE! #VALUE! 06-01-2013 1.50% 20000 40000 A P A P 300 600 0 900 #VALUE! #VALUE! DATE % OF GENTS % OF LADY MR A MR B Mr C Mr D LADY GENT 3% FOR LADY 2% FOR GENTS MR A MR B Mr C Mr D 01-01-2013 2% 3% P P A A 5000 10000 150 200 02-01-2013 2% 3% A A A A 6000 12000 180 240 03-01-2013 2% 3% A A P P 15000 30000 450 600 04-01-2013 2% 3% A A P P 20000 40000 600 800 05-01-2013 2% 3% P A P A 25000 50000 750 1000 06-01-2013 2% 3% A P A P 20000 40000 600 800

<tbody>
</tbody>

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### genius

##### New Member
Thank you, Mr Akashwani let me check the podcast

#### Marcelo Branco

##### MrExcel MVP
Assuming data beginning in row 4 (headers and column numbers in rows 1, 2 and 3), maybe...

K4 (column Mr A)
=IF(E4="A",0,(\$I4+\$J4)/COUNTIF(\$E4:\$H4,"P"))
copy across and down

M.

#### genius

##### New Member
Thank you, Mr. Marcelo Branco, it's done thanks once again

#### Marcelo Branco

##### MrExcel MVP
Thank you, Mr. Marcelo Branco, it's done thanks once again

You are welcome and thanks for the feedback.

M.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,682
Messages
5,838,784
Members
430,568
Latest member
bortey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back