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.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I've got this large spreadsheet I've been working on for months, no problems. Opened it up today to edit and every time I make a change all the cells that contain a formula zero out.
Doesn't matter where I perform an edit, even if it doesn't affect a formula, zeros appear in all cells. Looks like a bug, any thoughts?
Hi iamtrist,
it's been working perfectly all year.
Did it work perfectly in the year 2019 and is now broken in 2020?
Perhaps in the Transactions table, the Month (Column E) is actually a Date & Time format displaying only the Month, but containing a non-displayed year (2019).
Similarly, in the Categories by Month table. Are the months Text format or are they a display of the month copied from a Date & Time cell?
Best to use Text format in both cases. Then the formulas are independent of the year.
Just a thought.
Regards,
Ian.
Hi iamtrist,
it's been working perfectly all year.
Did it work perfectly in the year 2019 and is now broken in 2020?
Perhaps in the Transactions table, the Month (Column E) is actually a Date & Time format displaying only the Month, but containing a non-displayed year (2019).
Similarly, in the Categories by Month table. Are the months Text format or are they a display of the month copied from a Date & Time cell?
Best to use Text format in both cases. Then the formulas are independent of the year.
Just a thought.
Regards,
Ian.
Hi SGill,
See below for equation with context.
Transaction table holding source data to be filtered
One of the filtering tables using `SUMIFS`. If I edit the transaction table this table will display zeros in all cells with equations. If I undo my edit, this table still shows zeros. Same applies throughout the spreadsheet. If I edit the equation - zeros.
Where the equation references $A, I'd have thought this should reference a cell. Bear in mind this spreadsheet has worked perfectly all year. It's only during the last edit that I'm getting unexpected results.
More Info:
I have a large table of transaction data (for a year). I have a number of other table using `SUMIFS` to group the transaction data.
If I add more rows to the transaction table, all the other tables zero. If I edit a `SUMIFs` equation on the other tables, the edited cell displays zero. Using `undo` doesn't get me back to the correct result, I still get zero in that cell.
"it does seem as if in the equation on row 14 you should have $A14. Have you tried that?"
Yes, even if I correct the equation, all the `SUMIFS` cells display zero. Really odd, as I said it's been working perfectly all year. Also if I make a change to the document that results in all the `SUMIFS` cells displaying zero, attempting an undo doesn't return to previous state
Hi Yellowbox,
It was in fact the header cells in the Categories by Month table being in a date format. I'm guessing Numbers decided for me and I forgot to check. Your answer led me to the solution, thank you.
Thanks for your reply Wayne. Yes I tried restarting.
The cells affected use `SUMIFS`. I've noticed the `condition` property seems to have changed to a range, it was originally a cell reference.
Have you tried restarting your computer?
If I change the equation to reference just the cell it still displays zero. Undo back to original/saved equation still show zero
Can you post a screenshot that shows not just the formula but also the columns and cells to which the formula refers? Context is important.
SG
Still hard to picture what's going on, but it does seem as if in the equation on row 14 you should have $A14. Have you tried that?
SG
Hi iamtrist,
Happy to help (just a lucky guess). Thanks for your reply and the points!
Keep posting in Numbers for Mac.
Regards,
Ian.
Large Spreadsheet - Editing causes all calculated cells to Zero
