[RESOLVED] Calculate mean with missing values

[Naïde]

Hello,

I was wondering if there was any way to calculate a mean on several columns despite missing values ?
I attached a small simple example file.
In fact, when I add a computed variable and enter the mean formula, the mean is calculated only when all the columns have a value. When there is one missing value in a column, the mean is not calculated. When there is a missing value, I would like to calculate the mean on the others values, "ignoring" the missing one. Is it possible ?
Thanks for the help !

[jonathon]

ni naide,

no, sorry, this isn't possible at this time. we're just looking at the best way to provide it now.

we'll hopefully be able to come back to you soon.

with thanks

jonathon

[Naïde]

Hi Jonathon,

Thanks for your quick answer. I am looking forward to test this improvement !
Best,

[jonathon]

hi naide,

we've added an ignore_missing argument to the MEAN and the SUM now.

you can go: MEAN(A, B, C, D, ignore_missing=1)

you'll need the 0.9.5.2 or newer

jonathon

[Naïde]

Hi Jonathon,

I've just tried the ignore_missing argument and it works perfectly, thanks a lot !!!!!
And thanks for the amazing work you're doing on this software !

Best,

[WorkScientist]

I'm testing out the ignore_missing=1 functionality, and it seems that it works to average two vectors' values (ignoring missing) for missing values on the second vector, but not first. For example (where . indicates a missing value),
X Y MEAN(X,Y,missing=1)
2 . .
3 5 4
. 3 3

Possibly I'm missing a command in the function. Assist, please and thanks?

[jonathon]

hmm, seems to work here. could you double-check? this is what i get:

Screen Shot 2019-02-22 at 12.35.37.png (450.99 KiB) Viewed 1572 times

cheers

jonathon

[gulgunaydin]

"ignore_missing" comes really handy, thanks!

I was wondering if there is any way to calculate a mean on several columns when missing values don't exceed a certain number? For example, when taking the mean of 10 columns, I'd like to calculate the mean when there are missing values in only 2 or fewer of the columns.

[jonathon]

hey,

there's not an easy way to do it, but you can do it with the IF and IFMISS functions. for example if you have the columns A, B, C, D you could create a computed column called `N Missing` with the formula:

IFMISS(A, 1, 0) + IFMISS(B, 1, 0) + IFMISS(C, 1, 0) + IFMISS(D, 1, 0)

and then a mean computed column with the formula:

IF(`N Missing` <= 2, MEAN(A, B, C, D, ignore_missing=1))

(or you could combine these two formulas into one, but it becomes harder to read).

Screen Shot 2019-07-01 at 13.30.06.png (118.22 KiB) Viewed 963 times

cheers