Ask Your Question
0

Partial sums of a column of values

asked 2019-02-23 01:53:51 +0200

Ramil123Abc gravatar image

I have a column of values like this:
0`
1
3
0
0
12
45
56
0
How do I compute the subtotals of contiguous nonzero numbers?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-02-23 02:30:33 +0200

Lupp gravatar image

updated 2019-02-23 02:54:43 +0200

See this example.

===Edit1: Enhanced Example===
Here it is.

edit flag offensive delete link more

Comments

Thanks! I understand that the A2:A1001 is assumed to be the maximum run the data can have?

Ramil123Abc gravatar imageRamil123Abc ( 2019-02-23 02:43:42 +0200 )edit

It's not just the maximum run (of non-zero values), but the maximum range of any values.
You were right - basically. The range of 1000 rows moves down with the starting row. That's not a really satisfying solution. (It seems I had a kind of blackout.)
To expand the range without too much decrease in efficiency can be achieved using helper formulae that only need to look at two rows instead of many. The partial sums can then be taken from a "Pivot Table".
See the enhanced example.

Lupp gravatar imageLupp ( 2019-02-23 02:53:40 +0200 )edit

Wow, you already did what I have to accomplished next. Thank you very much!

Ramil123Abc gravatar imageRamil123Abc ( 2019-02-23 03:36:40 +0200 )edit
Login/Signup to Answer

Question Tools

Stats

Asked: 2019-02-23 01:53:51 +0200

Seen: 36 times

Last updated: Feb 23