Adding Data That Lies Between Two Dates

B

Bazy2k

Hi guys.

I have a spreadsheet which details a list of fixed assets that mature over a
period of time. Now i need to know which items fall into which categories
without having to work it out manually!
An example of my spreadsheet is:

Item Acquisition Fully Dep'n Cost Current Depn WDA
x 01/05/06 30/04/11 300 250
50

I have the current date in cell J1 and the depreciation is calculated from
this, now imagine the list being very long full of different items fully
depreciating at different times.

I need to put the current WDA figure of each item into one of the following
groups dependant on when that item is fully depreciated:

0-3Months
3-6Months
6-12Months
1-5years
Over 5years

Please help! Thanks.
Tom.
 
P

Pete_UK

If you set up a table like this:

0 0-3Months
91 3-6Months
182 6-12Months
365 1-5years
1825 Over 5years

(assume this is in X1:Y5), then you can use ths formula:

=VLOOKUP(C2-B2,X$1:Y$5,2)

where C2 is the Fully Dep'n date and B2 is the Acquisition date, and
this will give you the group description. Copy the formula down as far
as required.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top