Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet references another tab in formula | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
How can I paste a formula that references a chart of data | Excel Worksheet Functions | |||
I want a formula to ignore text eg 5mts * 5 ignoring the mts any . | Excel Discussion (Misc queries) | |||
Indirect references in a linked formula | Excel Worksheet Functions |