Summing over a row, before and after a delimiter in Calc

English
#1

I have a row of numbers separated by a whitespace delimiter; i.e. [1 0, 2 3, 4 0, 4 1, 3 2]. I would like to do the following:

  1. Sum the numbers before the delimiter: 1 + 2 + 4 + 4 + 3 = 14
  2. Sum the numbers after the delimiter: 0 + 3 + 0 + 1 + 2 = 6
  3. Return the column where the maximum item before the delimiter occurs; 4 appears in columns 3 and 4, so ideally I would return both
  4. Return the column where the maximum item after the delimiter occurs; 3 appears in column 2

I have very little skill with Office or BASIC programming. I have tried using LEFT() and the FIND() functions to trim the left side of the input (for before the delimiter), but I cannot figure out how to substitute that in a SUM function. I also do not know how to search for and return the column where the maximum item occurs.

Any help would be greatly appreciated. I would also like to be able to do this down columns.

1 Like
#2

Assuming the quote above is the Content of A2:

=SUM(IFNA(REGEX($A2;"\d+"; ;SEQUENCE(LEN($A2);;;2))*1;""))	 #for 1; 2; 4; 4; 3
=SUM(IFNA(REGEX($A2;"\d+"; ;SEQUENCE(LEN($A2);;2;2))*1;"")) #for 0; 3; 0; 1; 2

enter both as array-function: ctrl shift enter

1 Like
#3

would definitely make more sense to organize it at import time :

image
image1261Ă—712 51.8 KB

and then go a reasonable way, e.g :

1 Like
#4

Both formulas produce an error (see closing parenthesis of the LEN function).

2 Likes
#5

Thanks for the answer. The text is not the content of single cell, but the content along a row; the first cell is “1 0”, the second is “2 3”, etc.

#6 
=SUM(IFNA(REGEX($A1:$Z1;"\d+")*1;""))  #first numbers
=SUM(IFNA(REGEX($A1:$Z1;"\d+$")*1;""))   #second numbers
2 Likes
#7

and

That’s for tasks 1 and 2.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.