Varying a cell reference in a formula

I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell.

For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50)
So far, so good.

However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).

Any suggestions?
Thanks!

Try =IRR(offset(C50, 0, 0, 1, b2)

A simple sol'n Using a helper cell, say H1, let H1=

Please Login or Register  to view this content.

to build the actual range of cells to evaluate, then use

Please Login or Register  to view this content.

If you need enough of these that is slows your workbook down, it could be changed to use INDEX (which is not volatile) instead of OFFSET (which is).