Average formula for Google Sheets with substitution over some figures

Question

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, 2019 at 10:43

user0 · Accepted Answer · 2019-01-26 15:53:33Z

0

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

answered Jan 26, 2019 at 13:58

user0

25.8k8 gold badges48 silver badges76 bronze badges

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 1 and everything above 1 is considered as 1 are two different things
– user0
Jan 26, 2019 at 15:00

Add a comment |

user0 · Accepted Answer · 2019-01-26 15:56:24Z

0

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

answered Jan 26, 2019 at 15:50

user0

25.8k8 gold badges48 silver badges76 bronze badges

1

Thank you very much. I guess it helped.
– Дима Р.
Jan 28, 2019 at 2:13
@ДимаР. you are welcome, webapps.stackexchange.com/help/someone-answers
– user0
Jan 28, 2019 at 2:14

Add a comment |

serenesat · Accepted Answer · 2019-01-28 08:08:37Z

0

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:

=AVERAGE(A2:J2)/2

serenesat

9,82830 gold badges31 silver badges49 bronze badges

answered Jan 27, 2019 at 3:22

Erik Tyler

4,8202 gold badges6 silver badges10 bronze badges

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
– user55949
Feb 27, 2019 at 9:51

Add a comment |

3 Answers 3