IRR from a range only?

[phstol]

Hello,
When using IRR in an income statement.
I can only get IRR if I transfer the -INV dollars to the cell immediately ahead of the Net Profit range of cells.
Is there a way to reference the -INV as a cell on a different line to avoid having the -INV in the NP range? i.e., like NPV works.
Help wd be much appreciated
PHSTOL

[lecxe]

Hi phstol

IRR accepts a multi-area range as parameter. Just enclose the list of the areas in parentheses and use the comma as separator.

Ex:

=IRR((C2,A3:A6))

with the -INV in C2 and the NP range A3:A6.

[phstol]

HI LECXE,
your solution works ..... I thought I had tried every combination of the above, but missed the correct one. Thanks vm for your suggestion.
Actually, I was trying to use (C2*-1) to avoid having a -ve in the INV cell.... if you know of a way to do that it wd make my spreadsheet look a little better .
Best , PHSTOL

[lecxe]

Hi

I'm glad it helped.

Actually, I was trying to use (C2*-1) to avoid having a -ve in the INV cell.... if you know of a way to do that it wd make my spreadsheet look a little better

For ex. 3 options:

- if you just want to make the spreadsheet look better, format the cell to display negative numbers as positive numbers. The value of the cell will still be negative but anyone looking at the worksheet sees it as positive. You must, however, remember always to enter the INV value as negative. In this case you don't have to change the formula.

- if you really want to enter the value of INV as positive, you can use a formula to generate an array where you change the sign of the INV value.

- you could also use vba and write a udf with the INV and NP ranges as input

The first option is the simplest.