[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sat Apr 21, 2012 12:07 am    Post subject: Sumproduct of data-cells between specific value I have a column of numbers and empty cells (with formula) looking like this 4 EMPTY EMPTY 2 EMPTY EMPTY 3 EMPTY EMPTY EMPTY 2 EMPTY EMPTY EMPTY EMPTY EMPTY 4 EMPTY EMPTY What I want to do is to find out the SUMPRODUCT of the cells that contains any data, between a cell containing a specified data. For example, (look at the chart above) The amount of data-cells between cells containing number 4 is 3 (2+3+2). The amount of data-cells between cells containing number 2 is 1 (3). If you know a way to DELETE thos empty cells without affect the order of the data, that would be useful too. Thank you and have a great day
karolus

Joined: 22 Jun 2011
Posts: 208

Posted: Sat Apr 21, 2012 1:30 am    Post subject:

Hi
Sum between the 4's
 Code: =SUM(OFFSET(A1;MATCH(4;A1:A100;0);0;MATCH(4;A1:A100;-1)-MATCH(4;A1:A100;0)-2))

Karo
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 2:38 am    Post subject: cant get it to work [quote="karolus"]Hi Sum between the 4's [code]=SUM(OFFSET(A1;MATCH(4;A1:A100;0);0;MATCH(4;A1:A100;-1)-MATCH(4;A1:A100;0)-2))[/code] Karo[/quote] Can´t get it to work, maybe I´m doing something wrong. I pasted the code into B1. It generates dfferent numbers depending on the values I write in column A, but the generated numbers does´nt seem to follow a logical pattern at all. I´m using the latest open office, Windows. Thank you..
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 1875
Location: Sydney, Australia

Posted: Sun Apr 22, 2012 3:36 am    Post subject:

 Code: =SUM(OFFSET(A1;MATCH(4;A1:A100;0);0;MATCH(4;A1:A100;-1)-MATCH(4;A1:A100;0)-2))
sums the numbers between the 4 closest to A1 and the 4 closest to a100.

If you want the sum of numbers between the 4 closest to A1 and the next 4 close to A1 then try...
 Code: =SUM(INDEX(A1:A100;MATCH(4;A1:A100;0)+1):INDEX(INDEX(A1:A100;MATCH(4;A1:A100;0)+1):A100;MATCH(4;INDEX(A1:A100;MATCH(4;A1:A100;0)+1):A100;0)-1))

Ken Johnson
_________________
darren5
Newbie

Joined: 21 Apr 2012
Posts: 1

 Posted: Sun Apr 22, 2012 3:45 am    Post subject: my Hello!! Good post!!_________________When I routinely have to build writings or various variations of training work I continuously access to papers buy
fivefootnine
Power User

Joined: 09 Apr 2012
Posts: 60

 Posted: Sun Apr 22, 2012 3:53 am    Post subject: Thanks Guys!! I´d like the code to work for ALL THE 4´s all the way down to A100..?? is that possible?? I´ve been thinking and what I need is a helper column located to the right of the column containing data, that counts how many times a numbered cell turns up between a cell containing a certain information (number 4) Like this EMPTY CELL 1 EMPTY CELL EMPTY CELL 4 (helper starts counting) EMPTY CELL EMPTY CELL 5 (helper counts 1) 6 (helper counts 2) EMPTY CELL EMPTY CELL 4 (helper stop counting and the number 2 is printed in this cell) IF the number 4 turns up two times in a row, the helper would print a 0 (as there are 0 number between the two 4´s)
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum