Is there a NVL type function for excel?

What I am trying to accomplish is a fomula that will give an IRR value
if exists, but if IRR produces an #DIV/0!, return "N/A".

Right now I have the formula ...

=IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeri ods),0.01)*12),"N/A",IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0. 01)*12)

Which works fine, but it would be nice if I did not have to copy the
IRR part of the equation twice.

Thanks,

Jonathan

Will this do?

First, define the following...

Insert Name Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Secondly, custom format the cell as follows...

Format Cells Number Custom Type:

[=0]"N/A"

Then, try the following formula...

=LOOKUP(BigNum,CHOOSE({1,2},0,IRR(OFFSET(G10,0,Beg inOffset,1,NumOfPeriods
),0.01)*12))

Hope this helps!

In article .com,
"Jonathan" wrote:

Is there a NVL type function for excel?

What I am trying to accomplish is a fomula that will give an IRR value
if exists, but if IRR produces an #DIV/0!, return "N/A".

Right now I have the formula ...

=IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeri ods),0.01)*12),"N/A",IRR(OFF
SET(G10,0,BeginOffset,1,NumOfPeriods),0.01)*12)

Which works fine, but it would be nice if I did not have to copy the
IRR part of the equation twice.

Thanks,

Jonathan

Interesting approach, thanks.

-- jt

Domenic wrote...
....
=LOOKUP(BigNum,CHOOSE({1,2},0,
IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0.01 )*12))
....

If the #N/A error value would be acceptable, this could be shortened to

=LOOKUP(BigNum,IRR(OFFSET(G10,0,BeginOffset,1,NumO fPeriods),0.01)*12*{0;1})

Note: the effective annual interest rate derived by IRR from monthly
cashflows should be calculated as (1+IRR(..))^12-1 rather than as
IRR(..)*12. Since IRRs can get large, this does matter unless you
really do want the nominal annual interest rate compounded monthly.

The *simplest* way to handle problems like this is to use an intermediate
cell to use the intermediate value. In your case that would be the result
of the IRR(). Suppose you put that in cell G12. Then, the 'final' formula
would be if(ISERR(G12),na(),G12).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Is there a NVL type function for excel?

What I am trying to accomplish is a fomula that will give an IRR value
if exists, but if IRR produces an #DIV/0!, return "N/A".

Right now I have the formula ...

=IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeri ods),0.01)*12),"N/A",IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0. 01)*12)

Which works fine, but it would be nice if I did not have to copy the
IRR part of the equation twice.

Thanks,

Jonathan

Thanks guys!