Cell reference within array

  • Thread starter Thread starter davidbr55
  • Start date Start date
D

davidbr55

I am trying to use a cell reference within an array, but Excel won't
let me.

In my case, here is the formula I want: =IRR(A8,50,50,50,50)

A8 is a negative number that will change by row: the next line would
read =IRR(A9,50,50,50,50)

Ideas?
 
I am trying to use a cell reference within an array, but Excel won't
let me.

In my case, here is the formula I want: =IRR(A8,50,50,50,50)

A8 is a negative number that will change by row: the next line would
read =IRR(A9,50,50,50,50)

Ideas?
Click to expand...

Nothing?
 
Why not share the real formula and describe what you're doing and how it fails?
 
Glenn said:
Put 50 in B8:E8 (you can hide those columns) and use this:

=IRR(A8:E8)
Click to expand...

Or, put 50 in cell A1. Select cell A8, then Insert / Name / Define "ARRAY" as
follows:

=Sheet1!$A8,Sheet1!$A$1,Sheet1!$A$1,Sheet1!$A$1,Sheet1!$A$1

Then use this:

=IRR(ARRAY)
 
Put 50 in B8:E8 (you can hide those columns) and use this:

=IRR(A8:E8)
Click to expand...

Glenn,

Thanks, That is indeed what I ended up doing and it solved the
problem. I was looking for a more elegant solution, though.

Dave, that was a weird comment. This is the real formula. Maybe I
forgot the brackets, but basically cell A8 would change and the
formula would ideally read =IRR({$A8,50,50,50,50},0.15)

Any more thoughts are welcome.

Thanks again.
 
I pasted your original formula and the new formula into the formulabar and
xl2003 yelled at me saying that each contained an error.

What formula worked for you?
What cell did you place the formula?
 
I pasted your original formula and the new formula into the formulabar and
xl2003 yelled at me saying that each contained an error.

What formula worked for you?
What cell did you place the formula?
Click to expand...

The formula that would work is =IRR({-40,50,50,50,50},0.15)

The only difference is that I want -40 to be A8.

The formula may change cells, but is is on C8 for now. I don't think
that matters, though
 
(e-mail address removed) wrote...
....
I am trying to use a cell reference within an array, but Excel won't
let me.

In my case, here is the formula I want: =IRR(A8,50,50,50,50)
Click to expand...
....

Excel prohibits this because IRR takes a single argument, but you're
trying to pass 5 arguments.

IRR accepts array first arguments, so make this an array.

=IRR(IF({1;0;0;0;0},A8,50))
 
Harlan said:
(e-mail address removed) wrote...
...
...

Excel prohibits this because IRR takes a single argument, but you're
trying to pass 5 arguments.

IRR accepts array first arguments, so make this an array.

=IRR(IF({1;0;0;0;0},A8,50))
Click to expand...


Nice!
 
One more option (similar to Glenn's without the name) if B8=50:
=IRR((A8,B8,B8,B8,B8))

[Maybe of interest: if({1},A1:A2) is different from if(1,A1:A2),
the first converts the reference to an array e.g. try N() on both.]
 
Back
Top