Calculating overall averages

[#1]

Hi Everyone,

Suppose I have the following dataset:

Practice     Patients

A               1000

B               2000

C               6000

What's the formula I need to be able to refer to the OVERALL average for the entire dataset?

I plan to use this to format the cell backgrounds accordingly so that practices A and B would be green as they are below the 3000 average and practice C would be red as they are above the overall 3000 average.

I know how to do the formatting bit, just can't get the equation right to calculate the overall average.

Many thanks,

Robin

[Anonymous]

avg({1} Patients)

This should work I think

[#3]

Thanks Johannes but unfortunately this hasn't worked.

[jonathandienst]

Robin

In the absence of information why Johannes' selection does not work or details of your data structure, I guess that you need something like

=Avg({1} Aggr(Count(Patients), Practice))

Hope that helps

Jonathan

[#5]

Thanks again, Jonathan but, this hasn't worked either.
Assume the data is exactly as in the original post and I've formatted as follows:

=

if(PATIENTS > Avg({1} Aggr(Count(Patients), Practice)),RGB(255,0,0),RGB

(0,255,0))

But, all the cells show green.

Hope this helps.

[jonathandienst]

OK

Firstly - QV field names are case sensitive, so should the field be PATIENTS or Patients? This expression will fail because one of the two sides of the compare will be an error and treated as null.

Secondly - are you doing this in a straight/pivot table or chart? If so, what dimensions are you using? This may mean that PATIENTS has no dtisinct value, but you are comparing a field value with a count of fields.

If you say that the data structure is like the table in the initial post, then the first expression is certainly correct, but I suspect the problem is in the comparison expression - but I am just guessing.

If you post your application or a sample of it, then someone may be able to assist without wild guesses.

Regards

Jonathan

[johnw]

Perhaps this:

avg({1} total Patients)

But yeah, we're kind of shooting in the dark.  As Jonathan suggests, if you really want an answer, post a sample application.  This would be trivial to solve if we had the right information.