
Badunit Feb 7, 2014 5:43 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneTo be more clear on your problem, you want to average cells that may contain errors?
This seems to work:
=AVERAGEIF(UNION.RANGES(0,Sheet 1::Table 1::B4,Sheet 2::Table 1::B4,Sheet 3::Table 1::B4,Sheet 4::Table 1::B4),"<>")

hughmacfarlane Feb 7, 2014 6:45 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to BadunitThanks Badunit,
To clarify, my problem is that one (or more) of the sheets may contain empty cells. This then creates a division by zero error because each empty cell is divided by the sum of the empty cells (=0). This error then appears in both the original sheet and the averages sheet. I am trying to tackle the problem from the averages sheet as I would have four times as much work to error protect the other sheets.
I tried replacing my =AVERAGE(IFERROR... statement displaying the "AVERAGE expects a number... " message in my Averages sheet with the function you gave me:
=AVERAGEIF(UNION.RANGES(0,Sheet 1::Table 1::B4,Sheet 2::Table 1::B4,Sheet 3::Table 1::B4,Sheet 4::Table 1::B4),"<>")
but it doesn't seem to work as I am now getting a " "UNION.RANGES" isn't a valid function name" " error message.

Jerrold Green1 Feb 7, 2014 6:59 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneHugh,
I think Badunit may have missed that you are still on version 2.3, which doesn't have that function.
I confess that I'm not as good at visualizing your situation as I probably should be, but if you could post a screen shot or two it might be helpful. There are a ways to avoid creating errors by checking for content before executing the function, rather than letting the error happen and then trapping it.
Jerry

Badunit Feb 7, 2014 7:14 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneThe error message from your formula implies that one of the IFERROR functions is finding an error and replacing it with a null string, as you asked it to do. If the problem was that all the cells being averaged were blank or zero, you would get a divide by zero error instead. You could put an IFERROR around the whole formula to trap that error.
UNION.RANGES is a valid function in Numbers 3 but not prior versions. I somehow totally skipped over the part where you said 2.3
I originally wrote some more here but I removed it because I am confused. You said the cells to be averaged may be empty. But you also seem to be saying the cells might have errors in them. An error is not the same as empty. A correct answer requires a full description of the problem

t quinn Feb 7, 2014 7:46 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneShouldn't the IFERROR function wrap the AVERAGE function rather than the other way around? It was the one throwing the error.
Quinn

hughmacfarlane Feb 7, 2014 8:17 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to Jerrold Green1Jerrold,
Thanks for getting back to me. Below is a screenshot of one of the tables that I have in each of the subsidiary sheets that contribute to my Averages main sheet.
The highlighted cell is one of those referenced in the Averages sheet when a data set is missing and which I am trying to get Numbers to treat as if it were blank so and thus calculate the average of the remaining sets. I used IFERROR statements in the Averages sheet not realising it was incompatible with the AVERAGE function.

t quinn Feb 7, 2014 8:44 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to t quinnShouldn't the IFERROR function wrap the AVERAGE function rather than the other way around? It was the one throwing the error.
Wish that edit button would stick around.
Quinn

Jerrold Green1 Feb 7, 2014 8:53 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneHugh,
If you would like to, you can send me a copy of the document, or a representative portion of it, and I'll post the parts that I think would help me and the others here study your layout for a solution.
Jerry

hughmacfarlane Feb 7, 2014 8:54 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to BadunitBadunit,
The null string was an unsuccessful attempt to trap the division by zero error. IFERROR has to go inside AVERAGE otherwise I would get blanks in the AVERAGES mainsheet.
I have managed to google something in an EXCEL forum that seems to work but will take ages to replace the 400 null strings with references to an empty cell. I am hoping that someone can come up with something that won't take as long,
Hugh

Wayne Contello Feb 7, 2014 9:15 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneHugh,
Try this method. I made a small example to demonstrate:
columns C thru E are averaged into column B of the same row, then column B is averaged into cell A1.
B2=IFERROR(AVERAGE(C2:E2), "")
select B2, copy
select column B, then hold the command key and click B1 (to unselect the header cell), paste
A2=AVERAGE(B1:B11)

Jerrold Green1 Feb 7, 2014 10:48 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneHugh,
You may want to consider an intermediate sheet to handle the errors and then pass the result on to the main sheet.
In the following mockup, I've created a small interposing table the consists of a list of sources in column A and the result from each table. In the example, I perform some operation on those intermediate results, to be passed on to the main table. This may not be a good example, and since I don't know the overall goal of your project it's just a surrogate. (In real life we probably wouldn't be interested in finding the average of averages.)
The expression in Summary::B2 is:
=IFERROR(INDIRECT(A&"::B4"), "SourceError")
Regards,
Jerry

Badunit Feb 7, 2014 11:10 AM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneHugh,
You have 400 unique formulas, not a few that can be copy/pasted or filled to the other cells? I do not know why your spreadsheet is that way but maybe you should consider redesigning the structure of it so you can copy/paste formulas or maybe you can develop a formula that can be copy/pasted to other cells. Typical spreadsheets have few unique formulas. There may be thousands of formulas in total but only a few unique ones. The rest of the cells are populated by copy/paste or fill.

hughmacfarlane Feb 7, 2014 2:02 PM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to Wayne ContelloWayne,
I will keep this in mind as it will be helpful in future but I can't see how I can integrate it right now with what I have at present to calculate the average of four cells located on other sheets when one or more of those cells contains a division by zero. I started with multiple cells of the form " =AVERAGE(Sheet 1::Table1::D3,Sheet 2::Table 1::D3,Sheet 3::Table 1::D3,Sheet 4::Table 1::D3) but soon discovered that if the data for one of the sheets was missing, the Averages sheet went missing too. So I tried enclosing each cell in IFERROR(*,"") which doesn't work. I have discovered that it will work if I replace the null string with a reference to an empty cell which is a lot of work as I removed all my absolute references in order to be able to move the tables around to accomodate modifications to the spreadsheet.
Thanks again,
Hugh

Wayne Contello Feb 7, 2014 2:10 PM
Re: Function to replace division by zero error cells with blank cells when calculating a numeric average in response to hughmacfarlaneHugh,
I also suggest you consider a redesign. It is starting to sound unmanagable.