E
ExcelMonkey
I am using an index function to escaltate a value in real terms. For example:
RealValue * (1 + rate)^N = Nominal Value
Or as below
D16*(1+INDEX($B$27:$B$29,MATCH(F16,$A$27:$A$29,0),1))^((B$1-E16)/365)
Or better defined as:
Real*(1+INDEX(list of escalation
types,row,column)^((currentdate-basedate)/365days)
D16 is the real value and everything else is the escalation.
This works fine. Problem I am having is that I now want to incorporate this
into an array formula. So instead of trying to find the nominal value of
D16. I want to find the nominal values of D1623. I am attempting to do so
by incorporating the INDEX function into a SUMPRODUCT function. I was trying
to incorporate it as follows:
=SUMPRODUCT($D$16:$D$23,Escalation)
I tried making the following changes to the indexation formula from this:
(1+INDEX($B$27:$B$29,MATCH(F16,$A$27:$A$29,0),1))^((B$1-E16)/365)
to this
(1+INDEX($B$27:$B$29,MATCH(F16:F23,$A$27:$A$29,0),1))^((B$1-E16:E23)/365))
or as an array formula:
{=SUMPRODUCT($D$16:$D$23,(1+INDEX($B$27:$B$29,MATCH(F16:F23,$A$27:$A$29,0),1))^((B$1-E16:E23)/365))}
However this is not working. How do I do I incorporate an index formula
into this array formula?
Thanks
EM
RealValue * (1 + rate)^N = Nominal Value
Or as below
D16*(1+INDEX($B$27:$B$29,MATCH(F16,$A$27:$A$29,0),1))^((B$1-E16)/365)
Or better defined as:
Real*(1+INDEX(list of escalation
types,row,column)^((currentdate-basedate)/365days)
D16 is the real value and everything else is the escalation.
This works fine. Problem I am having is that I now want to incorporate this
into an array formula. So instead of trying to find the nominal value of
D16. I want to find the nominal values of D1623. I am attempting to do so
by incorporating the INDEX function into a SUMPRODUCT function. I was trying
to incorporate it as follows:
=SUMPRODUCT($D$16:$D$23,Escalation)
I tried making the following changes to the indexation formula from this:
(1+INDEX($B$27:$B$29,MATCH(F16,$A$27:$A$29,0),1))^((B$1-E16)/365)
to this
(1+INDEX($B$27:$B$29,MATCH(F16:F23,$A$27:$A$29,0),1))^((B$1-E16:E23)/365))
or as an array formula:
{=SUMPRODUCT($D$16:$D$23,(1+INDEX($B$27:$B$29,MATCH(F16:F23,$A$27:$A$29,0),1))^((B$1-E16:E23)/365))}
However this is not working. How do I do I incorporate an index formula
into this array formula?
Thanks
EM