[Solved] "Last Entry" Running Totals

[JustBaffled]

Please can you help me create a simple total by adding together only the latest entries in Columns. I have attached a Sample Sheet which I hope explains my query. I have tried to do this but it needs somebody with greater skills than I have! I have Apache 4.1.10, thank you for your assistance.

Regards, Derek.

[Zizi64]

Please upload an ODF (.ods) type sample document here.

[RusselB]

For future reference, when requesting help with formulas it is better to attach a spreadsheet that has sample data rather than a pdf, as, realistically, that is the same as a screen shot, and almost useless.
I'll post again when I have a working solution, unless someone else beats me to it.

[Zizi64]

Try to shift the same year data into same row (where the years what have not data, the data cell will be empty with value zero)

Then you can summarize the last row or all of the rows. And you can use a conditional formula: if the nexr row not contain any data - ISBLANK() - then the result will be an empty string - "" -.

[platyhiker]

In your example, all your columns have steadily ascending values, so the latest value is also the maximum value in the column. *IF* that will *always* be the case, you can just add the maximum values - something like: =MAX(B2:B50)+MAX(D2:D50)+MAX(F2:F50)

If you data will *not* always have maximum value in the latest entry in the column, you can use COUNT() and to figure out where the last non-blank entry is, and OFFSET() to get that value. Here is a formula that will return the latest value in the X column: =OFFSET(B1;COUNT(B2:B50);0;1;1)

Note: In my examples above, I arbitrarily chose row 50 as the point where I expected data to not go beyond; depending on your expected data inputs, you may want to choose a different row and adjust the formulas accordingly.

[JustBaffled]

Sorry, I didn't think the matter through properly. I have a attached the same example in ODS format. Thank you for all the help received, I haven't had chance to look through them all yet but as soon as I am able I will do so. Thank you all.

Regards, Derek.

[John_Ha]

A simple way is to use a helper column. See Calc Functions listed by category for SUMPRODUCT

1. Place a 1 in the adjacent column where you want to select the number to be added.
2. SUMPRODUCT the column B3:B23 with the column C3:C23, which gives (B3*C3)+(B4*C4)+ ... +(B23*C23). As every term in C3:C23 is blank except C20, you get 18, the value opposite the added 1. Note I extended the formulae below the figures so it works for the next few years.

Conceptually, it is doomed to fail in time as it relies on user memory to add the 1 so it would be better to set the 1s automatically by climbing up the column C from bottom to top. Do something like (it's not quite correct but you get the idea) If Bn does not have a number AND C(n+1) does not have a number, set Cn to blank (use ""), otherwise set it to 1. This will place a 1 opposite the 18 and blanks everywhere else. Or climb downwards looking for "a cell with a number followed by a cell which does not have a number". The danger with all schemes like this is that placing a space in an empty cell, which is invisible, could mess up the logic if it is searching for blanks.

Diligent examination of Chapter 8 - Using the DataPilot in the manual will no doubt reveal a far more elegant solution.

LOOKUP could select the values based on the year. You enter the current_year as the key and LOOKUP the values using current_year, current_year-1 and current_year as arguments. Similarly with VLOOKUP etc.

[JustBaffled]

Thank you John and thank you everyone who has responded. I will have to spend some absorbing your suggestions and will also follow your advice with regard to the OO online help. In the meantime thank you again and I'll let you know how I get on.

Regards, Derek.

[John_Ha]

I think this may be a classic XY problem. You want to do X but you cannot. You come up with a solution Y which you find does not quite work and is probably not a good idea. You then ask for help to fix Y.

It is far better to say "I want to do X ..." and ask for help on X. That way we can consider all your options without getting drawn down the possible dead end of Y.

I think something based on LOOKUP and the year is probably much less error prone.

[JustBaffled]

I have set up John's simple solution using a "1" in an adjacent column and it works perfectly! The requirement to remember to insert a "1" isn't an obstacle because the annual inputs are either once or at most twice a year and aren't a problem as as the latest figure is received the "1" is moved at the same time. I did look at the suggestions made by everyone else but John's was the most appropriate for me.

I am still reading up on the "Lookup" solution and will also follow this up in the manual.

Again; many thanks for the expert responses received so quickly and so effectively.

Regards, Derek.