Calculation based on grouping 1

[OCM]

Greetings,
Given the attache sample, I would like to calculate as follows:

1. Sum ‘Amounts’ (column J)  $8,265.83
2. Sum ‘Units’ (column I)  59.00
3. Sum of ‘Amounts’/Sum of ‘Units’  $8,265.83/59 =$140.10
4. Subtract 24 from total units (step #2 above)  59 – 24 = 35
5. Finally, multiply (step #3) by (step # 4) above $140.10 * 35 = $4,903.5

So, total for Provider #001 = $4,903.5

Once I obtained totals for each provider, I would like to display the grand total for all.

TIA,


OCM

 

[SkipVought]

Hi,

Oh, the plague of merged cells!!! Say it ain't so, Joe! The next reporting period you have merging and unmerging to do yet again.

And you posted a blasted picture. Worthless, at least to me. This is Excel. Upload a workbook with a working example, please.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So here's a solution sans merged cells, and using Excel's wonderful Structured Table feature...
[pre]
Provider ID Code Units Amounts SumUnits SumAmts Total recovery amount
001 Ad 15 2976.74 59 8295.83 4921.255085
001 Ad 24 2032.43 0 0 0
001 Ad 20 3286.66 0 0 0
002 Am 0 0 0
...
[/pre]

The last three columns are calculated fields, formulas from Row 2...
[tt]
SumUnits: =IF([@[Provider ID]]<>$A1,SUMPRODUCT(([Provider ID]=[@[Provider ID]])*([Units])),0)
SumAmts: =IF([@[Provider ID]]<>$A1,SUMPRODUCT(([Provider ID]=[@[Provider ID]])*([Amounts])),0)
Total recovery amount: =IFERROR(IF([@[Provider ID]]<>$A1,[@SumAmts]/[@SumUnits]*([@SumUnits]-24),0),0)
[/tt]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[macropod]

Cross-posted at:

http://www.msofficeforums.com/excel/34967-group-ids-calculate.html

Please read Tek-Tip's policy on Cross-Posting:

http://www.tek-tips.com/market.cfm

Cheers
Paul Edstein
[MS MVP - Word]

 

[OCM]

Skip,

Thank you for the reply post. I’ll try your method and post back.
Sorry for not attaching a workbook sample.

Regards,


OCM

 

[SkipVought]

I constructed that table and generated the formulas in about 10 minutes.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OCM]

Thanks for providing the formulas that works for what I’m trying to achieve. This is the first time I learned about the use of @ symbol in excel.

I appreciate your help

Regards,


OCM

 

[SkipVought]

Works in Structured Table references.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]