Average Calculation problem

[#1]

Hi all,

I have a data as per the table below. I am trying to calculate an average and have used sum(cost)/sum(activity). This works fine for the total ie 1000/500 = 2 and for the ward 600/500=1.2.

However, theatre is returning 400/2 = 200 as only 2 patients used the theatre. I would like an average that returns 400/500 = 0.8 as an average cost for all the patients.

Any help gratefully received.

thanks

ian

Cost type

Costtype	Total Cost	Total Activity
Total	1000	500
Ward	600	500
Theatre	400	2

[#2]

Try this,

Sum(if(costtype='Theatre',Total Cost) / Sum(if(costtype='Ward',Total Activity)

[MK_QSL]

Try

sum(cost)/sum(TOTAL activity)

or

sum(cost)/sum({1}activity)

or

sum(cost)/COUNT(DISTINCT activity)

or

sum(cost)/sum(DISTINCT activity)

[#4 · Accepted solution]

Hi Ian

Try

sum(cost)/sum(Total activity).

best regards