Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating INDEX function into Array Formula (CNTRL-SHIFT-ENTER
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 D16:D23. 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,MATC H(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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating INDEX function into Array Formula (CNTRL-SHIFT-ENTER
=SUMPRODUCT((D16:D23)*(1+INDEX($B$27:$B$29,MATCH(F 16,$A$27:$A$29,0),1))^((B$1-E16)/365))
"ExcelMonkey" wrote: 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 D16:D23. 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,MATC H(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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating INDEX function into Array Formula (CNTRL-SHIFT-E
That doesn't work correctly. . The reason I am doing this is that D16:D23
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 "Teethless mama" wrote: =SUMPRODUCT((D16:D23)*(1+INDEX($B$27:$B$29,MATCH(F 16,$A$27:$A$29,0),1))^((B$1-E16)/365)) "ExcelMonkey" wrote: 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 D16:D23. 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,MATC H(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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating INDEX function into Array Formula (CNTRL-SHIFT-E
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((D16:D23),(1+LOOKUP(F16:F23,$A$27:$B$2 9))^((B$1-E16:E23)/365)) And this works! Thanks EM "ExcelMonkey" wrote: That doesn't work correctly. . The reason I am doing this is that D16:D23 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 "Teethless mama" wrote: =SUMPRODUCT((D16:D23)*(1+INDEX($B$27:$B$29,MATCH(F 16,$A$27:$A$29,0),1))^((B$1-E16)/365)) "ExcelMonkey" wrote: 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 D16:D23. 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,MATC H(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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Incorporating INDEX function into Array Formula (CNTRL-SHIFT-E
Hi excel monkey,
you have an interesting <finance post. try this =SUM((D16:D23)*(1+(B$1E16:E23)*LOOKUP(F16:F23,$A$ 27:$B$29))^((B$1- E16:E23)*(B$1E16: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 D16:D23 as calculated MAYBE decreasing as per the formula <pls. verify your original formula by tweaking the dates -- ***** birds of the same feather flock together.. "ExcelMonkey" wrote: 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((D16:D23),(1+LOOKUP(F16:F23,$A$27:$B$2 9))^((B$1-E16:E23)/365)) And this works! Thanks EM "ExcelMonkey" wrote: That doesn't work correctly. . The reason I am doing this is that D16:D23 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 "Teethless mama" wrote: =SUMPRODUCT((D16:D23)*(1+INDEX($B$27:$B$29,MATCH(F 16,$A$27:$A$29,0),1))^((B$1-E16)/365)) "ExcelMonkey" wrote: 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 D16:D23. 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,MATC H(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List of functions contained in the add-ins, esp. Analysis Toolpak | Excel Worksheet Functions | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Proper way to enter array formula | Excel Worksheet Functions | |||
how to enter a formula using column() function for a range | Excel Worksheet Functions |