Offset function for cashflows in different years

[neodjandre]

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

 

[tusharm]

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]

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]

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]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Period1Starts	Period2Starts	Period3Starts
2	Year	2010	2015	2018		2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024
3	Amount	100	200	300		100	100	100	100	100	200	200	200	300	300	300	300	300	300	300
4
5
Sheet1

 

[neodjandre]

yellow cells are the only inputs... everything else should be formula driven... does it make sense ?

 

[tusharm]

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

 

[neodjandre]

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]

Hopefully, someone else knows how to figure out what you mean from what you write. I will stop monitoring this topic.

 

[neodjandre]

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 !