Need formula

genius

New Member
Joined
May 14, 2009
Messages
14
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 FIGURESEMPLOYEESCOMM % OF SALESCOMM AMT TO BE DISTRIBUTED
DATECOMMISSION %LADY SHOESGENTS SHOESMR AMR BMR CMR DLADYGENTSMR AMR BMr CMr D
123456789101112
01-01-20131.50%500010000PPAA7515056.2556.2556.2556.25
02-01-20131.50%600012000AAAA901800000
03-01-20131.50%1500030000AAPP2254500000
04-01-20131.50%2000040000AAPP3006000000
05-01-20131.50%2500050000PAPA37575011250#VALUE!#VALUE!
06-01-20131.50%2000040000APAP3006000900#VALUE!#VALUE!
DATE% OF GENTS% OF LADYMR AMR BMr CMr DLADYGENT3% FOR LADY2% FOR GENTSMR AMR BMr CMr D
01-01-20132%3%PPAA500010000150200
02-01-20132%3%AAAA600012000180240
03-01-20132%3%AAPP1500030000450600
04-01-20132%3%AAPP2000040000600800
05-01-20132%3%PAPA25000500007501000
06-01-20132%3%APAP2000040000600800

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,948
Members
414,417
Latest member
Nobu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top