Forum Discussion
Solved
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...
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:
And there are many more articles to be found on line that deal with this type of references.
LenB Did you add some Conditional Format rule yourself? If so, what does it say? Or perhaps it's built into the template.
Yes, I added the conditional format...
Formula: =AVERAGE($D15:H15)
Applies to: $I$15:$O$72
And the format changes the cell color to orange
LenB Try a CF rule like this:
=E15=AVERAGE($D15:E15)applied to $E$15:$O$72
See the example attached and you should be able to apply it to your template as well. In any case, it's important that you set a condition that a cell equals an average
Thank you, I finally got that to work.
First attempt when I went to do the =average formula and clicked on the D15 cell, it came up =AVERAGE([@Jan],) instead of D15. There must be some embedded formula in their template. Column E is [@Feb], F is [@Mar] and so on. It won't allow me to freeze the [@Jan] cell with a $ so I'm copying across and adding what I need to each cell to make it right, then I'll copy everything down.
It seems to be working properly so, thank you again.
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:
And there are many more articles to be found on line that deal with this type of references.
