how to create an array within Excel formula?

Question

I want to calculate IRR where I have 3 sequential inputs: initial value, a series of payments, a final value. The number of payments is variable and therefore, I want to create a dynamic array within the formula which IRR can take as input. I wrote a formula below but the result is sort of unexpected:

Data:
A
-----
1  -15
2   10
3   10
4   10
5    5

{=IF( ROW(1:5)=1 , IF(ROW(1:1),$A$1,"") , IF(ROW(1:4)<4,$A$2:$A$4,$A$5))}


This should result in {-15,10,10,10,5}
However, this results in {-15,10,10,5,#N/A}

What is wrong here? Or is there an easier/better way?

That gives {-15;10;10;#N/A;#N/A}. Idea with ROW(1:4)<4 is to get 3 values from A2:A4 and 4th from A5 — dsauce
– dsauce, Commented Feb 12, 2018 at 19:24

Scott Craner · Accepted Answer · 2018-02-12 19:38:32Z

1

For your formula, the rows() need to be the same:

=IF(ROW(1:5)=1 , $A$1, IF(ROW(1:5)<=4,$A$2,$A$5))

To make it so the number of payment is dynamic, try this array formula:

=IRR(IF(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,B4+2))=1,B2,IF(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,B4+2))<B4+2,B3,B5)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

edited Feb 12, 2018 at 19:38

answered Feb 12, 2018 at 19:29

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dsauce Over a year ago

great, thanks. I had a plan to make payments repeat in my formula using ROW(1:N) * payment / ROW(1:N) where N is number of payments. But after your modification of my formula i don't need to do this as well. I could use ROW(INDIRECT("1:" & N)) to make ROW() dynamic. However, could you explain what exactly INDEX(AAA:AAA,1):INDEX(AAA:AAA,B4+2) is doing?

Scott Craner Over a year ago

@dsauce INDIRECT is volatile and will make the formula recalc every time Excel recalcs whether the underlying data changes or not. But using INDEX instead, now it will on recalc if the data in the reference ranges changes. Specifically, A2:A5 and Column AAA:AAA. I chose that column as it is a column so far out that that the chance of having data that changes regularly on that column is slim. Other wise it does the same as the INDIRECT. I just avoid Volatile Functions when ever possible.

dsauce Over a year ago

Valid point. i will use Index as well then, have a huge table to fill. Thanks again.

Collectives™ on Stack Overflow

how to create an array within Excel formula?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related