A | B | C | D | E
1 (1 2 3) | (3 3 3) | | |
2 (3 3 3) | (1 2 3) | | |
3 (4 4 4) | (4 4 4) | | |
...
If I make average =AVERAGE(A1) it doesn’t count every number in the row to sum.
Separating every number in different rows and then doing some hack to average them makes the whole sheet messed up.
I want simple average A1 but counting all numbers separated by spaces.
This looks as if A1 (and additional cells contain text. There is no AVERAGE() function (or any numerical / satatistical function) extracting numbers from text. Why did you expect that?
I’m sorry, but I think you’ve confused something.
I placed =AVERAGE(A1) in the C1. So all numbers (without text, just numbers) in A1 can be averaged, and the output placed on C1 that’s previously empty.
I just want to average a row that has multiple numbers “0 3 5 5”, but the simple command average isn’t working because it only works if numbers are not separated by space, but each number in different rows.
(It’s annoying. Something spoilt my original comment during my attempt to post it. And I surely didn’t confuse something.)
The sheet as it is is messed up.
A1 (and additional cells) contain text. There is no AVERAGE() function (or any numerical / satatistical function) extracting numbers from text. Why did you expect that?
With my LibO
{=AVERAGE(IFERROR(VALUE(XTEXTSPLIT(" ";1;MID(TRIM(A1);2;LEN(TRIM(A1))-2);100));""))}
would work, but the central function XTEXTSPLIT() is a user function programmed in Basic.
First of all understand that it is a basic principle of numeric / statistical functions in spreadsheets to work with numbers referenced from cells one by one. From what you have you can get that with the help of Text to Columns....
If you can give evidence for your exceptional need to keep your sequences the way you did, I can provide you with my mentioned user function though it is not in releasable state.
Further to what @Lupp has told you, you have confused us with your use of terminology. In Calc a “row” is a horizontal sequence of cells, each of which might contain text or a single number. Three numbers, all in the same cell, are not a row, and are difficult to define as numbers. You are very fortunate that @Lupp has given you a solution to what you want to do and in the way you want to do it.
It would be trivial, and much more “regular”, to accomplish the same thing if you placed each number in its own cell. Then your first average (what you called A) would be =AVERAGE(A1,A2,A3) and the second (B) would be =AVERAGE(A4,A5,A6) or just =AVERAGE(A4:A6). Having each number in its own cell always works and requires no special coding.
I thought a bit about the matter and decided to provide an example. It is mainly for showing what complications come with the attempt to evaluate number sequences from textual tuples.
Demo file averageFromTuples_1.ods containing user code and provided as is without any guarantee. Use for critical tasks not recommended!