Hello,
I wonder, can I use a cell reference in the formula FVSCHEDULE like
FVSCHEDULE(A1, {C1, C1, C1}) ?
Any help is much appreciated.
According to the documentation, inline arrays in formulas can only consist of constants.
To do what you request, you’d need a redirecting step:
- Have the function reference a cell range
- Each cell in that range has a reference to the cell you specified
Thanks for your posting.
I’m trying to digest your suggestion re. ‘reference to a cell range’.
Wouldn’t be the reference not violate the rules that only constants can be used?
Can you perhaps post an example?
TIA
Please attach a sample file, to make it easier to answer.
No.
- An inline array is only supported in formulas as a constant array.
- The function also accepts a cell range where you suggested the inline array. The cells within that range are not limited by the inline array constraint.
Note @Lupp’s answer if you really want the same value for all entries.
- If you have scheduled interest rates in an array as given by a CellRange of your sheet like
J1:J3, you can use=FVSCHEDULE(A1; J1:J3)and the referenceJ1:J3will pass three values to the function without the usage of an inline array. The three cells may all contain the same value gotten fromC1if each one contains the formula=$C$1. - If you actually have three times the same interest rate (contained in
C1in your example) to apply n times (n=3in your example,FVSCHEDULE()isn’t the appropriate function. Use the formula=A1 * (1+C1)^3in this case.
The formula ‘A1 * (1+C1)^3’ hit the nail on the head (as well as FVSCHEDULE(A1; J1:J3) matches).
My mistake was that I continued to use the ‘{}’ in my attempts.
Thanks all.
Problem solved.