Formula in a cell that is within a range referenced by the formula

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 02/20/20 in Formulas and Functions

@Genevieve P & @Andrée Starå

I was finally able to do some testing based on THIS THREAD and the recent discovery that you can reference a range in a formula that covers the cell that the formula itself is in.

I got some interesting results when I used JOIN and JOIN/COLLECT. It treats the cell as blank, so if you just use JOIN between f and g below with a comma delimiter, you end up with

"a, b, c, d, e, f, , g, h, i, "

I also found that it HAS to be whole column references to work.

[Column1]:[Column1] --> Works

[Column1]:[Column15] --> Works

[Column1]1:[Column1]12 --> Does NOT work

[Column1]@row:[Column15]@row --> Does NOT work

And of course specifically referencing that cell does not work either.

HERE is the sheet I used for testing that shows what all I have tried thus far. I personally found the JOIN function tests to be pretty interesting.

Typing up this discussion got me to thinking, so I threw in another quick test that had more interesting results. The formula DOES LOOK AT ITSELF. It looks at itself as a blank. Typing up the JOIN test results hinted at it, so I did another COUNTIF test to count all cells in that column that were blank, and it included itself in that count.

Of course that prompted more testing such as MATCH where it was pulled as the count for the first blank cell in the range.

Overall I think it's pretty interesting how it is working...


Original "note" that spurned this testing:

"NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula."

Link where note was found:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!