Weighted Avg calc?

  • Thread starter Thread starter perryclisbee via AccessMonster.com
  • Start date Start date
P

perryclisbee via AccessMonster.com

I'm trying to replicate what SUMPRODUCT does in Excel to calculate a weighted
average. See the following example:

I have 2 patients under the PPO Plan Type.

*Patient 1 has an RPT (rate per treatment) of $251.56, and has had 52
treatments.
*Patient 2 has an RPT of $1,659, and has had 300 treatments.

I need the weighted average RPT (a single result) for the combination of
these 2 patients, as they are under the same plan type.
In Excel, using the SUMPRODUCT calc (ie: =SUMPRODUCT(R8:R9,S8:S9)/SUM(S8:S9),
....R8:R9 being the RPT Values, S6:S9 being the treatment values), the result
would be: $1451.08 RPT. I need the same result to be created in Access.
Patient counts do not matter, just the RPT variables.

Any suggestions on how this could be done?

Thanks,

Perry
 
Is there a reason you don't simply export the raw data to Excel and do the
calculation there, where the functions exist?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
perryclisbee said:
I'm trying to replicate what SUMPRODUCT does in Excel to calculate a weighted
average. See the following example:

I have 2 patients under the PPO Plan Type.

*Patient 1 has an RPT (rate per treatment) of $251.56, and has had 52
treatments.
*Patient 2 has an RPT of $1,659, and has had 300 treatments.

I need the weighted average RPT (a single result) for the combination of
these 2 patients, as they are under the same plan type.
In Excel, using the SUMPRODUCT calc (ie: =SUMPRODUCT(R8:R9,S8:S9)/SUM(S8:S9),
...R8:R9 being the RPT Values, S6:S9 being the treatment values), the result
would be: $1451.08 RPT. I need the same result to be created in Access.
Patient counts do not matter, just the RPT variables.
Click to expand...


Will so what you want"

Sum(RPT * Rate) / Sum(Rate)
 
Back
Top