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? – fixer1234 Jan 28 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 at 14:50
  • @ДимаР. with the rule that would count 2 as 1 and everything above 1 is considered as 1 are two different things – user0 Jan 26 at 15:00

everything above 1 is considered as 1

                      IF(A1:1<2 ; ROUNDDOWN(A1:1; 0);
                      IF(A1:1>=2; 1; )))))



I think perhaps there is a language/communication issue here.

Do you mean:

  1. That all of your numbers are between 0 and 2 (with no numbers lower than 0 and no numbers higher than 2)?

  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)?

  3. 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 at 2:18
  • @ДимаР. Well, then update your question so that it contains all necessary information – user55949 Feb 27 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.