I wrote a formula:
=(Z8−AA8)÷Z8
but when I drag copied it down the column after a few rows it changed to a column name format
('Opening Price >>' 1.2301−'Closing Price >>' 1.2301)÷'Opening Price >>' 1.2301
and then after 5 rows reverts back to the original number format in the top formula?
Is there a way to make it adopt the number instead of name reference format?
Cheers
MacBook Pro 15″, macOS 13.0
Numbers->Preferences
Uncheck "use header names as labels"
With that option on (checked), it will replace cell/range references with their header names, if they are one-of-a-kind (sort of). But it can get weird if you start making changes. Say column B has the word "Data" in the header row and Column A has numbers 2, 3, 4, 3 in cells A2:A5. The references to B2:B5 will be 'Data 2', 'Data 3', 'Data 4', and B5 (because there cannot be two Data 3's). If you then put a 6 in cell A3, B3 does not change from 'Data 3' to 'Data 6', it becomes B3, and B5 does not change to 'Data 3', it stays B5. So you end up with a mix of labels and regular references that make debugging much harder.
Numbers->Preferences
Uncheck "use header names as labels"
With that option on (checked), it will replace cell/range references with their header names, if they are one-of-a-kind (sort of). But it can get weird if you start making changes. Say column B has the word "Data" in the header row and Column A has numbers 2, 3, 4, 3 in cells A2:A5. The references to B2:B5 will be 'Data 2', 'Data 3', 'Data 4', and B5 (because there cannot be two Data 3's). If you then put a 6 in cell A3, B3 does not change from 'Data 3' to 'Data 6', it becomes B3, and B5 does not change to 'Data 3', it stays B5. So you end up with a mix of labels and regular references that make debugging much harder.
Thanks, right now that makes sense. I looked at the preferences and I was surprised to see mine is ticked to "use header names as labels" and with the large amount of editing and deleting and recreating or rows in this table I'm surprised I didn't run into this issue earlier! What would have been handy is to have both the names and the cell ranges in that name format eg,
'Opening Price A2:A5>>' 1.2301−'Closing Price B2:B5 >>'
but would imagine that isn't possible without still causing the same issue to occur, perhaps even more frequently.
Cell Reference Ranges change from Numbers A1:A10 to Column Name