Offset function for cashflows in different years

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
Hi,

I have got three period like this


Period: 2010 2015 2018
Cash: 100 200 300

I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...

The three periods described above will be changeable, so the cashflows will adjust accordingly...

can anyone help with this?

cheers
andy
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Maybe someone else knows what you mean but what does "put these cashflows on a timeline" mean? They have associated periods. That defines the timeline. So, what more processing does it involve?
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
The timeline is in years: every column is a year

2010, 2011, 2012, 2013, 2014 and so on

whereas the three periods span a number of years

i.e. in the above example the first period is years: 2010-2014

The function should automatically put "100" for each year 2010,2011,2012,2013,2014 and "200" for 2015 onwards...and so on

I hope this makes sense.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Pretend you don't have a context for your problem -- none of the readers do. Now, re-read your post. What does one do with "and so on?" How does one know that 100 for 2010-2014 becomes 2015 onwards? Where are these period (year) values? Where does one put the 100? Or 200? And, so on.
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926

ADVERTISEMENT

Book2
ABCDEFGHIJKLMNOPQRST
1Period1StartsPeriod2StartsPeriod3Starts
2Year201020152018201020112012201320142015201620172018201920202021202220232024
3Amount100200300100100100100100200200200300300300300300300300
4
5
Sheet1
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
yellow cells are the only inputs... everything else should be formula driven... does it make sense ?
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

F3 contains =HLOOKUP(F2,$B$2:$D$3,2,TRUE)
Copy F3 across row 3 as required.
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
thanks but there should be a unique formula doing that for all periods... i.e. a formula which can tell when the next period starts...i was thinking of OFFSET somehow...
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Hopefully, someone else knows how to figure out what you mean from what you write. I will stop monitoring this topic.
 

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
926
please don't .. the function you suggest is correct but you said "copy as required".

I would like a more powerful function which will be copied across columns F to T which does what your function describes but considers the fact that you have 2 period changes...

it can't be more clear than that .. please someone tell me that you understand what i am talking about !
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,872
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top