Function to replace division by zero error cells with blank cells when calculating a numeric average

To 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),"<>")

Hugh,

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

The 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

Shouldn't the IFERROR function wrap the AVERAGE function rather than the other way around? It was the one throwing the error.

Quinn

Shouldn'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

Hugh,

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

Hugh,

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)

Hugh,

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

Hugh,

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.

Hugh,

I also suggest you consider a redesign. It is starting to sound unmanagable.