Need formula(s) for Moving Totals to automatically update within that or another formula

This will return a result from row 1 based on finding the last non-blank cell in row 2.

Formula:

Please Login or Register  to view this content.

Change the ranges to match the columns you have data in.

Hope that helps.

Note to self - read the full question so I can answer all of it (I blame being on night shift having been awake for over 24hrs!)
Anyway, back to your question: I'll go through it in stages. You're on exactly the right lines by considering using the Address function.

1. The formula I gave above will find the last non-blank entry in the range A2:Z2 (as I said above) then return the result from A1:Z1 (that bit of the formula is the 'result vector').

2. To get the column reference, we can change the result vector to give the column:

Formula:

Please Login or Register  to view this content.

I've made the column refs absolute but left the row refs relative, for dragging down. What this will give is the column number - for example '25' (not the 25th column in the range, but the 25th column in the worksheet). However, for building a range, we need the column letter.
This is a common question in Excel - how to convert the numerical result of 'Column' to the column letter. Luckily, there's a simple solution to it, which is to put the result into the 'Address' function, just as you had already thought of . Putting our formula from above into the 'column ref' part gives us:

Formula:

Please Login or Register  to view this content.

If the previous result was '25' this will give us '$Y$2' (since column Y is column 25). So that's the cell address of the last non-blank cell in the range A2:Z2.

3. To find the cell address six back from there, just subtract 5 from that number - as you said:

Formula:

Please Login or Register  to view this content.

This will give you $T$2.

4. To put those two results into another formula as a range, you need to combine them using the Indirect function. Where you would normally put the range (for example $T$2:$Y$2), you would use this instead:

Formula:

Please Login or Register  to view this content.

Your two choices are CountIf (to count 'n' or 'y') and CountA (to count non-blanks):

Formula:

Please Login or Register  to view this content.

Personally, I'd probably use CountA, as you can then drag it down without having to change the 'n' to a 'y' - but if you don't have too many rows to do then you might prefer to be explicit.


The attached file shows all of this working. Hope that's what you need.

You're welcome and thanks for the rep.

The reason the formula only works on the same row as the data is the inclusion of ROW() in the Address() parts of the formula. That will always return the row which the formula is on. For example, if your formula is on row 4, ROW() will return '4'. If you want it to refer to row 2 instead, then you need to include a cell ref: ROW(A2) for example.

So, to put the formula which refers to row 2 on another row, you can change the CountIf/CountA formulae to:

Formula:

Please Login or Register  to view this content.

Hope that makes sense.

Edit: since I'm explaining one part of the formula, I might as well explain the Lookup part too.
• Lookup syntax is Lookup ( a value , in an array , give result from another array-optional ). The important thing to remember about Lookup is that it looks through the array in ascending order and returns the nearest match working backwards.
• So, in the formulae above, $A2:$Z2<>"" will give an array of True/False statements, like this: {True, False, True, True, False}.
• Excel treats True as 1 and False as 0, so 1/True gives 1, and 1/False gives a #Div/0 error. So the above array becomes: {1,#DIV/0!,1,1,#DIV/0!}
• Lookup then looks through this for the value it's been told to find, in this case 2. It doesn't find it, so returns the position in the array which is closest, -working backwards-. That, in this case, is the fourth value.
• If there's a 'result vector' specified, the Lookup will then return the equivalent value from that vector. So if the result vector for the above example were {11,22,33,44,55} the Lookup would return the fourth value, 44.
I hope that helps understand what the 'Lookup' is doing.