Combine multiple cells into single expression - common issue?

ahamilton613

New Member
Joined
Jan 10, 2018
Messages
3
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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?
 
Upvote 0
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]
 
Upvote 0
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))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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
Back
Top