Using formula over a range of cells with the end cell a variable

[arod]

I'm trying to create a formula that calculate the NPV from a given cell reference along a row for x number of cells.

ie. my formula is =NPV(2,B32:x32)

The number of cells I want to calculate it for (x) is contained in B40.

ie. if I have the number 10 in B40 I want to calculate the NPV from B32:K32 (10 columns along from B32).

I've fiddled with the indirect, match, address formulas with no luck so far.

Are there any suggestions how to approach this?

 

[c_m]

Try:
=NPV(2,OFFSET(B32,,,,B40,))

 

[arod]

I was wondering if I can get a bit of help using the NPV formula again.

In cells B3:V3 I have the numbers 0-20, representing time periods.

In cells B5:V5 I have the values I want to calculate the NPV for.

I cell B1 I have the a value between 1-20 that I want to start my NPV calculation from corresponding to the value in the range B5:V5. in cell C1 I have the value between 1-20 that I want the NPV calculation to end from.

ie. if I have '1' in B1 and '5' in C1, the formula will match these values in the range B3:V3 and calculate the NPV for the corresponding values in B5:C5.

The npv formula will be something like =npv(discount rate,match the value in B1 to value in range B3:V3 and use the correspondong cell reference in range B5:V5 : match the value in C1 to value in range B3:V3 and use the correspondong cell reference in range B5:V5)

I can't figure out how to do this myself-if anyone can help that'd be much appreciated.