Find the average based on color filled cell

[paguilar1986]

Hello everyone,

I wanted to know if there is a formula, or if anyone has any better suggestions on how to solve my current problem with finding the average of a range of cells, based on the color of the cell and if they value is either "Yes", "No", or "0".

I know that I could also use the filter by function, but I would like to see if anyone knows a formula that could possibly calculate it without me having to use filter by COLOR.
For example, when a cell that is filled with YELLOW, and has a YES, No, or 0... I can find the average of only those. Currently I have the following formula to find the average of all : =AVERAGE(IF(I3:I33="Y",1,IF(I3:I33="NO",0,"")))

Any suggestions or if you all can think how I could go about doing this in a better way?

Here is a snip of what I am talking about.
Excel.png

[Gregb11]

Please see the yellow banner at the top of the page for attaching a sample workbook. Are the cells colored for a reason, meaning, what's the logic. Maybe that could be used in calculating the average.

[paguilar1986]

They are colored because the user wanted to distinguish which students are non-English speaking, so if they are filled with yellow they are all the non-English speaking students in the classroom.


For example: If they have a Y on column 1 they are labeled Non-English speaking. In column two based on their score, they get a YES, No, or 0.

[Gregb11]

Can you have a column for Y/N for non-English speaking?
Can you attach a sample as per the instructions at the top of the page?
I'm not aware of how you'd be able to do it based on color alone with a formula or or without filtering the data.

[paguilar1986]

I didn't know how to add the color yellow to the column, but Rebecca is defined as a non-English speaking student, so she has 'Y' in the second column.
In the third column she got a 'N' because she did not pass.

Gill is a English speaking student, so he does not have a 'Y' in the second column, so we know he is English speaking.

Sofia is a Non-English speaking student, so she has a 'Y', so it is easier to distinguish her from the other students.
I'd like to find the average only of the students who would be colored Yellow, which helps distinguish them as Non-English speaking.

Rebecca	Y	N
Gill	N	Y
Sofia	Y	Y
Emma	Y	Y

[Gregb11]

I would use AVERAGEIFS to do this. After asking twice, you still haven't attached a sample spreadsheet so it's a little difficult to produce an exact formula. Take a look at AVERAGEIFS and see if that helps.

[paguilar1986]

This is what I have come up with, but my Average is not coming out correctly.
=AVERAGE(IF(G3:G33="Y",1,IF(I3:I33="NO",0,"")))

[paguilar1986]

I would use AVERAGEIFS to do this. After asking twice, you still haven't attached a sample spreadsheet so it's a little difficult to produce an exact formula. Take a look at AVERAGEIFS and see if that helps.

I apologize, but I am trying to add an excel sheet, but I am doing something wrong because every time I go to click on add attachment, it is blank and won't let me add an excel document.

[wk9128]

I apologize, but I am trying to add an excel sheet, but I am doing something wrong because every time I go to click on add attachment, it is blank and won't let me add an excel document.

Please read and refer to this link, how to upload attachments
https://www.excelforum.com/the-water...his-forum.html

First New Thread Or Second Edit your Post
scroll down with your mouse and You will see your left hand side Attachments word , then click manage Attachments
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Gregb11]

In your reply, instead of clicking "Post Quick Reply", click on "Go Advanced". Then scroll down and click on the link for "Manage Attachments". Click on Choose File (and choose the file), then click on "Upload", then "Close this window", then "Submit Reply".

[paguilar1986]

Hope this helps.

=AVERAGE(IF(D3:D33="Y",1,IF(F3:F33="NO",0,"")))

[Gregb11]

Please try this:

=AVERAGE(IF(D3:D33="Y",IF(F3:F33="Y",1,IF(F3:F33="NO",0,"")),""))

[paguilar1986]

PERFECT!!! Thank you so much!

[wk9128]

Cell F35 Array formula , Drag right

Please Login or Register  to view this content.