Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gharden
 
Posts: n/a
Default 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
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

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:A1 0))

(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
  #4   Report Post  
gharden
 
Posts: n/a
Default

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

  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Worksheet references another tab in formula Bonita Excel Worksheet Functions 2 March 14th 05 10:44 PM
IF & VLOOKUP FORMULA taxmom Excel Worksheet Functions 3 March 2nd 05 04:35 PM
How can I paste a formula that references a chart of data brantty Excel Worksheet Functions 1 February 25th 05 08:21 PM
I want a formula to ignore text eg 5mts * 5 ignoring the mts any . Malshenton Excel Discussion (Misc queries) 1 January 14th 05 08:59 PM
Indirect references in a linked formula Markshnier Excel Worksheet Functions 1 November 15th 04 03:49 AM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"