Combine multiple cells into single expression - common issue?

[ahamilton613]

The FVSCHEDULE formula requires a single range of cells with the series of interest rates.

However, I need to build this series of interest rates through a dynamic formula.

Therefore I need a way to combine "[Formula 1]+[Formula 2]+[Formula 3]..." into a single range, or an expression that excel recognises in place of a single cell/range in the FVSCHEDULE formula (I have this issue quite a lot with different formulas).

I'm sure there is a simple solution, but the obvious solutions of arrays, conculcate, "&" etc didn't do it for me.

I don't think you need the full story, but if you are interested - I have a different interest rate for each year, I am trying to inflate a number but the start and finish date of the inflation period vary.

 

[NiMip]

Can you provide an example of your data? You can effectively join individual arrays using the CHOOSE function, but I think they have to be all the same size, which could be an issue. I can't really tell if it will work without seeing your spreadsheet

 

[NiMip]

Or if you're just looking to pull a range of values out of the middle of a bigger range, then INDIRECT might be the way to go, but your description of combining formulas to get a single range sounds more like you want to stack arrays?

 

[ahamilton613]

Or if you're just looking to pull a range of values out of the middle of a bigger range, then INDIRECT might be the way to go, but your description of combining formulas to get a single range sounds more like you want to stack arrays?

Hi,

Thank you for taking the time to respond.

Whilst this is a common issue for me, this specific issue is as follows:

• A354:A365 is a list of years (ascending order)
• C354:C365 is a list of corresponding inflation rates
• E174 is the start date for inflation
• Eomonth(O174,M174/2) is the end date.
• Inflation could be anything from c. 0 - 7 years

So I am looking for a formula which says FVSHEDULE([present value],lookup of appropriate dates within C354:C365 expressed as a range). I have a workaround below, but it is very cumbersome and I think there must be a more elegant solution to what I expect is a common issue on many formulas.

I don't think an indirect will produce a range expression - as you say, I don't know what an array within an array is - sounds very cool though!!

My workaround:

=[Present Value]*
((1+INDEX(C$354:C$365,MATCH(YEAR(E$182),$A$354:$A$365,0)))^((12-MONTH(E$182))/12))* [This calculates year 1 inflation, accounting for number of months in year]
(1+IFNA(INDEX(C$354:C$365,MATCH(((YEAR(E$182)+1)*((YEAR(E$182)+1)<YEAR(EOMONTH(O174,M174/2)))),$A$354:$A$365,0)),0))* [This calculates full year 2 if period is 3 yrs+]
(1+IFNA(INDEX(C$354:C$365,MATCH(((YEAR(E$182)+2)*((YEAR(E$182)+2)<YEAR(EOMONTH(O174,M174/2)))),$A$354:$A$365,0)),0))* [This calculates full year 3 if period is 4 yrs+]
(1+IFNA(INDEX(C$354:C$365,MATCH(((YEAR(E$182)+3)*((YEAR(E$182)+3)<YEAR(EOMONTH(O174,M174/2)))),$A$354:$A$365,0)),0))* [This calculates full year 4 if period is 5 yrs+]
MAX(1,((YEAR(EOMONTH(O174,M174/2))>YEAR(E$182))*(1+INDEX(C$354:C$365,MATCH(YEAR(EOMONTH(O174,M174/2)),$A$354:$A$365,0)))^((12-MONTH(EOMONTH(O174,M174/2)))/12))) [Final year (if period is across 2 calendar yrs), accounting for number of months in year]

 

[NiMip]

By "pull a range of values out of the middle of a bigger range", I just mean finding the start and end positions within a range of values using the MATCH function, and bringing it all together using INDIRECT. Does this work? Note that I've named a cell "Present_Value" to test this formula, so you'll need to replace that with something of your own!

=FVSCHEDULE(Present_Value,INDIRECT("C"&ROW($A$354)+MATCH(YEAR($E$174),$A$354:$A$365,0)-1&":C"&ROW($A$354)+MATCH(YEAR(EOMONTH($O$174,$M$174/2)),$A$354:$A$365,0)-1))


EDIT: Actually, this is probably a bit cleaner:

=FVSCHEDULE(Present_Value,OFFSET($C$354,MATCH(YEAR($E$174),$A$354:$A$365,0)-1,,YEAR(EOMONTH($O$174,$M$174/2))-YEAR($E$174)+1))

 

[ahamilton613]

THank you very much for clarifying - I'll have a think about your solution - sounds really good!