Can this even be done?

[Soccer_Dad]

WARNING: I am a rookie when it comes to excel.

I am wanting to track attendance to practice and games using excel that I can then send out to people. Currently, I manually go to each practice/game day column and mark yes/no. Then I go and adjust the formula for average attendance. Example, if this is the 5th practice of the month and someone has now attended 4, I change the formula to =4/5.

What I am hoping can be done is a formula that allows me to put a "Y" or a "N" in the appropriate column if a player attended and then have excel auto calculate the attendance percentage. Is this even something that can be done? If there was a way to color code the "No's" for attending that would be great as well, if not, I can keep manually doing that as well.

Thank you kindly in advance.

 

[Joe4]

Welcome to the Board!

You can use the COUNTIF function in Excel to count the number of cells in a range meeting a certain criteria.
So, if you had wanted to count all the cells in range A1:A10 that are filled with "Y", would could do so like this:

=COUNTIF(A1:A10,"Y")

To count the total non-empty cells in a range, you can use the COUNTA function, i.e.

=COUNTA(A1:A10)

So, just divide those two numbers to get your attendance percentage.

You can use Conditional Formatting to highlight all the "N" values.
See: https://excelribbon.tips.net/T006235_Highlighting_Cells_Containing_Specific_Text.html

 

[Soccer_Dad]

Thank you for such a quick reply.

To confirm, I would need to put the formula's in two different rows correct or is there a way to add both formula's to the same cell and divide them?

 

[Joe4]

To confirm, I would need to put the formula's in two different rows correct or is there a way to add both formula's to the same cell and divide them?

It can be done all in one cell. Just use put the divided by symbol (/) between each formula, i.e.

=[COLOR=#333333]COUNTIF(A1:A10,"Y") / [/COLOR][COLOR=#333333]COUNTA(A1:A10)[/COLOR]

 

[Soccer_Dad]

It can be done all in one cell. Just use put the divided by symbol (/) between each formula, i.e.

=[COLOR=#333333]COUNTIF(A1:A10,"Y") / [/COLOR][COLOR=#333333]COUNTA(A1:A10)[/COLOR]

THANK YOU!!!!

That and the conditional formatting to change color was everything I needed!! You saved me hours of time in the future.

Thank you again and I am sure glad I joined up to the forum!!

 

[Joe4]

You are welcome!

Feel free to come back anytime if you have more questions.

 

[jtakw]

.