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>