average and stdev

[Jenny]

Say I have two columns (A and B) going from rows 1 to 20. Is there
some elegant way to add the numbers across each row, THEN take the
average and stdev of the column(besides making a new column with the
sum across the row)? I've done it (inelegantly) by doing:
=AVERAGE(A1+B1, A2+B2, A3+B3, .... , A19+B19, A20+B20)
and
=STDEV(A1+B1, A2+B2, A3+B3, .... , A19+B19, A20+B20)

it works, but it looks bad... just wondering if there was a better
way...

Thanks!

 

[Leo Heuser]

Jenny

One way:

=AVERAGE(A1:A20+B1:B20)

This is an array formula and it must be entered with
<Shift><Ctrl><Enter>, also if edited later.

Similar for STDEV.

 

[A.W.J. Ales]

Jenny,

=AVERAGE(A1:A20+B1:B20) and
=STDEV(A1:A20+B1:B20)

You have to "array enter" these formula i.e.
type the formula and then pres Cntr + Shift + Enter

the formual's will be surrounded bij { }.

(You shouldn't type these yourself, if you array enter they will be added to
your formula)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Beto]

Say I have two columns (A and B) going from rows 1 to 20. Is there
some elegant way to add the numbers across each row, THEN take the
average and stdev of the column(besides making a new column with the
sum across the row)? I've done it (inelegantly) by doing:
=AVERAGE(A1+B1, A2+B2, A3+B3, .... , A19+B19, A20+B20)
and
=STDEV(A1+B1, A2+B2, A3+B3, .... , A19+B19, A20+B20)

it works, but it looks bad... just wondering if there was a better
way...

Yes there is: Array Formulas...

=AVERAGE(A1:B1+A20:B20)

=STDEV(A1:B1+A20:B20)

Do you know how to enter array formulas? If you don't, once you write
the formula, instead of pressing ENTER, press CTRL+SHIFT+ENTER. You'll
see in the "formula line", the formula surrounded by brackets {}, this
means is an array formula.

Regards,