Previous 1 2 20 Replies Latest reply: Feb 12, 2014 4:17 AM by hughmacfarlane
Level 1 (0 points)

Hi everyone,

I have a five sheet spreadsheet that uses data from four sheets to calculate numeric averages in the fifth. The problem is that there is not always data in all four sheets which produces a division by zero error in the averages sheet. Is there a function or setting in Numbers 2.3 (not yet ready to upgrade to Numbers 3) that will allow these division by zero cells to be replaced by blank cells.

I have tried using statements of the form =AVERAGE(IFERROR(Sheet 1::Table 1:: B4,""),IFERROR(Sheet 2:: Table 1:: B4,""),Sheet 3:: Table 1:: B4,""), Sheet 4:: Table 1:: B4,"")). however instead of a division by zero error this produces an error telling me "the function AVERAGE expects that a number, date or duration but found "." ". It looks as though I will have to make about 400 changes to my sheet as these errors are in this and approximately 100 other cells!

I am hoping that there is some way if possible of correcting these errors without having to manually edit each cell individually.

Thanks,

Hugh

OS X Mavericks (10.9.1)
• Level 6 (11,400 points)

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

• Level 1 (0 points)

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.

• Level 7 (29,950 points)

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

• Level 6 (11,400 points)

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

• Level 4 (3,320 points)

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

Quinn

• Level 1 (0 points)

Jerrold,

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.

• Level 4 (3,320 points)

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

• Level 7 (29,950 points)

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

• Level 1 (0 points)

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

• Level 6 (16,140 points)

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)

• Level 7 (29,950 points)

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

• Level 6 (11,400 points)

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.

• Level 1 (0 points)

Wayne,

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

• Level 6 (16,140 points)

Hugh,

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

Previous 1 2