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>
 

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.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,035
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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