You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

function error

In Numbers, with the function AVERAGEIF, I get the error "numbers can't be divided by zero" even though all figures are two or three-digits, no divisions. There are blank cells, but that should be ignored by the condition ">0" as in AVERAGEIF B2:B25, ">0"

In the range, I'm not including cells containing either text or functions.

What am I overlooking?

MacBook Pro 15″, macOS 11.3

Posted on Sep 26, 2021 7:13 AM

Reply
9 replies

Sep 26, 2021 12:58 PM in response to SGIII

Row 25 is on the same row as the function. I've eliminated and added back the comma, all combinations of open and close parentheses, and it still comes up with the same error. The same AVERAGEIF function works in two other rows above the yellow block. Even if I try copy-pasting those to the row in question, guess what? Same error. The other rows that work also have some zero values contained.


Sep 26, 2021 10:29 PM in response to DougRoth

Hi Doug,


Error messages tend to be short and succinct. The first time you see this one in this circumstance it's a bit puzzling at first, but it does tell you there's division by zero going on somewhere in your formula. In AVERAGE, which calculates the arithmetic mean, the only value that is going to cause a division by zero error is the count of the numeric values contributing to the average. If that count is zero, you're looking for a reason that none of the cells included in the average is found to have contained a number.


Puzzling the first time, but less so if it happens again.



Functions in Numbers, with few exceptions, interpret text values, including text values composed of number characters, as having a numeric value of zero. The first notice of that often comes from the 'wrong' results of a formula using the SUM format.


Spotting text masquerading as number values is pretty simple if you haven't manually set the text alignment of text in the cells. Numbers aligns text values to the left edge of the cell, and aligns numeric values (and quasi-numeric values such as Date and Time) to the right edge of the cell. Note the examples to the left of the error triangle in your example.


Regards,

Barry

function error

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.