Cannot figure out why this Numbers formula isn’t working

Question

Level 2

173 points

Cannot figure out why this Numbers formula isn’t working

Below is a screenshot of a simple checkbook register style spreadsheet with an error message that has me stumped. All cells are formatted as numbers and the only cells containing formulas are the green ones in column C. They are all the same formula except for relative cell references. Here’s a sample formula from the first GREEN cell (C3).

IF(A3>0,C2+A3,IF(B3>0,C2−B3,C2))

I cant figure out why it gives me this particular error starting with C5 when the “+” operator isn’t even relevant in that cell while it doesn’t give that error in C4 — where it is relevant.

I’m hoping someone can clue me in. Thanks in advance!

Vera

PS: This is on Numbers for the iPad but I could only find the Numbers for the Mac community to post it in. I hope that isn’t an issue.

iPad Pro Wi-Fi, iOS 11.4, Numbers 4.1

Posted on Jun 19, 2018 1:48 PM

Reply

Answer 1

SGIII

Level 8

37,532 points

Jun 19, 2018 7:39 PM in response to VeraMilo

You can also use this formula, in C2, filled down:

=SUM(A$1:A2)−SUM(B$1:B2)

SUM doesn't care if you have visible or invisible non-number values in cells; it just ignores them. It also has the advantage of allowing you to add or delete rows without having to adjust the formula.

Note that this approach uses "self-expanding ranges." The $ "anchors" the top of the ranges to the first row. As you fill the formula down it becomes =SUM(A$1:A3)−SUM(B$1:B3) then =SUM(A$1:A4)−SUM(B$1:B4) , etc.

These formulas work the same in Numbers for iOS as they do in Numbers for the Mac.

SG

Reply

Answer 2

Yellowbox

Level 7

21,915 points

Jun 19, 2018 7:41 PM in response to VeraMilo

Hi Vera,

The formula works here. But when I type a space into A5 I get the same error. There must be an invisible character in your A5 or B5.

There is a simpler formula. No need to use IF.

=C2+A3−B3

Regards,

Ian.

Reply

Answer 3

VeraMilo Author

Level 2

173 points

Jun 19, 2018 7:39 PM in response to Yellowbox

Thank you, Yellowbox. There were no spaces in my blank fields nor the ones with numbers. I even set the format of all fields to number. But your example of a simpler formula was a useful lesson — my feeble attempts at programming efficiently leave much to be desired!

Reply

Answer 4

VeraMilo Author

Level 2

173 points

Jun 19, 2018 7:40 PM in response to SGIII

Thank you, SG! That solved it!

Reply

Cannot figure out why this Numbers formula isn’t working

Similar questions