Cell reference within array

[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?

 

[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?

Nothing?

 

[Dave Peterson]

Why not share the real formula and describe what you're doing and how it fails?

 

[Glenn]

Nothing?

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

=IRR(A8:E8)

 

[Glenn]

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

=IRR(A8:E8)

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)

 

[davidbr55]

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

=IRR(A8:E8)

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.

 

[Dave Peterson]

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?

 

[davidbr55]

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?

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

 

[Harlan Grove]

(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)

....

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))

 

[Glenn]

(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))

Nice!

 

[Lori Miller]

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.]