Add-ins:
Excel
PowerPoint

**From Paul on Sep. 01, 2011:**

Page Rating: 4

Site Rating: 4

This led me to try closing double brackets around a UDF that had discontinuous cells as input. It actually worked! Thanks.

There are some Excel functions that appear to need a single contiguous argument. IRR is one such function. From Excel help:

Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

It is the natural tendency to believe that the
first argument must be a single range. After all, if one were
to specify two ranges, IRR would interpret the 2nd as the optional
*guess* argument, wouldn't it?

The way around this is to enclose the range within a pair of parenthesis! This tells Excel that everything inside the parenthesis is part of a single argument. Whether the function is expecting a range that includes multiple areas is another story. An example where the technique works is the IRR function. Suppose there are three cash outflows followed by a single cash inflow. While there are better ways to organize the data, in this instance the layout is shown in Figure 1.

Formulas that don't work include:

=IRR(D2:F5,0.1), which yields the wrong result because it implies there are 8 cash flows (-9, 1, -10, 2, -11, 3, 4, and finally, 35.

=IRR(D2:D5,F2:F5,0.1), which cannot even be entered because Excel complains there are too many arguments.

=IRR(D2:D5,F2:F5), which yields the #VALUE! error since F2:F5 is interpreted as the 2nd argument, which is unacceptable for an argument that is supposed to be a single number.

Without reorganizing the data, the **
correct** way to use IRR would be =IRR((D2:D4,F5),0.1).
Because of the parenthesis around the dis-contiguous range, Excel
interprets it as a single argument consisting of 2 distinct ranges.
Clearly, the IRR function is OK with that since it returns the
correct result.

Is this a technique that always works? No, not necessarily. It only works if the function expects and handles a range consisting of multiple areas. One function that doesn't work is LINEST -- it insists on a single contiguous range. An argument consisting of a dis-contiguous range within parenthesis leads to a #REF! error.

The bottom line is that if you must provide a multiple area range to an argument that looks like it expects a single range, it might not hurt to try using parenthesis to enclose the dis-contiguous ranges. If it works -- and you should verify that the result is correct -- great. If not, you will have to reorganize the data into a form more acceptable to the function.

Keywords: Dis-contiguous contiguous continuous discontinuous Excel function IRR LINEST parenthesis multiple range