Re: Editing auto-generated statistic cells

[Andreas J Guelzow <aguelzow pyrshep ca>]

On Fri, 2014-02-14 at 22:01 +1100, Pete Crite wrote:
> Hi,
>
> I'm attempting to do some repetitive statistics on some data. I'm not
> sure if it's important, but I'm using the menu item at Statistics > One
> Sample Tests > Claims About a Mean.
>
> I'm planning on testing about 30 sets of data, and thought it would be
> quickest to copy-paste the statistical formulae into new positions.

I cannot imagine that performing 30 separate tests is the appropriate
way to go, but you will now the circumstances better.

It seems to me that correctly adjusting the formulae, copying them and
then verifying them will take longer than just create them form scratch,
especially since the tool allows you to perform several tests at once by
selecting all data and grouping cells into separate data sets by column
or by row or by area.

I can't see any need to copy paste those formulas.

> Hence, I attempted to make all the references relative, by removing the
> $ (e.g. $B$2 ---> B2). However, I can't seem to modify all the cells
> properly. e.g. for the formula for Observed Variance
>
> {=var(iferror(Sheet1!$B$2:$B$2,""))}(1,1)[0][0]
>
> If I click the cell, the formula changes immediately to the part within
> {}. i.e.
>
> =var(iferror(Sheet1!$B$2:$B$2,""))
>
> I can leave the formula unchanged, or remove the $, but either way I get
> an error of #DIV/0! I've also tried to manually enter the whole formula,
> i.e. including the {} and the subsequent code, but gnumeric thinks this
> is plain text.
>
> Is it possible to modify all auto-generated statistic cells so that they
> can target different data?

You can edit them like any other spreadsheet formula. The formulas you
seems to have difficulties with are array formulas. You need to select
the whole array
{=var(iferror(Sheet1!$B$2:$B$2,""))}(1,1)[0][0]
would be a 1by1 array so just selecting the cell would be fine. While
you are editing it is will look like
=var(iferror(Sheet1!$B$2:$B$2,""))
and you reenter the array formula with
ctrl-shift-enter rather than just enter.

Andreas