Cell reference within array
 

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?

Cell reference within array
 

On May 4, 3:53*pm, 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)

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

Ideas?

Nothing?

Cell reference within array
 

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

wrote:

On May 4, 3:53 pm, 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)

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

Cell reference within array
 

Cell reference within array
 

Glenn wrote:
wrote:
On May 4, 3:53 pm, 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)

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

Ideas?

Nothing?


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,Sh eet1!$A$1

Then use this:

=IRR(ARRAY)

Cell reference within array
 

On May 5, 12:23*pm, Glenn wrote:
wrote:
On May 4, 3:53 pm, 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)

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

Ideas?

Nothing?

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.

Cell reference within array
 

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?



wrote:

On May 5, 12:23 pm, Glenn wrote:
wrote:
On May 4, 3:53 pm, 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)

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

Ideas?

Nothing?

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

Cell reference within array
 

On May 5, 5:31*pm, Dave Peterson wrote:
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?



wrote:

On May 5, 12:23 pm, Glenn wrote:
wrote:
On May 4, 3:53 pm, 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)

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

Ideas?

Nothing?

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

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

Cell reference within array
 

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

Cell reference within array
 

Harlan Grove wrote:
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))


Nice!

Cell reference within array
 

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


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

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

Ideas?