Need formula

[genius]

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>

 

[Akashwani]

Hi,

I don't have a solution for you, sorry, but see if this helps....

Dueling Excel -"An IF Formula for Commissions": Podcast #1677 - YouTube

Good luck.

Ak

 

[genius]

Thank you, Mr Akashwani let me check the podcast

 

[Marcelo Branco]

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]

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

 

[Marcelo Branco]

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

You are welcome and thanks for the feedback.

M.