Weighted Average

J

Jd.willis

I need some help calculating the weighted average of a turnaround time from
month to month based on the number of transactions for each transaction type

Any help with this would be greatly appreciated.


Here is the general layout of the table:

April May
TAT Time # Patients TAT Time # Patients
Transaction Type1 35 52 55 38
Transaction Type2 131 174 79 181
Transaction Type3 72 25 92 25
Transaction Type4 94 5 141 15
Transaction Type5 48 31 61 42
Transaction Type6 579 1 290 3
Transaction Type7 367 33 100 39
Transaction Type8 218 301 122 333
Overall Total 172(avg) 622(Count)102(Avg) 676(Count)
 
T

TomPl

What does this mean?

In the first example, did you turn around 52 patients in 35 minutes or is 35
the average turn around time for the 52 patients?
Then you want the weighted average of April's and May's results for each
transaction type? Will it always be two months? Or, did you want the
weighted average of all transaction types for one month?

Confused!
 
S

Sheeloo

Assuming your data is downto row 100 then April weighted Avg will be given by
=SUMPRODUCT((B2:B100)*(C2:C100))/SUMPRODUCT(C2:C100)
Col B contains Time and Col C # of patients

Replace B with D and C with E to get the weighted average for May and so on
 
S

ShaneDevenshire

Hi,

Here is another, short, way to do it:

=SUMPRODUCT(B3:B10*C3:C10)/SUM(C3:C10)

where times are in column B and number of pat. in C.
 
S

scooper

What is the * for?
The correct syntax is =SUMPRODUCT(B3:B10,C3:C10)/SUM(C3:C10)
scooper
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top