complex AVG formula

What results are you expecting for Person IP ?

I'm not sure I follow your logic but I'll take a chance on this.

@koosh1986 : In your original spreadsheet column C is called AVG.

Are those values ALREADY averages cos mathematically you can't add averages together to produce an overall average.
If no then I have to query what that column is because it certainly isn't an average so what does it represent?

https://uk.answers.yahoo.com/questio...6075127AAnfArl

If they are just ordinary values and you're adding/dividng them to obtain an overall average then thats ok.

As an example consider this:

The average of 2,3,4 is 3
The average of 6,8,13 is 9

But the average of ALL those values is NOT 12 (3 + 9) it is
(2+3+4+6+8+13) / 6 = 6

Hence you CAN'T add averages.

Hi,

As Special-K has remarked, if those numbers are themselves averages from a larger data set then mathematically speaking it's not correct to average them without weighting them - i.e. using the original data.

However

Formula:

Please Login or Register  to view this content.

will give you the avaerage of those numbers.

And if you mean the count of the column E star ratings then

Formula:

Please Login or Register  to view this content.

Hmm... So this makes more sense then?

Originally Posted by Jacc

Hmm... So this makes more sense then?

Not necessarily

It all depends on what you are trying to demonstrate. For instance it's not clear to me why you average D2:R2 when seemingly column D is a different category of number to the star ratings that appear in E:R, or at least it carries a different descriptor.

For instance, take IP as an example.
If we include column D in the calculation of averages then there are 6 rows, containing 15 numbers totalling 35 giving an average of 2.3333
However if you simply take the 6 rows and the 6 'averages' you show in column C then the 'average' of those averages is 2.272

Similarly
If we use just column E in the calculation of averages then there are 6 rows, containing 6 numbers totalling 15 giving an average of 2.5
However if you simply take the 6 rows and the 6 'averages' you show in column C then the 6 numbers total 13.6667, giving an 'average' of 2.2777

The attached shows you what I think you're asking for but whether in the light of the above it's what you actually want is a moot point.

Hi,

See attached.

And just to throw another Q. in to the pot. In terms of the average star rating should a rating of 3 be given more weight than a rating of 1?
For instance currently IP on row 2 returns an average of 2.5 being the sum of 10 divided by 4 values.

If the ratings were weighted so the you had 2,2,3,3,3,3,3,3,2,2, A sum of 26 divided by a count of 10 the average would be 2.6.