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

#### arod

##### Board Regular
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?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try:
=NPV(2,OFFSET(B32,,,,B40,))

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.

Replies
6
Views
524
Replies
8
Views
1K
Replies
2
Views
271
Replies
12
Views
3K
Replies
7
Views
6K

1,196,297
Messages
6,014,525
Members
441,826
Latest member
roudarreza

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back