I have data that looks like this, but with more rows and columns.

For each row, I want the number of columns with entries other than 99999, and the mean (or median) of those non-99999 entries.

mollwollfumble said:

I have data that looks like this, but with more rows and columns.

For each row, I want the number of columns with entries other than 99999, and the mean (or median) of those non-99999 entries.

Global find and replace: – (dash) for 99999

Do your calculations. (One cell, first row, then fill down.)

Copy and paste special (values): calculation column.

Global find and replace 99999 for – (dash).

For data in I10:M10,with first formula in N10:
=COUNTIF
=SUMIF/N10

For median, I don’t know (other than a VBA function, that would be quite easy).

Can’t remember how to post code here.

Click on Quote to see text in full.

The Rev Dodgson said:

For data in I10:M10,with first formula in N10:

=COUNTIF
=SUMIF/N10

For median, I don’t know (other than a VBA function, that would be quite easy).

Try reading the help:

The Rev Dodgson said:

Can’t remember how to post code here.

Click on Quote to see text in full.

The Rev Dodgson said:

For data in I10:M10,with first formula in N10:
=COUNTIF(I10:M10,"<>99999")
=SUMIF(I10:M10,"<>99999")/N10
For median, I don’t know (other than a VBA function, that would be quite easy).

For Median:

=MEDIAN(IF(I10:M10<>99999,I10:M10,""))

Has to be an array formula.
Enter as shown (or copy and paste)
Press F2
Press Ctrl-Shift-Enter

The Rev Dodgson said:

For Median:

=MEDIAN(IF(I10:M10<>99999,I10:M10,""))

Has to be an array formula.
Enter as shown (or copy and paste)
Press F2
Press Ctrl-Shift-Enter

Thanks to both of you. I’d forgotten that there was such a function as “median”, “countif” and “sumif”.
I’m startled that “dash” works, but it might at that.