Feb 22 2024 05:01 PM
I've inherited a spreadsheet for work and noticing some weird use of formulas. One of them I'm hoping someone can explain. I have a column like below. The last cell basically is a sum of all the cells above, so you'd expect something like =SUM(B29:B49) and you get the answer of 1.
However they use =SUM(B29#).
Plus, assuming it's just another way to sum up the column I put a 1 in another cell and expect an answer of 2. But that's not what happens. Instead, I get an error #SPILL!
I though oh maybe the hash means start at B29 until you reach something, but I don't understand what the use of a hash does.
0 |
0 |
0 |
0 |
1 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
1 |
Feb 22 2024 05:56 PM
@lfk73 With MS365, the hash symbol is used to refer to a "spill range", which is the result of a dynamic array formula. Cell B29 in this example has a formula that returns multiple results (array), which extends to cell B49. Only the first cell in the array contains the formula, and only the first cell can be edited. Attempting to change the value of any other cell within the spill range will result in the #SPILL! error.
For more information, please see: https://www.xelplus.com/excel-hash-sign-formulas/
Feb 23 2024 12:38 AM
I've analyzed the information you've provided and the image, and here's my understanding of the formula =SUM(B29#) and its behavior:
Formula Breakdown:
Spill Operator Behavior:
Unexpected Behavior:
Alternative Approach:
Summary:
I hope this explanation clarifies the formula's behavior and helps you choose the best approach for your spreadsheet.