Excel 2007 Function, last three items
2014-03
Duall
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?
Ellesa
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.
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)
Jonno_FTW
Is there a way to quickly fill down a column of functions. For example, if I change my function from
=3*B2
And then fill down so it does it for a lot of rows.
But then change this column to:
=10*B2
Is there a way to quickly update all my functions in the column?
Craig
Update your formula in the first cell. Position you mouse over the bottom-right hand corner of the cell, the cursor which change from a white-cross to a black-cross. Double-click and your range will auto-fill with your new formula.
Lance Roberts
Drag and Fill Again.
If this is a common occurrence you can used Named Constants for the constant value, so you can change it easy.
David Basarab
You can do a copy and replace.
So highlight the cells you want to change, and do a Ctrl-F and replace the value according.
However this might have side effects you might not desire.
For example lets say you want to change 3 to 10, then you might actually change cell B3 to B10, B30 to B100 etc. . .
Your best bet is to make a constant by having all the values represented in another cell.
For exmaple A1 then your formula will look like
$A$1 * B1
$A$1 * B2
$A$1 * B3
etc.. .
Then you just change A1 and all your forumlas update accordingly.
($ means don't change the cell either column or row after when I do a drag or fill. In this example A and 1 do not change.)