Ignore DIV/0 Errors in Array?

[AnonyMouse]

Hi all...

I've got a spreadsheet that looks something like this:

Score1 Score2 AvgScore
1 1 1
2 1 1.5
NULL NULL #DIV/0

The spreadsheet takes two scores we enter an averages them. The trick is
that the spreadsheet is being fed from our database and people who haven't
been 'scored' yet get a NULL.

On a separate form, I am trying to run a =PERCENTILE function on the
AvgScore column to see what our top x% score is, etc. The problem is, so
long as the NULLs are in the data, there are DIV/0 cells in the array and
the PERCENTILE function errors out.

Ideally, we need the nulls there -- can't turn them into 0s because that
would throw the score avgs off and if we just turn them into empty cells
("") it will cause errors on other formulas. Is there a way to tell the
PERCENTILE function to ignore any cells containing an error condition in its
calcuation?

Thanks, Mouse

 

[Peo Sjoblom]

Try something like

=PERCENTILE(IF(ISNUMBER(C2:C4),C2:C4),1)

entered with ctrl + shift & enter

 

[Harlan Grove]

Peo Sjoblom wrote...

Try something like

=PERCENTILE(IF(ISNUMBER(C2:C4),C2:C4),1)

entered with ctrl + shift & enter

....

OK, but I'd think the OP would be better off building better logic into
the average formulas in the source range, e.g., something like

C#:
=IF(COUNT(A#:B#),AVERAGE(A#:B#),"")

 

[AnonyMouse]

Thanks for the post, Peo.

That seems to return a valid result, but the problem is it doesn't seem to
actually allow me to vary the actual percentile.

Here's a similar formula (for calculating percentiles for someone's first
score):
=PERCENTILE(Array2ndACD,'zzz2nd Read Groups'!D5)

The reference to 'zzz2nd Read Groups'!D5 allows me to actually specify the
percentile 'cut-off' in another form... I could just vary it in the
equation, like this:
=PERCENTILE(Array2ndACD,.80)

Your formula (entered as an array) returns a value but doesn't actually
allow me to manipulate the percentile. I tried the following:
{=PERCENTILE(IF(ISNUMBER(ArrayAvgACD),ArrayAvgACD),1)}
- your original formula

=PERCENTILE((IF(ISNUMBER(ArrayAvgACD),ArrayAvgACD),1)),.80)
- following the same syntax as a regular PERCENTILE formula... This
generates a "This formula contains an error" message no matter whether I
enter it plain or as an array.

I'm sure it's a syntax error on my part. And thoughts?

Thanks so much!