Ignore error msgs in formula references

I have an Average formula that refers to cells linked to another spreadsheet.
The linked spreadhsheet gets updated each month, so that for months with no
data yet, a #Div/0! error appears. I need to average a group of cells that
display updated numbers as well as this error msg & I can't remove any of
them from the calculation.

I have found that you can use the IF function to display a string value in a
cell w/an error message, but only if you can control the formula causing the
Div/0! error...I can't, as that is the linked spreadsheet

this was the topic in the help files I found:

"Prevent the error value from displaying, using the IF worksheet function.
For example, if the formula that creates the error is =A5/B5, use
=IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text
string."

There is no way I know of to make Excel disregard this error msg in the
linking process...I can't get the Average function to work because of the
error message appearing in the group of cells I need to average

any ideas?

thanks, gina

How about something like:

=AVERAGE(IF(ISNUMBER(sheet1!A1:A10),sheet1!A1:A10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

gharden wrote:
>
> I have an Average formula that refers to cells linked to another spreadsheet.
> The linked spreadhsheet gets updated each month, so that for months with no
> data yet, a #Div/0! error appears. I need to average a group of cells that
> display updated numbers as well as this error msg & I can't remove any of
> them from the calculation.
>
> I have found that you can use the IF function to display a string value in a
> cell w/an error message, but only if you can control the formula causing the
> Div/0! error...I can't, as that is the linked spreadsheet
>
> this was the topic in the help files I found:
>
> "Prevent the error value from displaying, using the IF worksheet function.
> For example, if the formula that creates the error is =A5/B5, use
> =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text
> string."
>
> There is no way I know of to make Excel disregard this error msg in the
> linking process...I can't get the Average function to work because of the
> error message appearing in the group of cells I need to average
>
> any ideas?
>
> thanks, gina

--

Dave Peterson

In fact, if you have the possibility that there are no numbers in that range:

=IF(COUNT(Sheet1!A1:A10)=0,"",
AVERAGE(IF(ISNUMBER(Sheet1!A1:A10),Sheet1!A1:A10)))
(one line)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

---------
Maybe I misread the question.

=IF(COUNT(Sheet1!A1:A10)=0,"",AVERAGE(Sheet1!A1:A10))

(non-array entered)

I wasn't sure if the data had div/0's in it or the average formula returned a
div/0 error.


gharden wrote:
>
> I have an Average formula that refers to cells linked to another spreadsheet.
> The linked spreadhsheet gets updated each month, so that for months with no
> data yet, a #Div/0! error appears. I need to average a group of cells that
> display updated numbers as well as this error msg & I can't remove any of
> them from the calculation.
>
> I have found that you can use the IF function to display a string value in a
> cell w/an error message, but only if you can control the formula causing the
> Div/0! error...I can't, as that is the linked spreadsheet
>
> this was the topic in the help files I found:
>
> "Prevent the error value from displaying, using the IF worksheet function.
> For example, if the formula that creates the error is =A5/B5, use
> =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text
> string."
>
> There is no way I know of to make Excel disregard this error msg in the
> linking process...I can't get the Average function to work because of the
> error message appearing in the group of cells I need to average
>
> any ideas?
>
> thanks, gina

--

Dave Peterson

that was it, thanks! worked like a charm, just like these newsgroups, thanks
to folks like you...gina

"Dave Peterson" wrote:

> How about something like:
>
> =AVERAGE(IF(ISNUMBER(sheet1!A1:A10),sheet1!A1:A10))
>
> This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> correctly, excel will wrap curly brackets {} around your formula. (don't type
> them yourself.)
>
> gharden wrote:
> >
> > I have an Average formula that refers to cells linked to another spreadsheet.
> > The linked spreadhsheet gets updated each month, so that for months with no
> > data yet, a #Div/0! error appears. I need to average a group of cells that
> > display updated numbers as well as this error msg & I can't remove any of
> > them from the calculation.
> >
> > I have found that you can use the IF function to display a string value in a
> > cell w/an error message, but only if you can control the formula causing the
> > Div/0! error...I can't, as that is the linked spreadsheet
> >
> > this was the topic in the help files I found:
> >
> > "Prevent the error value from displaying, using the IF worksheet function.
> > For example, if the formula that creates the error is =A5/B5, use
> > =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text
> > string."
> >
> > There is no way I know of to make Excel disregard this error msg in the
> > linking process...I can't get the Average function to work because of the
> > error message appearing in the group of cells I need to average
> >
> > any ideas?
> >
> > thanks, gina
>
> --
>
> Dave Peterson
>

Glad one of the several worked ok for you <vbg>.

gharden wrote:
>
> that was it, thanks! worked like a charm, just like these newsgroups, thanks
> to folks like you...gina
>
> "Dave Peterson" wrote:
>
> > How about something like:
> >
> > =AVERAGE(IF(ISNUMBER(sheet1!A1:A10),sheet1!A1:A10))
> >
> > This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
> > correctly, excel will wrap curly brackets {} around your formula. (don't type
> > them yourself.)
> >
> > gharden wrote:
> > >
> > > I have an Average formula that refers to cells linked to another spreadsheet.
> > > The linked spreadhsheet gets updated each month, so that for months with no
> > > data yet, a #Div/0! error appears. I need to average a group of cells that
> > > display updated numbers as well as this error msg & I can't remove any of
> > > them from the calculation.
> > >
> > > I have found that you can use the IF function to display a string value in a
> > > cell w/an error message, but only if you can control the formula causing the
> > > Div/0! error...I can't, as that is the linked spreadsheet
> > >
> > > this was the topic in the help files I found:
> > >
> > > "Prevent the error value from displaying, using the IF worksheet function.
> > > For example, if the formula that creates the error is =A5/B5, use
> > > =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text
> > > string."
> > >
> > > There is no way I know of to make Excel disregard this error msg in the
> > > linking process...I can't get the Average function to work because of the
> > > error message appearing in the group of cells I need to average
> > >
> > > any ideas?
> > >
> > > thanks, gina
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson