I have a temperature chart that I have set up for this year. When I type in the high temperature for the day, the cell turns to a designated color in my chart (so, for example, when it is between 81-85, the cell will be yellow, when 86-90 it will be pink, and so on).



Now that I am in December, I went to a section below all the temperatures/dates and created a list of cells for the months, and then a formula to figure out the average temperature for each month and for the year.

However, when I apply the same conditional formatting to those cells, they do not all turn to the assigned color - some of them are off. I can only guess that it is because there is a formula applied to that cell instead of just typing in the number.

How do I get the averages (still in column B below line 368) to use the same formatting as the cells above? For a temporary fix, I had to type in the averages manually into cell C next to them.

Hope that makes sense. I am using the temperatures for a crochet project.

Attached Files

2016TempChart.xlsx (56.8 KB, 12 views)

Here is one way...(and there are surely others)

Beginning in A371 and down, put in the actual date (i.e., 1/1/16, 2/1/16, 3/1/16 etc)

Then in B371 copied down

=SUMPRODUCT(--(MONTH($A$3:$A$368)=MONTH(A371)),$B$3:$B$368)/SUMPRODUCT(--(MONTH($A$3:$A$368)=MONTH(A371)))

In A371, place a custom format to get January, February etc

Custom Format

• Right click cell (or Ctrl + 1)
• Format Cells
• Number
• Custom
• Type: mmmm

Replace your formulas like =AVERAGE(B277:B307) with =ROUND(AVERAGE(B277:B307),0).
Your CF will no work as required

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you for your reply.

I followed your directions (I think) for the months. Currently, the year cell is still in the =AVG(B3:B368 format. I wasn't sure how to apply the formula for that.

But I still have the same problem. In the updated version attached, I did not apply the conditional formatting. But what I had tried, and still didn't work was....

• copied one of the cells from above (right click, copy)
• right clicked in one of the cells below (eg, B371)
• clicked on paste special, selected paste special again, clicked on the radio button for "format."

When I do that, it changes SOME of the temperatures to the correct color, and others not.

Attached Files

2016TempChart.xlsx (54.3 KB, 9 views)

Okay...this one is working. Thanks!!