Weighted Average

[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)

 

[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!

 

[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

 

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

 

[scooper]

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