Forum Discussion
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 month thru the year and changes the font color if it's an averaged number. As I continue each month, the average changes to include the most recent month's amount and the rest of the month's number changes to the new average.
For instance...
Expense Jan Feb March Apr...
Electricity $100 $50 $75 $75
Then when I put in the actual March value the average from April thru Dec will be the new average.
Expense Jan Feb March Apr May...
Electricity $100 $50 $150 $100 $100
My problem is, when I input the actual value for the month, the font color stays red even if I try to change the font color.
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
