# Average, Excluding Zeros, Non-Consecutive Range

1. ## Average, Excluding Zeros, Non-Consecutive Range

I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have
numeric zeros in some of the cells. However, I would like to exclude them,
and the cells from the calculation.

Why does this array not work?

=AVERAGE(IF((F35:F38,F42:F45,F48:F54)<>0,(F35:F38,F42:F45,F48:F54)))

2. ## Re: Average, Excluding Zeros, Non-Consecutive Range

Try...

=AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54
>0),F35:F54))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

"Coal Miner" <Coal Miner@discussions.microsoft.com> wrote:

> I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have
> numeric zeros in some of the cells. However, I would like to exclude them,
> and the cells from the calculation.
>
> Why does this array not work?
>
> =AVERAGE(IF((F35:F38,F42:F45,F48:F54)<>0,(F35:F38,F42:F45,F48:F54)))

3. ## Re: Average, Excluding Zeros, Non-Consecutive Range

OK! That worked great on some of the data rows but for some reason some of
the data rows are returning the #DIV/0! error. What the heck?!?!

"Domenic" wrote:

> Try...
>
> =AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54
> >0),F35:F54))

>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> "Coal Miner" <Coal Miner@discussions.microsoft.com> wrote:
>
> > I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that have
> > numeric zeros in some of the cells. However, I would like to exclude them,
> > and the cells from the calculation.
> >
> > Why does this array not work?
> >
> > =AVERAGE(IF((F35:F38,F42:F45,F48:F54)<>0,(F35:F38,F42:F45,F48:F54)))

>

4. ## Re: Average, Excluding Zeros, Non-Consecutive Range

You'll get that error if none of the target cells contain a value
greater than zero. In this case, the following formula will return a
blank...

=IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2),AVERAGE(IF((1-ISNUMBE
R(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54>0),F35:F54)),"")

Hope this helps!

In article <ACFB2CC6-041B-4150-93C6-3EBC6374D4FF@microsoft.com>,
"Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:

> OK! That worked great on some of the data rows but for some reason some of
> the data rows are returning the #DIV/0! error. What the heck?!?!
>
> "Domenic" wrote:
>
> > Try...
> >
> > =AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54
> > >0),F35:F54))

> >
> > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >
> > Hope this helps!
> >
> > "Coal Miner" <Coal Miner@discussions.microsoft.com> wrote:
> >
> > > I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that
> > > have
> > > numeric zeros in some of the cells. However, I would like to exclude
> > > them,
> > > and the cells from the calculation.
> > >
> > > Why does this array not work?
> > >
> > > =AVERAGE(IF((F35:F38,F42:F45,F48:F54)<>0,(F35:F38,F42:F45,F48:F54)))

> >

5. ## Re: Average, Excluding Zeros, Non-Consecutive Range

Domenic,

Thanks alot. That did fix the problem when none of the target cells
contained a value greater than zero. However, I am still getting the #DIV/0!
error in some cells. I checked to make sure formatting was correct. I
removed the "1-" portion of the formula (located immediately prior to
ISNUMBER) and this allows the formula to work in the cells that were
returning the #DIV/0!. But, that formula will now return the #DIV/0! error
in the other cells that were working correctly. What is the purpose of the
"1-" text within the formula. Maybe if I understand that I could repair
myself.

"Domenic" wrote:

> You'll get that error if none of the target cells contain a value
> greater than zero. In this case, the following formula will return a
> blank...
>
> =IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2),AVERAGE(IF((1-ISNUMBE
> R(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54>0),F35:F54)),"")
>
> Hope this helps!
>
> In article <ACFB2CC6-041B-4150-93C6-3EBC6374D4FF@microsoft.com>,
> "Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:
>
> > OK! That worked great on some of the data rows but for some reason some of
> > the data rows are returning the #DIV/0! error. What the heck?!?!
> >
> > "Domenic" wrote:
> >
> > > Try...
> > >
> > > =AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F35:F54),{39,40,41,46,47},0)))*(F35:F54
> > > >0),F35:F54))
> > >
> > > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> > >
> > > Hope this helps!
> > >
> > > In article <121BA8FE-53BC-42CA-82D6-AAADEB917F70@microsoft.com>,
> > > "Coal Miner" <Coal Miner@discussions.microsoft.com> wrote:
> > >
> > > > I am trying to average a range of numbers (F35:F38,F42:F45,F48:F54) that
> > > > have
> > > > numeric zeros in some of the cells. However, I would like to exclude
> > > > them,
> > > > and the cells from the calculation.
> > > >
> > > > Why does this array not work?
> > > >
> > > > =AVERAGE(IF((F35:F38,F42:F45,F48:F54)<>0,(F35:F38,F42:F45,F48:F54)))
> > >

>

6. ## Re: Average, Excluding Zeros, Non-Consecutive Range

=IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2),AVERAGE(IF((1-ISNUMBE
R(MATCH(ROW(F35:F54)-ROW(F35)+1,{5,6,7,12,13},0)))*(F35:F54>0),F35:F54)),
"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Does this help?

In article <AC8881EB-EBE5-479E-A742-B73DA93F53C4@microsoft.com>,
"Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:

> Domenic,
>
> Thanks alot. That did fix the problem when none of the target cells
> contained a value greater than zero. However, I am still getting the #DIV/0!
> error in some cells. I checked to make sure formatting was correct. I
> removed the "1-" portion of the formula (located immediately prior to
> ISNUMBER) and this allows the formula to work in the cells that were
> returning the #DIV/0!. But, that formula will now return the #DIV/0! error
> in the other cells that were working correctly. What is the purpose of the
> "1-" text within the formula. Maybe if I understand that I could repair
> myself.
>

7. ## Re: Average, Excluding Zeros, Non-Consecutive Range

Ok! Took care of that problem but it is not excluding f39,f40,f41,f46,f47.
I see where the values of {5,6,7,12,13} should take care of this. Maybe we
got off course somewhere. Here is a simple version of what I want to do

f
1 10
2 12
3 0
4 0
5 7

OK, I want the average of f1,f2,f3,and f5 but do not include the zero value
located in f3 in the average calculation. Also, do not include f4 in the
calc. This is overly simplified but if you get this to work, I could get the
remainder.

Thanks again.

"Domenic" wrote:

> Try the following formula instead...
>
> =IF(INDEX(FREQUENCY((F35:F38,F42:F45,F48:F54),0),2),AVERAGE(IF((1-ISNUMBE
> R(MATCH(ROW(F35:F54)-ROW(F35)+1,{5,6,7,12,13},0)))*(F35:F54>0),F35:F54)),
> "")
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Does this help?
>
> In article <AC8881EB-EBE5-479E-A742-B73DA93F53C4@microsoft.com>,
> "Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:
>
> > Domenic,
> >
> > Thanks alot. That did fix the problem when none of the target cells
> > contained a value greater than zero. However, I am still getting the #DIV/0!
> > error in some cells. I checked to make sure formatting was correct. I
> > removed the "1-" portion of the formula (located immediately prior to
> > ISNUMBER) and this allows the formula to work in the cells that were
> > returning the #DIV/0!. But, that formula will now return the #DIV/0! error
> > in the other cells that were working correctly. What is the purpose of the
> > "1-" text within the formula. Maybe if I understand that I could repair
> > myself.
> >

>

8. ## Re: Average, Excluding Zeros, Non-Consecutive Range

Okay, in this case, you would use the following formula...

=IF(INDEX(FREQUENCY((F1:F3,F5),0),2),AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F1:
F5),{4},0)))*(F1:F5>0),F1:F5)),"")

....confirmed with CONTROL+SHIFT+ENTER. Note that the number in the
array constant --- {4} --- determines which row to exclude, in this case
row 4 or F4.

If, for example, you wanted to exclude rows 2 and 4 (F2 and F4,
respectively), then you would change the array constant to {2,4}. Does
this help?

In article <E8F64F2B-33A9-4945-8BD6-29CB159D2BD4@microsoft.com>,
"Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:

> Ok! Took care of that problem but it is not excluding f39,f40,f41,f46,f47.
> I see where the values of {5,6,7,12,13} should take care of this. Maybe we
> got off course somewhere. Here is a simple version of what I want to do
>
> f
> 1 10
> 2 12
> 3 0
> 4 0
> 5 7
>
> OK, I want the average of f1,f2,f3,and f5 but do not include the zero value
> located in f3 in the average calculation. Also, do not include f4 in the
> calc. This is overly simplified but if you get this to work, I could get the
> remainder.
>
> Thanks again.

9. ## Re: Average, Excluding Zeros, Non-Consecutive Range

Domenic,

Welllll, the formulae I have had in my workbook are correct. There must be
a setting or something on the specific worksheet that I am working in for
this not to be working.

Just to check that I had my formula correct, I input the exact scenario I
gave you into a new worksheet in my workbook and as expected it worked fine.

Then, I put the exact information on the worksheet I am needing the formulae
on and it shot craps again. All is working excpet it is not excluding the
cells we are indicating (i.e. f4). Don't have a clue as to why it is
occurring on this particular worksheet?!?!?

"Domenic" wrote:

> Okay, in this case, you would use the following formula...
>
> =IF(INDEX(FREQUENCY((F1:F3,F5),0),2),AVERAGE(IF((1-ISNUMBER(MATCH(ROW(F1:
> F5),{4},0)))*(F1:F5>0),F1:F5)),"")
>
> ....confirmed with CONTROL+SHIFT+ENTER. Note that the number in the
> array constant --- {4} --- determines which row to exclude, in this case
> row 4 or F4.
>
> If, for example, you wanted to exclude rows 2 and 4 (F2 and F4,
> respectively), then you would change the array constant to {2,4}. Does
> this help?
>
> In article <E8F64F2B-33A9-4945-8BD6-29CB159D2BD4@microsoft.com>,
> "Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:
>
> > Ok! Took care of that problem but it is not excluding f39,f40,f41,f46,f47.
> > I see where the values of {5,6,7,12,13} should take care of this. Maybe we
> > got off course somewhere. Here is a simple version of what I want to do
> >
> > f
> > 1 10
> > 2 12
> > 3 0
> > 4 0
> > 5 7
> >
> > OK, I want the average of f1,f2,f3,and f5 but do not include the zero value
> > located in f3 in the average calculation. Also, do not include f4 in the
> > calc. This is overly simplified but if you get this to work, I could get the
> > remainder.
> >
> > Thanks again.

>

10. ## Re: Average, Excluding Zeros, Non-Consecutive Range

If you'd like, you can send me your file and I'll look at it right away
to see if I can pinpoint problem. If you're interested, you can email
me at domenic22@sympatico.ca (remove any personal/confidential
information). Otherwise, I'll give it more thought and get back to you
if and when I have any other suggestions.

In article <433C316F-C8C8-472C-85A5-60BCD1A2D159@microsoft.com>,
"Coal Miner" <CoalMiner@discussions.microsoft.com> wrote:

> Domenic,
>
> Welllll, the formulae I have had in my workbook are correct. There must be
> a setting or something on the specific worksheet that I am working in for
> this not to be working.
>
> Just to check that I had my formula correct, I input the exact scenario I
> gave you into a new worksheet in my workbook and as expected it worked fine.
>
> Then, I put the exact information on the worksheet I am needing the formulae
> on and it shot craps again. All is working excpet it is not excluding the
> cells we are indicating (i.e. f4). Don't have a clue as to why it is
> occurring on this particular worksheet?!?!?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1