Ask Different is a question and answer site for power users of Apple hardware and software. It only takes a minute to sign up.
Sign up to join this community
I would like to add up (and do other functions) to multiple numbers in a single cell separated by semicolons.
The idea is that I have multiple values (e.g. „15; 20; 25; 25“) logged in a cell and want to add them all up AND THEN divide them by the amount of numbers logged in said cell. The amount of numbers logged can varied from cell to cell. Basically, I just want to have the mean value of each cell.
There are several ways to handle this, all with complications and side-effects. This is because spreadsheets, in general, expect one datum per cell, and in this case, you've got at least 4.
Let's assume the contents of cell A1 is "15;20;25;25"
One way is to split up the contents of that one cell into an appropriately sized range. Instead of one cell, you might have A2 = "15", A3 = "20", A4 = "25", etc..
Numbers doesn't have a single command for parsing strings like A1 into multiple cells, except when you open a non-native format file, like a .txt, .csv, or .xls file. You may have better luck adjusting how data like that is input into your spreadsheet.
Another way is to use Number's Regex command. Here's a trivial example of counting the number of semi-colons in that cell:
=COUNTMATCHES(A1, REGEX(";"))
displays "3".
Note that setting up a robust regex is non-trivial.
Have you looked at CalcService? It will let you enter an equation and evaluated it, anywhere you can type text. Don't know that it will do averages, but it certainly can do basic math on a string of text.
I believe CalcService simply passes the current text selection to the shell command bc, which you can look up and see if it will do what you want to do. You can test expressions in the Terminal until you get what you want.
