AVERAGE function breaking conditional formatting - any work-around?

Hi!

I'm new to this forum and thought it would be worth a shot to learn from here !

I'm trying to conditionally format the fill of specific ranges of dataset cells in colors corrosponding to a range: 1-4 Red, 5-7 Yellow and 8-10 Green.

Then I would like to find the average (=AVERAGE()), round it up to no decimals and have this value formatted to Red, Yellow or Green too.

I'm not experienced enough to figure out how to work around the =AVERAGE() function in conditional formatting, as the conditional formatting breaks when a number rounds up from one color into another. E.g. 4.8 (RED) to 5 (YELLOW).

The white fill cells are where the average rounding error occurs and breaks the formatting.

The generel idea is to have serveral dataset averages go into an category score overview, and then generate a final average score for the ID.

I've attached an image as reference. It is a dummy for the sets im working in, but the logic is the same.

Hopefully, one from the forum could help solve this challenge of mine ..

Thank you, and all the best,

Lforlasse

Not sure if I understand correctly, but try =ROUNDDOWN(AVERAGE(range),0)

This formula conditionally formats a red background::

=D6<5

Yellow:

=AND(D6>=5,D6<8)

Blue:

D6>=8

Hi AliGW,

Thank you for clarifying. I'll look into updating my post with an example workbook and follow that set of yellow box principles ..!

All the best,
Lforlasse

Hi again,

Here is the sample workbook, for those of you trying to help me sort this conditional formatting out.

Thank you all for the help so far - I'll look into the pointes I've received so far, and get back to you when it works as intended.

Sought solution:
- Formatting the fill of cells with values (numbers) in specific colors corrosponding to the range of the number: 1-4 red, 5-7 yellow and 8-10 green.
- Averaging the datasets into avarage values. Keeping the fill formatting.
- rounding the datasets average values up to have no decimals. Keeping the formatting.
- gather these values in an overview and compiling a new average (and final score). Keeping the formatting.
- show the final score for the ID's formatted in red, yellow and green.

All the best,
Lasse

Thank you for replying. This seemed to work!

I'm a total beginner, learning my way into the Excel world - I appreciate you taking your time to help me.

Thank you for your help in post #3. This worked to solve my issue of rounding the numbers correctly with the formatting.