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
"with the rule that would count 2 as 1" -- what does that mean?– fixer1234Jan 28, 2019 at 10:43
with the rule that would count 2 as 1
"select Col1 where Col1 is not null"; 0); 2; 1))))
It's gotta be wrong, cause average couldn't be more than 1. Everything above 1 is considered as 1. Also I'm sorry but copy pasting didn't help. I've changed cell names. What should I do? Jan 26, 2019 at 14:50
with the rule that would count 2 as 1and
everything above 1 is considered as 1are two different things– user0Jan 26, 2019 at 15:00
everything above 1 is considered as 1
IF(A1:1<2 ; ROUNDDOWN(A1:1; 0);
IF(A1:1>=2; 1; )))))
1Thank you very much. I guess it helped. Jan 28, 2019 at 2:13
@ДимаР. you are welcome, webapps.stackexchange.com/help/someone-answers– user0Jan 28, 2019 at 2:14
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:
Previous comment solved it. I didn't mention that there could be only whole numbers in range from 0 to 2. And all of it is to count successful actions in percents and 2 not really differs from 1 percentagewise, but could mess heavily when counting average. Jan 28, 2019 at 2:18
@ДимаР. Well, then update your question so that it contains all necessary information– user55949Feb 27, 2019 at 9:51