average a column

[Leesha]

Hi,

I need to know how the obtain the average of a group of cells in a column. There will be cells without data, so the average should only include the cells with data.

Thanks,
Leesha

[mbarron]

The average function only takes cell with numeric values into consideration when calculating. Blank cells and cells with text, within the range are ignored.

[Leesha]

Thanks! I didn't realize that.

Leesha

[Leesha]

Another question, what would the formula look like if the cell contains a zero. The user doesn't want the cells with zero counted.

Thanks!

[mbarron]

If your user has a version of Excel prior to 2007 you can use the array formula (confirmed with Ctrl+Shift+Enter) of:
=AVERAGE(IF(A1:A8<>0,A1:A8))

For 2007 and up:
=AVERAGEIF(A1:A8,"<>0")

Adjust the ranges as necessary of course.

[Leesha]

Thanks!

[Leesha]

I'm not sure what is wrong with this. It's returning a value error.

Thanks,
Leesha

[HansV]

The formula is an array formula, i.e. you must confirm it with Ctrl+Shift+Enter (as Mike Barron indicated in his reply), not just with Enter.

[StuartR]

Because this is an array formula you need to enter it in the cell and type Control-Shift-Enter
If you do this it will work fine.

[Leesha]

I actually type control-shift-enter after the formula in the same cell?

[HansV]

I actually type control-shift-enter after the formula in the same cell?

Either double-click the cell, or click on the cell and press F2, or click on the cell, then in the formula bar.
Either of these will start edit mode.
Then (without actually changing the formula) press Ctrl+Shift+Enter, i.e. hold down Ctrl and Shift, press Enter, then release Ctrl and Shift.
You should see braces { } around the formula in the formula bar; this indicates that you have turned the formula into an array formula.
(Do *not* type the braces { } yourself, that won't work)

[Leesha]

Thanks! Worked perfectly!
Leesha

[Leesha]

I just wanted to let everyone know who helped me on this one that the end user is beside herself with gratitude! She thinks I'm the genius LOL but I told her its with this forum.

Thanks again,
Leesha