# Average formula for Google Sheets with substitution over some figures

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 '19 at 10:43

with the rule that would count 2 as 1

``````=ARRAYFORMULA(AVERAGE(VALUE(SUBSTITUTE(QUERY(TRANSPOSE(A1: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 '19 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 '19 at 15:00

everything above 1 is considered as 1

``````=AVERAGE(ARRAYFORMULA(IF(A1: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:

`=AVERAGE(A2:J2)/2`

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