yogi_Compute Row By Row Sum Of Latest 5 Non-Blank EVENT Numbers

                                          Google Spreadsheet   Post  #1110
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 11, 2013
user Brodwyn Appanna :(http://productforums.google.com/forum/?zx=kt0soilg3ufa#!category-topic/docs/spreadsheets/Cq3GwQB8_jU)
How do I sum ONLY the first 3 non-blank cells in every row?

I want to sum only the first 3 numbers in each row. There are more than 3 entries in every row with variable blanks between.

So the outputs I want are:

Row 1: 3 (because of 1+1+1 -- these three numbers added were from a range of 7 cells because of the blanks).

Row 2: 3

Row 3: -1

Row 4: -1

Row 5: -1

Row 6: 1

Row 7: 1

I have searched for a while now and got some leads but in the end, still a dead end. I ideally don't want helper/additional rows to hold data, but calculate everything in one cell. Here is what I have pieced together so far.

1. I can strip the blanks from the row and put into an array in memory of only the numbers with the filter() function. I discovered I can also apply the sum() or count() functions to this array of non-blank numbers. E.g. of this just on the first row:

=sum(filter(A1:P1,A1:P1<>""))

This gives the correct sum of the whole row (but now adding the array without blanks in memory).

2. I worked on the idea of removing blanks because know that when a "row range with number in each consecutive cell" is given to the offset() function, asking for just the first few numbers to be added is possible. So if on the original first row I just wanted to sum the first two cells of the row, I would use:

=sum(offset(A1:P1,0,0,1,2)

This would have added just the first two cells of the row -- 0,0 saying start from the start of that range and 1, 2 speaking to height and width (height=1, width= how many cells to add).

3. So since the filter() part of the first function holds a stripped array in memory, I decided to feed it to offset and specify 3 in the width argument to sum the first three non-blank numbers:

=sum(offset(filter(A1:P1,A1:P1<>""),0,0,1,3)

I received an error, though, saying the argument needs to be a range. I assume there is a difference between an array and range data structure, that the filter() function returns an array, but the offset() function can only take a range.

So close, yet no cigar!

Please help.

---

hanks Yogi for the reply.

Here is the link to the sample of data:

https://docs.google.com/spreadsheet/pub?key=0Avzp169F3TuqdG1ZMXJnSXJaRm5vUTFGV1hQM3F0dHc&output=html

It is the outcome of events on different dates (columns) at different sites (rows).

1 represents a successful outcome.

-1 represents an unsuccessful outcome.

Column B -- I calculate the total number of events at a site.

Column C -- I calculate the % of successful events from all the events that took place.

Column D is where I'm stuck.

I want to sum the totals from the last 5 events (so I want to sum all the 1s and -1s from the last 5 events).

If we take just the first site (row 2), I tried to calculate the sum of the first 5 events in cell D2:

=sum(offset(filter(E2:CQ2,E2:CQ2<>""),0,0,1,5))

I basically tried to strip the whole row of blanks and put into an array (in memory) that only contains the event values (1s and -1s). I then tried to use the offset to get only the first 5 elements from that array and then sum them.

I get the error "Argument must be a range".

I appreciate any help you can offer.

Thanks

---

Oh yes and just to answer the part about my expected outcomes, the cells in column D should yield the following if the first 5 numbers in each row (which is either a -1 or 1) are added together:

D2: 1 (because -1+1+1+1-1)

D3: 3 (because -1+1+1+1+1)

D4: 1 (because +1+1-1-1+1)

... and so on for D5, D6, D7, D8, D9, D10, D11

--------------------------------------------------------------------------------------------------------

following is a solution to the problem