CODEWITHSUNDEEP
excelexcel-formula
user2707702
user2707702

Reputation: 45

Excel IRR Formula with non-contiguous ranges on a different sheet

I just realized that you can reference non-contiguous ranges in an IRR excel formula by enclosing a collection of references in parentheses (i.e. =IRR( (C18:C62,$B$1) ). This is a useful feature but, from various attempts, I have concluded that it does not work when a range reference includes another sheet/workbook or when a range is constructed with an offset function. Does anyone know of a workaround for this or another way to approach the problem?

Thank you for all your help!

Upvotes: 1

Views: 7337

Answers (2)

Todd
Todd

Reputation: 1

The best way I found to add multiple ranges in IRR is to add brackets around the multiple ranges. With the brackets it seems to recognize the multiple ranges. Example is shown below:

=IRR( **(** B27:F27,Z27:AB27 **)**, 10%)

Upvotes: 0

Mrig
Mrig

Reputation: 11702

IRR Function:

Sytax : IRR(values, [guess])

Assuming data is in Range A1:A5 and Range B1 for calculating IRR.

Being obvious =IRR(A1:A5) will give correct result. But =IRR(A1:A5,B1) makes excel assume that B1 is [guess] argument of the formula as syntax states and hence gives result same as for =IRR(A1:A5). Now if you add another range in formula like =IRR(A1:A5,B1,C1), excel will give error stating too many arguments entered. Thus, for non-contiguous ranges, all the ranges needs to be enclosed in parenthesis as =IRR((A1:A5,B1)) (as you mentioned in the question). This makes Excel to interpret it as a single argument consisting of 2 distinct ranges.

Using Offset in IRR.

it does not work when a range is constructed with an offset function

Well, IRR does support OFFSET function.

=IRR(OFFSET(A1,0,0,5)) is same as =IRR(A1:A5)

=IRR((OFFSET(A1,0,0,5),OFFSET(A1,0,1))) is same as =IRR((A1:A5,B1))

Above two formulas using Offset will give you correct results.

Using ranges from multiple sheets in IRR

it does not work when a range reference includes another sheet/workbook

This is TRUE when you are using different worksheets in one formula. However, if you are referencing range from only one worksheet and is different from current worksheet (one in which you are entering formula) then IRR function will work. That means =IRR(Sheet2!A1:A5) will give desired result even if formula is entered in Sheet1. But, IRR does not allows to use ranges from multiple worksheets in one formula. Hence, =IRR((A1:A5,Sheet2!B1)) is incorrect if entered in Sheet1.

So to use multiple ranges from different sheets you can use UDF(User Defined Function) in VBA. Following might help.

Function IRR_DEMO(rng1 As Range, ParamArray rng2() As Variant) As Double
    Dim elem As Variant
    Dim i As Long, cntr As Long
    Dim arr() As Double

    ReDim arr(1 To rng1.count) As Double    'set size of arr as per rng1
    cntr = 1

    For Each elem In rng1                   'first range
        arr(cntr) = elem.Value              'put range values in array
        cntr = cntr + 1
    Next elem
    For i = LBound(rng2) To UBound(rng2)    'all the ranges apart from first range and in rng2
        ReDim Preserve arr(1 To UBound(arr) + rng2(i).count) As Double 'reset size of arr as per rng2(i)
        For Each elem In rng2(i)
            arr(cntr) = elem.Value          'put range values in array
            cntr = cntr + 1
        Next elem
    Next i
    IRR_DEMO = IRR(arr)                     'use array in IRR
End Function

You can use above UDF as

=IRR_DEMO(A1:A5)
=IRR_DEMO(A1:A5,B1)
=IRR_DEMO(OFFSET(A1,0,0,5),OFFSET(A1,0,1))
=IRR_DEMO(Sheet2!A1:A5)
=IRR_DEMO(A1:A5,Sheet2!B1)
=IRR_DEMO(Sheet2!A1:A5,Sheet2!B1)

See images for reference.

Sheet1

enter image description here

Sheet2

enter image description here

Upvotes: 2

Related Questions