Average not including Zeros/Blanks

DaS

unread,

Oct 17, 2007, 11:30:03 AM10/17/07

to

I am using the following formula for an average function, but it doesn't
quite do what I need it to do.

=AVERAGE(IF(F3:F38<>0,F3:F37))

There are cells in this that I need to omit from the average, because they
are a subtotaled average for the week. I need to somehow remove F10, F18,
F26, F34, and F38 while the formula still calculates the average for the
cells that have information in them, omitting the blank cells. I did some
looking around on the forum (that's where I got the above formula) and didn't
find anything that would fit. Thanks.

Don Guillett

unread,

Oct 17, 2007, 11:36:31 AM10/17/07

to

The ARRAY formula presented should work IF you array enter it using
CTRL+SHIFT+ENTER

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"DaS" <D...@discussions.microsoft.com> wrote in message
news:5C438FC4-178D-48CA...@microsoft.com...

DaS

unread,

Oct 17, 2007, 11:44:00 AM10/17/07

to

My apoligies...I forgot to type in the brackets. It is entered as an array,
but the problem is that the "subtotaled" cells for the week, that average the
week ahead, are being included into the overall picture. I need those cells
removed from the equation.

Don Guillett

unread,

Oct 17, 2007, 11:51:29 AM10/17/07

to

You do NOT type in the brackets. You enter the formula using cse
Show your layout or send me a file to the address below along with copies of
these posts snipped onto a sheet and full details of what you desire.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"DaS" <D...@discussions.microsoft.com> wrote in message

news:92617D08-7BDB-4D37...@microsoft.com...

David Biddulph

unread,

Oct 17, 2007, 11:58:13 AM10/17/07

to

To start with, you probably need the 2 arrays to be the same length.
--
David Biddulph

"DaS" <D...@discussions.microsoft.com> wrote in message
news:5C438FC4-178D-48CA...@microsoft.com...

JE McGimpsey

unread,

Oct 17, 2007, 12:01:38 PM10/17/07

to

Well, no, it won't.

First, the ranges need to be the same size.

Second, it won't remove the cells the OP wants it to.

This should work:

=SUMPRODUCT(--(MOD(ROW(F3:F37),8)<>2), --(F3:F37<>0),F3:F37)/
SUMPRODUCT(--(MOD(ROW(F3:F37),8)<>2), --(F3:F37<>0))

In article <eY$JDONEI...@TK2MSFTNGP04.phx.gbl>,

JE McGimpsey

unread,

Oct 17, 2007, 12:03:36 PM10/17/07

to

I think the OP meant that he didn't type in the brackets in his post. He
obviously knows what array-entering means...

In article <ewsVaWNE...@TK2MSFTNGP05.phx.gbl>,

DaS

unread,

Oct 17, 2007, 12:43:02 PM10/17/07

to

That worked great. Thanks JE, also for identifying the whole "bracket"
mix-up. I hate to be a bother, but could you explain how the formula works?

JE McGimpsey

unread,

Oct 17, 2007, 1:29:22 PM10/17/07

to

Briefly:

(F3:F37<>0)

returns an array of 35 TRUE/FALSE values.

--(F3:F37<>0)

converts the array to 1/0 (see

http://www.mcgimpsey.com/excel/doubleneg.html

for more explanation).

--(MOD(ROW(F3:F37),8)=2)

returns an array of 35 1s/0s depending on whether each row's row number,
when divided by 8, has a remainder of 2.

Those arrays are multiplied to return an array of 1s/0s (1 for rows for
which the remainder<>2 and the value<>0).

In the denominator, SUMPRODUCT() then adds those 1/0 to get a count of
valid values.

In the numerator, the arrays are further multiplied by the array of
values in F3:F37. Where both the remainder <> 2 and the value <>0, the
cell's value is returned in the array. SUMPRODUCT() then adds those
values in the array.

Dividing the sum of valid values by the count of valid values gives the
average.

In article <12EEE43B-26E8-43E4...@microsoft.com>,