Incorporating INDEX function into Array Formula (CNTRL-SHIFT-ENTER

[Guest]

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

 

[Guest]

=SUMPRODUCT((D1623)*(1+INDEX($B$27:$B$29,MATCH(F16,$A$27:$A$29,0),1))^((B$1-E16)/365))

 

[Guest]

That doesn't work correctly. . The reason I am doing this is that D1623
represents 7 separate costs items which have 1 of three esclation factors.
These three factors are epxressed in $B$27:$B$29. I want to be able to use
the index function to search out this variable for all 7 cost items and then
escalate these 7 items and add them together in the one formula. This is why
I am using the SUMPRODUCT. As written, the index applies one rate to all
seven numbers. I in fact have three separate rates which do not get applied
using this methodology.

EM

 

[Guest]

Actually I replaced the index with an HLOOKUP. So a simple HLOOKUP would
like this:

=D16*(1+LOOKUP(F16,$A$27:$B$29))^((B$1-E16)/365)

If you wrap it in a sumproduct and and array you get:

=SUMPRODUCT((D1623),(1+LOOKUP(F16:F23,$A$27:$B$29))^((B$1-E16:E23)/365))

And this works!

Thanks

EM

 

[Guest]

Hi excel monkey,

you have an interesting <finance> post.
try this
=SUM((D1623)*(1+(B$1>E16:E23)*LOOKUP(F16:F23,$A$27:$B$29))^((B$1-
E16:E23)*(B$1>E16:E23)/365))
press ctrl-shft-ent for the {}

reminder: if your base date :B$1 is earlier than any date in range E16:E23
your real money on D1623 as calculated MAYBE decreasing as per the formula
<pls. verify your original formula by tweaking the dates>