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.

My question is twofold, so bear with the wall of text. I'm making sort of a banking spreadsheet. I will input income/expenses in four columns (Date/Amount/Type/Description) and I need it to keep track of my day to day spending. I already have it so that if the date is not today, it won't add/subtract it until it is. Also, I have it so two/three/four weeks in the future. However, I want to add something to the effect of "Last three paychecks". The "Type" column has only three possible entries, "Income", "Transfer", and "Expense". If I can find a function to work for one, I'm sure I can get it to work with the rest easily. I need it to show the most recent "income" amount. For instance:

Date Amount Type

Sep 1 100 Income

Sep 2 100 Expense

Sep 3 100 Income

Sep 4 100 Expense

Sep 5 100 Income

Sep 6 100 Income

Sep 7 100 Income

Let's say it's Sep 6th today. I would want it to show Sep 5ths amount, Sep 5ths, and Sep 3rds. I don't want it to show expenses, and it's not yet Sep 7th. It would have to be three functions (one for each box), so how would I get it to do the most recent, second to most recent, and so on? If I can get it to work, I can edit to get the description as well, and refit it to expenses if need be.

Question 2:

I would like an "annual checkup" kind of thing. How would I get it to lookup each of every type (same as above), but then have cutoff dates so it's only one year?

share|improve this question
    
Do you want to filter the data table too? Or do you just need the subtotal for the most recent entries? –  Ellesa Sep 15 '11 at 8:42
    
Not even the subtotal, I want it to show the latest income entry in one cell, the next latest in another, and the next latest in another. –  Duall Sep 15 '11 at 22:25

1 Answer 1

up vote 2 down vote accepted

This might help you get the three most recent transactions per type. It helps if you've used named ranges for your data sheet.

To get the 3 most recent Income dates, you can use this array formula:

=INDEX(dates,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

Where:

dates -- the cell range/column range that contains the dates
types -- the cell range/column that contains either "Income", "Transfer" or "Expenses"
XXX -- contains either 1 (most recent), 2 (2nd most recent) or 3.

To get the most recent amount and description, replace the first instance of dates in the formula above with the cell range that contains the money amounts and descriptions.

=INDEX(amounts,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

&

=INDEX(descriptions,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

In the example below, dates refers to A2:A100, amounts refers to B2:B100, types refers to C2:C100 and descriptions refers to D2:D100.

enter image description here

In order for the formulas to work, the data table needs to be sorted according to date (oldest to latest). Be sure to commit the formulas using Ctrl + Shift + Enter. This also assumes that there are unique dates for each type of transaction (i.e. no multiple Income entries for a single date).


If you don't want to have to sort the table by date, here's an array formula you can try:

=INDEX(dates,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

Where:

dates -- the cell range/column range that contains the dates
types -- the cell range/column that contains either "Income", "Transfer" or "Expenses"
XXX -- contains either 1 (most recent), 2 (2nd most recent) or 3

Again, to get the most recent amounts and descriptions, replace the first instance of dates in the formula above with the cell range that contains the money amounts and descriptions. Something like this:

=INDEX(amounts,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

and

=INDEX(descriptions,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)
share|improve this answer
    
Extremely well written and well explained answer! Another couple for you though. Is there a reason it requires doing the ctrl/shift/enter keys vs. every other formula? Also, is there a way to do A:A without including the title? –  Duall Sep 20 '11 at 7:34
    
Thanks Duall! Yes, you can replace the names with exact cell references; in my example above, "dates" actually refers to "A2:A100". The named range just makes it easier to type the formulas in Excel. –  Ellesa Sep 20 '11 at 8:30
1  
Oh, array formulas need to be Ctrl+Shift+Entered in order to work. This page explains it better than I can: cpearson.com/excel/ArrayFormulas.aspx –  Ellesa Sep 20 '11 at 8:39
    
I have encountered a problem with this. If I have three expenses on the same day, it will take the first expense of that day and repeat it three times. Other than that it works wonders. –  Duall Sep 23 '11 at 4:35
    
Yep, I mentioned that in the answer -- I based it on your example and assumed the dates were unique. I'll update my answer as soon as I get off work. :) –  Ellesa Sep 23 '11 at 8:52

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.