Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a bank balance Excel sheet with:

  • Date in column A
  • Debit/Credit in Column B
  • Running balance in column C

On a separate sheet I want to have a 12 cells for months of the year that state what the balance in my account was on the last entry for that month that has a value in the balance column.

I have googled but can't find what I'm looking for.

Can anyone help please?

share|improve this question
    
Have a look at Excel's database functions, particularly DGET or DMAX (office.microsoft.com/en-gb/excel-help/dget-HP005209059.aspx). I've a feeling it's what you need. I'll see if I can elaborate later after work. –  Ellesa Nov 5 '11 at 4:48
    
Hi Kaze, that's something I've never seen before and interesting, but don't have the necessary excel background to see how I would adapt it to my needs. I would definitely appreciate your help. The number of rows (transactions) for any month varies, and as each month progresses the number of rows increases, so I can't see how to make the formulas in the 12 cells adapt, nor how to make the –  Tim Nov 5 '11 at 6:53
    
Hi Kaze, that's something I've never seen before. I don't have the necessary excel background to see how I would adapt it to my needs. I would definitely appreciate your help, thanks. The number of rows (transactions) for any month varies, and as each month progresses the number of rows increases, so I can't see how to make the formulas in the 12 cells adapt (maybe AA:CC ?). –  Tim Nov 5 '11 at 7:03
    
This is what I'm thinking I want to be able to do: End of January Balance = find max day value in column A from month January, then some kind of vlookup to return the Balance entry in the C column on the same row? End of February Balance = find max day value in column A from month February, then some kind of vlookup to return the Balance entry in the C column on the same row? –  Tim Nov 5 '11 at 7:03
    
Can you please add some example data? (Use 4 spaces to indent, to get fixed-width output.) Like: can a single date be present more than once? Are the dates really dates, or text? Could there be months without any data? –  Arjan Nov 6 '11 at 20:26
show 2 more comments

1 Answer

up vote 2 down vote accepted

Assuming your data is sorted in date accending order, you can use VLOOKUP for this

For conveniance name the account data range columns A:C, say AccountData.
On the summary sheet put the Date you want the balance for in one cell (say A2) and this formula in B2

=VLOOKUP(A2,AccountData,3,1)

To make getting the dates easy, use this formula in cell A3 and copy down (gets the last day of each month)

=DATE(YEAR(A2),MONTH(A2)+2,1)-1
share|improve this answer
    
Close, but what if some month(s) have no source data (yet)? And I think that the above requires that each date is listed in the source data only once (at most), which might not be true if there's a row for each bank transaction. (Also, the dates in the source data might actually be text, but that detail is lacking in the question too.) –  Arjan Nov 6 '11 at 20:24
    
@Arjan If no transactions exist for any given month then this still works: the balance returned is that of the previous month (which since no transactions exist is also the balance for the requested month). Still works if more than one transaction exists for a month (on same or different dates in the month) as it returns the balance for the highest numbered day. As I said the only constraint is that dates need to be sorted. If dates are strings rather than serials thats a bad design and should be fixed. –  chris neilsen Nov 6 '11 at 21:47
    
Ah, of course: the balance of the previous month should suffice. Still then: if multiple rows exist for the same day, the above will fetch the first of those, right? (But as we're not sure that can even happen, despite the question referring to some bank statement, this is the simplest solution without any array formulas. Nice.) –  Arjan Nov 6 '11 at 22:09
1  
@arjan, actualy no, vlookup with with range_lookup parameter set to 1 returns the index of the last value <= Lookup_Value which in this case is the final entry for the month, exactly what is required –  chris neilsen Nov 6 '11 at 22:35
    
Reading the documentation again I think you're right. The perfect answer then, too bad I cannot upvote twice ;-) –  Arjan Nov 9 '11 at 18:39
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.