How to get final monthly data from a worksheet containing (partial) daily data

Question

Okay, one for the Gnumeric gurus out there. I figure this question is okay since there are a large number to do with Excel formulae, despite the fact I'm not sure that should be considered programming per se.

I have a two-sheet workbook, the first of which contains a line for each day of the year, and which has a column accumulating a certain value over the year:

Date         TimesMyDogHasBittenMe   Cumulative
----------   ---------------------   ----------
2016-01-01                       4            4
2016-01-02                       1            5
:
2016-01-31                       7           92
2016-02-01                       3           95
:

Actually, if you knew my dog, you'd know she was incapable of biting anything, but I had to think of some counter quickly.

The other sheet needs to have the final accumulated value for each month:

Month   MonthlyBites
-----   ------------
Jan               92
:

So the question is, how do I extract the final row for each given month in the first worksheet and place it in the second, keeping in mind the following.

1. If there is only partial data for a given month, the final entered day for that month is the one I need (the subsequent ones will be all blank). For example, as of today (Feb 7), the final populated row in the first worksheet is Feb 6 since the dog hasn't finished it's (mythical) aggressive behaviour for today.

2. Similarly March has no data since, obviously, we haven't started there yet. For that case, the month entry on the second sheet should be blank.

3. I cannot just sum the values for each individual day in a month to create a row in the second sheet. Though it appears that the figure I want is accumulating, it's not just a simple addition. I really need to extract the specific accumulator row from the daily table.

I've tried using vlookup but, while that can find the first blank cell in a column, it doesn't give me the last non-blank cell. I also can't figure out how to make it group by month.

The sort of thing I'm after is:

=lastif("a2:c999","c","c!= '' and month('a') = 1")

where:

• first argument is the lookup area;
• second is the column to extract; and
• third is the condition (non-blank and month is January).

Answer 1

Okay, here's how I ended up doing it. It's a little complex so I'm still open to other solutions. Initially, I added the Python plug-in and wrote some bespoke code to do it but that appeared to have the problem in that it wouldn't update without a manual update operation with F9.

So I created a few columns in the summary sheet (the other one being the detail sheet), one to hold the month number, one to hold the last day of the month, and the final to hold the row in which I found that date in the source table:

Month    Month#    LatestDate    Row    Cumulative
-----    ------    ----------    ---    ----------
Jan           1       31/1/16     36            92
Feb           2       29/2/16     57            95
Mar           3       31/3/16     57

The month number was a simple incrementing value but you could have calculated it from the month name if necessary - I opted for the simplest way.

The latest date in the month was the formula:

=date(year(detail!$A$2),Bn+1,1)-1

where detail!$A$2 was the first date cell on the detail sheet (used only to get the current year - I have a separate spreadsheet for each year). The n in this case is the row of the data given above. This formula gets the first day of the following month and subtracts one to get the last day of the desired month.

The row uses a look-up function over the detail data range holding the dates:

=match(Cn,detail!$A$2:$A$999,1)

The advantage of using match is that it will find the exact value or the highest value less than or equal to it. So, if there is no date in the detail sheet for March, it will give you the row of the last piece of data for February.

Now here's where it gets a bit complex. Gnumeric has an indirect function which will, when given a string representing a cell, return the contents of that cell.

Hence =evaluate("A3") is equivalent to =A3. Not so useful in that particular case but vital if we want to build a cell address dynamically then reference the contents of that cell. Therefore the expression for the Cumulative cells are:

=if(month(indirect(concatenate("detail!a",fixed(Dn,0,TRUE))))=Bn,
    indirect(concatenate("detail!c",fixed(Dn,0,TRUE))),
    "")

The condition in that statement checks the date cell for the row which was found to compare its month against the month we're looking for. If they're different then there were no detail rows with that month (as in the February/March case mentioned before, if the last date found when looking for the end of March was actually in February, then March has no data).

In that case, the cell is set to the empty string.

Otherwise, we extract the relevant column from the given detail row and use that in the summary sheet.

So that's it, not the prettiest code in the world but certainly something that works (including not needing manual recalculations).