Find Average While Accounting For Variables

Hi all,

I've been keeping track of Wordle scores for my department at work and I wanted to correct a few flaws that's been bugging me. I have a row for each co-worker (9 total) and a column for each round (100+) Everyone shares their scores in a group text thread and I have just used the AVG function to generate each player's average (in other words, the average number of guesses it takes them to get the correct word). This doesn't account for a few things:

1. If the player doesn't share their score. Any formula to even out or penalize the blank cells?
2. I assume Xs are also not counted correctly as they should increase a players average. Any thoughts?

Let me know if you have any insights. I'd love to make everyone's average a truer representation (and not just because I'm not the winner)

Thanks!

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Options:

(SUM all actual scores + "count of blanks" * Penalty Points + "count of X " * Penalty points)/ number of rounds

this assumes blanks / "X" entries do not have a valid reason for not reporting the score.

You could increase the penalty points (increasing scale) depending on number of blanks/ Xs accrued to "encourage" players to provide accurate/timely data!