+ Reply to Thread
Results 1 to 8 of 8

Average cells based upon font color

  1. #1
    Registered User
    Join Date
    01-31-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    4

    Average cells based upon font color

    Hello,

    I have some student test data that I've entered into excel. I have represented the male student test data by changing the font color to green and the female test data by changing the font color to red.

    I'm wanting to average the test data based upon gender (green data averaged & red data averaged).

    From my basic internet research, I've gathered that I'm going to need to go into VBA and create a user defined function.

    Is there anyone out there that could assist me with that?

    Thanks,
    Mike
    Middle School Teacher

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Average cells based upon font color

    what makes you change it to green or red?ie how are you determining male/female?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-31-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Average cells based upon font color

    I entered the data into the table and manually changed the color so the color is already changed by
    male = green
    female = red

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Average cells based upon font color

    how do you know they are male or female what is the logic behind the choice?

  5. #5
    Registered User
    Join Date
    01-31-2013
    Location
    St. Louis
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Average cells based upon font color

    ModuleGradeStatistics7thGrade20122013.xlsxThey are my students. I know which test scores are from the males and which ones are from the females. I have entered the data and color coded it manually by gender. Now, I need to take the column of data (both male(green) and female(red)) and average the male(green) and female(red).

    I've attached my spreadsheet for review.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Average cells based upon font color

    Hi Mike

    Here's a UDF modified from Ozgrid Business Applications
    Please Login or Register  to view this content.
    Notice the Formulas on Rows 18 and 19.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Average cells based upon font color

    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Average cells based upon font color

    I have to pipe in here with some unrequested advice. If you search this and other forums, you will hear many common reminders about good data management. One such thing I have picked up and enforce is "Color is not data".

    Color is a human-eye reference thing, it's not data. Whatever logic is used to apply colors can also be used to create actual data. Once you do this, all this extra plumbing goes away and you can go back to using simple formulas and functions for your data analysis.

    My tip: Add a column next the name and put the words MALE/FEMALE or M / F in there. Bingo, that's data.

    Once that's done, a normal AVERAGEIF() function can be applied.

    =AVERAGEIF(B:B, "M",C:C )

    Now, if you want to color THAT cell green since it's the Male Average....
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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