I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")


Please help
Drock-13

Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff

"smeldawg" wrote:

I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")


Please help
Drock-13

Hi Biff,

I see you've gone back to using your nick-name!

Pete

On Apr 15, 8:24*pm, Biff wrote:
Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff



"smeldawg" wrote:
I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")

Please help
Drock-13- Hide quoted text -

- Show quoted text -

For some reason I can't get access using OE. Keep getting "Unable to connect
to the server" errors so I'm currently accessing through the web interface.
That's my sign-in for the web interface. I don't know how to change it! <g

--
Biff


"Pete_UK" wrote:

Hi Biff,

I see you've gone back to using your nick-name!

Pete

On Apr 15, 8:24 pm, Biff wrote:
Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff



"smeldawg" wrote:
I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")

Please help
Drock-13- Hide quoted text -

- Show quoted text -

The first one did the trick. Thank you for the help.
--
Drock-13


"Pete_UK" wrote:

Hi Biff,

I see you've gone back to using your nick-name!

Pete

On Apr 15, 8:24 pm, Biff wrote:
Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff



"smeldawg" wrote:
I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")

Please help
Drock-13- Hide quoted text -

- Show quoted text -

I think I found where to change the display name. Let's see if it shows up
differently this time.

--
Biff
Microsoft Excel MVP


"Biff" wrote:

For some reason I can't get access using OE. Keep getting "Unable to connect
to the server" errors so I'm currently accessing through the web interface.
That's my sign-in for the web interface. I don't know how to change it! <g

--
Biff


"Pete_UK" wrote:

Hi Biff,

I see you've gone back to using your nick-name!

Pete

On Apr 15, 8:24 pm, Biff wrote:
Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff



"smeldawg" wrote:
I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")

Please help
Drock-13- Hide quoted text -

- Show quoted text -

You're welcome. Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"smeldawg" wrote:

The first one did the trick. Thank you for the help.
--
Drock-13


"Pete_UK" wrote:

Hi Biff,

I see you've gone back to using your nick-name!

Pete

On Apr 15, 8:24 pm, Biff wrote:
Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff



"smeldawg" wrote:
I have a fomula that is trying to average the totals of individual sections
of averages. I have one section where there is not an average to add to the
formula. I would like to write a formula that would average all of the totals
even if there is a null value so I don't have to rewrite the formula if when
the section gains a value. Currently the null value is represented by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")

Please help
Drock-13- Hide quoted text -

- Show quoted text -

mark

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You're welcome. Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"smeldawg" wrote:

The first one did the trick. Thank you for the help.
--
Drock-13


"Pete_UK" wrote:

Hi Biff,

I see you've gone back to using your nick-name!

Pete

On Apr 15, 8:24 pm, Biff wrote:
Try one of these array formulas** :

If you will *never* insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58),6)=4)*(ISNUMBER(I10: I58)),I10:I58))

If you might insert new rows above the range:

=AVERAGE(IF((MOD(ROW(I10:I58)-ROW(I10),6)=0)*(ISNUMBER(I10:I58)),I10:I58))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff



"smeldawg" wrote:
I have a fomula that is trying to average the totals of individual
sections
of averages. I have one section where there is not an average to
add to the
formula. I would like to write a formula that would average all of
the totals
even if there is a null value so I don't have to rewrite the
formula if when
the section gains a value. Currently the null value is represented
by
#DIV/0!. Here is what I have so far;
=AVERAGE(I10,I16,I22,I28,I34,I40,I46,I52,I58)
This is what I have tried
-- =AVERAGEIF(I10,I16,I22,I28,I34,I40,I46,I52,I58,"< #DIV/0!")

Please help
Drock-13- Hide quoted text -

- Show quoted text -