I have a row of figures from 0 to 2, some cells are empty. I need to count an average for them, but with the rule that would count 2 as 1. Thanks
I think perhaps there is a language/communication issue here.
Do you mean:
That all of your numbers are between 0 and 2 (with no numbers lower than 0 and no numbers higher than 2)?
Do you mean that 0 should count as 0%, 1 should count as 50% and 2 should count as 100% (or, in decimal format, 0 counts as 0, 1 counts as 0.5, and 2 counts as 1)?
Is this statistical (e.g., population sample or probability)?
If "YES" to all of these, you may just need a simple formula.
Let's say your numbers are all in A2:J2. You would just need this: