Average not including Zeros/Blanks
 

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.

Average not including Zeros/Blanks
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaS" wrote in message
...
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.

Average not including Zeros/Blanks
 

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaS" wrote in message
...
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.

Average not including Zeros/Blanks
 

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

"DaS" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaS" wrote in message
...
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.

Average not including Zeros/Blanks
 

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

"DaS" wrote in message
...
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.

Average not including Zeros/Blanks
 

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 ,
"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaS" wrote in message
...
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.

Average not including Zeros/Blanks
 

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 ,
"Don Guillett" wrote:

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.

Average not including Zeros/Blanks
 

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" wrote:

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 ,
"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaS" wrote in message
...
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.

Average not including Zeros/Blanks
 

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 ,
DaS wrote:

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" wrote:

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))