Ignore text with SUMPRODUCT [duplicate]

Question

This question already has an answer here:

Implement p-mean in excel (1 answer)

Closed 5 months ago.

I'm using SUMPRODUCT() to calculate the p-mean of data where p=3:

=(SUMPRODUCT(E2:E99^3)/COUNT(E2:E99))^(1/3)

There are -'s mixed into the data. How can I ignore them so as not to get a value error and still get the correct answer (as if the -'s weren't there)?

Answer 1

To deal with non numbers in the range use this array formula:

=(SUM(IF(ISNUMBER(E2:E99),E2:E99^3))/COUNT(E2:E99))^(1/3)

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.