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, 10 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

__________________
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, 7 views)

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