+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

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

    Dear Excel gurus! Need your assistance please.
    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.

    Thanks in advance!
    Last edited by Abu Abdurakhim; 05-26-2013 at 02:05 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,186

    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?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    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!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,186

    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?

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    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!

  6. #6
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

    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!
    Last edited by Abu Abdurakhim; 05-25-2013 at 01:26 AM.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,186

    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?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-24-2013
    Location
    Aktobe
    MS-Off Ver
    Excel 2010
    Posts
    20

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

    Thank you! That is awesome! Works perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1