If I can do a calculation using basic functions, I tend to do that rather than worry about finding a more advanced function to do the same thing.
For example, I might work out an average using something like:
=SUM(A1:A3)/3
instead of using Excel's AVERAGE function.
An emailed question I received last week prompted me to re-think that approach.
The questioner had a problem with an average calculation, and wanted a formula to calculate an average, taking into account how many numbers were actually in the range. i.e. ignoring text and empty cells in both the numerator and the denominator.
My first instinct was to expand my existing approach. The SUM function will ignore anything that isn't a number anyway, so my numerator was fine.
The denominator would also need to ignore text and blanks too, for the average calculation to be correct, so I came up with:
=SUM(A1:A3)/COUNT(A1:A3)
NB: The COUNT function counts the number of cells containing a numeric value in the range.
This works, but a little more digging and I discovered that the AVERAGE function does this by default!
So the far simpler answer is:
=AVERAGE(A1:A3)
I think that's what's called re-inventing the wheel!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
The whole new look on COUNTA ;)
ReplyDeleteCOUNT returns the count of cells containing numbers.
ReplyDeleteCOUNTA returns the count of cells that contain anything (i.e. are not blank).
Thanks to everyone who pointed out my mistake. I have amended the post. That's what comes of writing a post in a hurry!
DeleteThe AGGREGATE function is going to blow your mind if you though AVERAGE was cool!
ReplyDeleteThat's a function I've never used. I've just had a look and it looks pretty powerful! Thanks for that.
Delete