Numbers can't be divided by zero using average function

Question

nomadicapple Author

Level 1

8 points

Numbers can't be divided by zero using average function

Simple problem.

I'm a visual guy so I'll show the screenshot first.

The goal is to get the average of the numbers entered in column A & B into column C.

As you can tell from the screenshot,

row 1 with numbers in column A & B works fine,
row 2 with numbers in column A only technically works fine,
row 3 with no numbers in column A & B generate an error.

This is done using the simple =AVERAGE(A1:B2) function.

What I want is to be able to write the conditional function where the result leaves the answer blank.

I do not quite understand how to write the conditional function. I get the concept, but not how to write it. I have been able to successfully do a conditional based on a single cell, but not on an average for multiple cells.

If I understand the logic right, I'm trying to create a formula that says if blank, return blank, if not blank, return average.

I see there's an IF function, IFERROR, ISERROR, and a AVERAGEIF function. I tried playing with all these functions but I can't wrap my head around it.

All I want is to be able to write the function that makes this possible. I'd appreciate the simple answer to a simple solution. I'm sure you get it. I just don't. Thanks.

MacBook Pro with Retina display, iOS 10.3.3

Posted on Aug 26, 2017 7:42 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,146 points

Aug 26, 2017 1:15 PM in response to nomadicapple

To use IFERROR you simply "wrap" your working function in it.

=IFERROR(<the original working function>,<what you want to show if there is an error>)

So

=IFERROR(AVERAGE(A1:B2),"")

should do what you want.

The "" simply means put nothing in that cell (i.e., leave it blank).

In your region you may need to use ; in the formula instead of , .

SG

Reply

Answer 2

Barry

Level 9

59,957 points

Aug 28, 2017 4:35 AM in response to nomadicapple

Hi na,

Three comments:

Math: "Numbers can't be divided by zero using average functions" is correct, but understates the case.

Speaking formally, "Division by zero is undefined," and you can't do it in any mathematical situation, not just "using average functions."

IFERROR: This is a very useful function, but can be a dangerous one if you use it without first knowing what error you expect to trap. IFERROR traps all errors, not just the ones you expect. That can make trouble-shooting a formula more difficult.

"" does not "put nothing" in a cell. It inserts a null string (also known as the empty string), a text value of zero length. The cell will appear 'empty' or 'blank', but COUNTA() will count it as a text value, and (IIRC) the arithmetic operators will choke on it.

SG's solution will solve your issue, but you do need to be aware of the limitations of IFERROR in solving similar issues in future.

Regards,

Barry

Reply

Answer 3

SGIII

Level 8

36,146 points

Aug 27, 2017 6:12 AM in response to nomadicapple

Glad it helped and thanks for the green tick!

I agree with Barry's points. But normally you won't need to worry about them. The "nothing" is not really nothing, of course. After all, you still have a formula in the cell. That formula is displaying "something" technically called a "null string" that looks like "nothing," but gets counted as something if you use COUNTA (all cells containing formulas are counted by COUNTA). You'll also get an error if you try to directly add, subtract, multiply, or divide that cell by a number or another cell containing a number. However, SUM, PRODUCT, and perhaps some other functions treat the cell just as you would expect.

I wouldn't view IFERROR as dangerous. It's very common in spreadsheets and highly useful. You get your formula working as you expect, then hide warning triangles that you would expect to see by "wrapping" the formula in IFERROR.

SG

Reply

Answer 4

nomadicapple Author

Level 1

8 points

Aug 28, 2017 4:56 AM in response to Barry

Barry,

Thank you for the additional information. The COUNTA regarding it as a text value is something I didn't know. I suppose I may have used the term "blank" somewhat loosely here. What started my search for the solution in my case was a cell that basically compiled the average of all the cells in column C but was unable to do so because of the original problem in the first place.

As soon as I was able to enter the correct formula which fixed the original problem, the second problem resolved itself.

If I am understanding the null string concept correctly, which is to create a text value of zero length, it allowed the second average function to determine whether to count the "blank" cells or not because there was no data of any length (ie. 0.1, 0.001, 1 etc..)?

I may be overthinking this question. In any case, I do appreciate the extra info.

Reply

Answer 5

nomadicapple Author

Level 1

8 points

Aug 28, 2017 5:03 AM in response to SGIII

Again, thank you for your help on this. Your solution helped solved a second problem which was triggered by the original problem. While I'm not 100% clear on the "null influence" regarding other functions yet, I suspect I will as I play around with it some more as explained by both you and Barry.

It's a spreadsheet that's an ongoing spreadsheet tracking data over 90 days but needs to be input on a daily basis. Not only is it's a bit annoying to see the warning triangle but also to be asked why are they there. Up till now, basically, I had to copy/paste the average formula one cell at a time per day which defeated the main purpose of having just two cells to enter data. Now everyone using my spreadsheet is happy.

Many thanks.

Reply

Answer 6

nomadicapple Author

Level 1

8 points

Aug 26, 2017 1:16 PM in response to SGIII

Perfect.

My attempts were nowhere near that simple solution. Much appreciated. Thank you.

Reply