conditional formatting

[LenB]

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.

[#2]

@LenB Did you add some Conditional Format rule yourself? If so, what does it say? Or perhaps it's built into the template.

[#3]

@Riny_van_Eekelen 

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

[#4]

@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

[#5]

@Riny_van_Eekelen 

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.

[#6 · Accepted solution]

@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-23... 

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

[#7]

I finally was able to get back to working on the spreadsheet. What you suggested works perfectly. Thank you so much and thanks for the link to the structured table references. I'm 63 and have a lot to learn, but it's always satisfying when all goes how it should.

[#8]

@LenB haha!  And I’m turning 62 in a few weeks. Never old enough to learn 😃

[#9]

That's right! This old dog can still learn some new tricks, but you can learn AND teach...Awesome!
Have a great weekend and happy birthday.