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:
IRR(values,guess)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
