Forum Discussion

LenB's avatar
LenB
Copper Contributor
Jun 27, 2024

conditional formatting

I'm using the Microsoft Simple Personal Cash Flow template spreadsheet.  As I fill in the amounts for each item each month, I have a formula that averages the amounts and extends that average to each...
  • Riny_van_Eekelen's avatar
    Jun 30, 2024

    LenB The syntax =FUNCTION([@Jan]) is actually a structured table reference and I would recommend you to learn about them. It means that 'the function' references the cell on the same row @ in the column called Jan

    That way, you don't need to worry about what cell you are referencing. But it needs a bit more if you want to make such a reference absolute like $B$15. To make a reference like [@Jan] absolute, you need to include the table name and repeat the column name, separated by a colon and with an extra pair of square brackets. It would look like Table1[@[[Jan]:[Jan]] . This reference can be copied to the right and it stays fixed on Jan. And to make a bit more awkward a formula that should first average Jan:Feb then Jan:Mar then Jan:Apr etc. when copied to the right, looks like this:

     

    =AVERAGE(Table1[@[Jan]:[Jan]]:[@Feb])

     

    The attached file contains an example. Read more about structured table references here:

    https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e 

    And there are many more articles to be found on line that deal with this type of references.

     

     

Resources