YTD Totalization Excel 2010

Question

Ok I have a situation We have monthly data for accounts

                May June July Aug Oct Nov Dec Jan Feb Mar April 
Jackson Hall    2   5     8
Goolrick Gym    2   4    10   15  17
Brent Hall      1   6

Each month is in its own column. Then each account has a year to date total.

What I want to do is make a heading called Total. In this Column I want it to pull the most recent Data entry.

I need the Total column to pulls the 8 from July for Jackson, the 17 from October for Goolrick and the 6 from June for Brent Hall.

Total
8
17
6

Answer 1

depending on the size of the spread sheet one of the easiest ways to do it is to use something like an * after the month with the last entry.

Jackson Hall 2 5 8 * and then use the following formula

=OFFSET(A2,0,MATCH("*",A2:C2,0)-1) in your final table.

There are other ways of doing it but would involve reversing your table so the months were inserted each month. Excel works left to right, rather than right to left.

Equally there's other more complex ways of doing it but that might be the easiest for you.

Answer 2

Assuming that each column is the cumulative total (by definition of YTD), and thus strictly increasing left-to-right, you can use the Max function to obtain the right-most column entry.

Answer 3

To pick off the last in a row (whether or not an ascending series, whether or not the series contains blanks) with a formula only: