| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
karolus
OOo Advocate
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 |
|
| Back to top |
|
|
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.. |
|
| Back to top |
|
|
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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) |
|
| Back to top |
|
|
|
