Search code examples
excelaverage

How to create a function in excel for sum of cubes or higher powers?

I'm trying to implement p-means, as laid out in this question

If p=2, I can do SQRT(SUMSQ(data)).

If p=3, I need to do CBRT(**SUM OF CUBES**(data))

And so on. The first part is easy to scale, I can just raise the whole thing to 1/N. But how would I implement sum of cubes, fourth powers, etc. ?

Solution

  • "But how would I implement sum of cubes, fourth powers, etc."?

    With SUMPRODUCT:

    If your data is in A1:A3 for example:

    =SUMPRODUCT(A1:A3^3)

=SUMPRODUCT(A1:A3^4)

    etc.

    enter image description here

    I'd ditch the use of SUMSQ here and just use SUMPRODUCT all around.

    This causes an error if any non-numbers are present (such as -), while the SUMSQ() method does not. Is there any way to do this such that non-numbers (or just -) are ignored?

    One option is with IFERROR:

    =SUMPRODUCT(IFERROR(--$A$1:$A$4,0)^C1)

    enter image description here