# Conditional formatting if equal or greater than average in the range of cells

1. ## Conditional formatting if equal or greater than average in the range of cells

I need to create a conditional formatting for cell A1: apply conditional formatting for the cell A1 if numbers in the range of cells B1:B10 is equal or above average of the total number in the range B1:B10.  Register To Reply

2. ## Re: Conditional formatting if equal or greater than average in the range of cells

Okay, so you have numbers in B1:B10, they give an average. Let's say 3.14159. You have another number in Cell A1. You want a conditional formatting (i.e turn cell green) if A1 is greater than 3.14159? Is that what you are asking?  Register To Reply

3. ## Re: Conditional formatting if equal or greater than average in the range of cells

Yes, that's right. I also have another two conditional formattings for the cell A1. If it equal or greater than value in cell C1 than green, if less than average in the cell range B1:B10 than red, if equal or above average in the cell range as above in the initial post than yellow. Thanks!  Register To Reply

4. ## Re: Conditional formatting if equal or greater than average in the range of cells

selecting A1, new rule> use formula
=A1>= AVERAGE(\$B\$1:\$B\$10)
Does that work for you?  Register To Reply

5. ## Re: Conditional formatting if equal or greater than average in the range of cells

I will check that out. I am just curios if another formatting condition "if equal or greater than in cell C1" will confront with the above suggested? I will see... Thanks a lot! Highly appreciated!  Register To Reply

6. ## Re: Conditional formatting if equal or greater than average in the range of cells

Performance Tracker_2013.xlsMy explanation was poor... Let me re phrase it. Please also see file in attachment

I have targets set for certain indicators, for example 100% of personnel complete training by the year end. And I have monthly progress tracker where i input status in percentage under each month. The last column shows YTD status, i.e. looking up for the last entry in the row. Now, i need the YTD cell to change color based on the following conditions:
1. If YTD value meets target 100% (match value in cell for target) then turn green;
2. If YTD value is equal or greater than the set value of progress for YTD (i.e. 100%/12 months=8.33 for each month. It means to tell that we are on schedule the YTD value should be equal or greater than 8.33 for Jan, 17 for Feb, 25 for Mar and so on multiplying 8.33 for each month, and 92 in Nov) then it means we are on schedule and YTD cell turns yellow;
3. If as above the YTD cell is less than of the progress value for the YTD period of time then it turns red.

The table updated on monthly basis and the formula should be able to tell what is the month today and what should be the progress for the current month and apply formatting accordingly.

I do not know if it is at all possible to build such formula.... Any help is highly appreciated!  Register To Reply

7. ## Re: Conditional formatting if equal or greater than average in the range of cells

Okay, selecting D6:D8 and clearing all of the conditional formatting that you previously put in there.

Red:
Formula: =Q6<D6/12 *COUNT(\$E6:\$P6) (alternately, you could have the default just be red and just have 2 rules)

Yellow:
Formula: =Q6>=D6/12 *COUNT(\$E6:\$P6)

Green:
Cell Value Equal to D6
See attachment.
Does that work for you?  Register To Reply

8. ## Re: Conditional formatting if equal or greater than average in the range of cells

Thank you! That is awesome! Works perfectly!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 