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
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.
I can't reproduce that here. Could you post a screenshot? (shift-command-4, select the area on the screen, release, then compose a new post here and use the 'mountains' icon below the compose window to insert the screenshot image from the Desktop.
SG
DougRoth wrote:
Same error.
What error? There is neither an error nor a result shown in your screenshot. So it is impossible to tell on this end what is going on.
Also, it seems as if you have an extra set of ( ) in your formula.
SG
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
I found it!!! The referenced cells were formatted as TEXT. DUH!
Thanks for putting your brain and time on this. I feel stupid.
The same error is "Numbers can't be divided by zero"
Can't tell much from that screenshot. Which is row 25? Also it seems as if you have an extra comma at the end of the formula and no closing parentheses.
SG
But still, you'd think the error message could be more specific and not lead users in some other direction.
function error
