Averaging cells

PaulJ

Board Regular
Joined
Dec 19, 2011
Messages
61
I have 6 cells I want average, Y13, Y15, Y17, Y19, Y21 & Y23. There are no used cells between these cells. I want the answer in Cell Z23. This number (Z23) is used for other calcs.

BUT, I want a cell with no entry to be ignored and not be a zero to be averaged. So if Y17 had no entry, the sum is divided by 5 not 6.

Thanks in anticipation.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

=AVERAGE(Y13:Y23) ignores blank cells, but doesn't ignore 0 values.

=SUM(Y13:Y23)/COUNTIF(Y13:Y23,">0") will ignore 0 values.

This assumes there is no data inbetween cells as you described.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,906
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top