Ask Your Question

Average row with multiple numbers (0 1 3 5 6 1 2 3 5 4 0 )

asked 2019-01-09 22:21:19 +0200

andradada gravatar image
 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.

edit retag flag offensive close merge delete


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?

Lupp gravatar imageLupp ( 2019-01-09 22:34:49 +0200 )edit

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.

andradada gravatar imageandradada ( 2019-01-09 22:50:08 +0200 )edit

(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
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 ...(more)

Lupp gravatar imageLupp ( 2019-01-09 23:01:08 +0200 )edit

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.

ve3oat gravatar imageve3oat ( 2019-01-10 21:14:55 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-01-09 23:57:02 +0200

Lupp gravatar image

updated 2019-01-09 23:59:54 +0200

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!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-09 22:21:19 +0200

Seen: 57 times

Last updated: Jan 09