Search code examples
textsumformulalibreoffice-calc

Sum numbers in a delimited text and return average

I have the following numbers as text in a cell:

"12-14-14-16-18-10"

And now I need to calculate the average but I do not want to create extra columns since the length of the data varies.

Is there any way to do this using a formula?

Solution

  • I know this is an old thread, but let me add my two cents. Use FILTERXML and some XPATH magic =)

    =FILTERXML("<t><s>"&REGEX(A1;"-";"</s><s>";"g")&"</s></t>";"sum(//s) div count(//s)")

    enter image description here

    We could even implement a check on numeric nodes:

    =FILTERXML("<t><s>"&REGEX(A1;"-";"</s><s>";"g")&"</s></t>";"sum(//s[.*0=0]) div count(//s[.*0=0])")

    enter image description here

    Looking at this I find it unfortunate Excel won't allow direct manipulation inside the expression itself. Suprised LibreOffice does!