PDA

View Full Version : How do I average in Excel 2003?

WalOne
10-09-2009, 05:28 PM
Specifically, where I need an average of two or more cells that are themselves averages. I'm using the average function to arrive at averages for some individual cells OK, e.g. if formatting at cell a6 is =average(a1..a5), cell a16 is =average(a10-a15), the answers are correct. But if I then format a different cell with =average(a6+a16), all I get is the sum of the values, and have to insert in this case, /2 in order to show the correct figure.

I can do a convoluted calculation that will add the totals, divide that using the count function, but the idea is to produce a spreadsheet that is robust from the standpoint that individual formulae do not need adjusting to suit changing criteria.

I must be missing something here: anybody any ideas?

pcuser42
10-09-2009, 05:36 PM
The formula =average(a6+a16) is wrong. It should be =average(a6,a16) (a comma). At least that's how Excel 2007 does it. :)

kahawai chaser
10-09-2009, 05:43 PM
A weighted average should be used as it's more representative, and is based on the number of data used for the two separate averages.

WalOne
10-09-2009, 05:57 PM
Thanks guys, but I'm now away until Saturday, so won't have a chance to try those solutions until then.
:)

Renmoo
10-09-2009, 07:25 PM
Thanks guys, but I'm now away until Saturday, so won't have a chance to try those solutions until then.
:)
You can try it out for yourself on Google Docs :)

WalOne
12-09-2009, 03:12 PM
The formula =average(a6+a16) is wrong. It should be =average(a6,a16) (a comma). At least that's how Excel 2007 does it. :)

Thanks pcuser - that's the right solution, it works spot on. Thanks for the other inputs, guys.

:thanks