 # Name range in formula

English isn't my first language so sorry in advanced.
Hi, I'm using Excel 2016. My teacher use the name range in NPV formula but somehow I can't use it at all.
In details: my teacher name 2 range, each has 15 values, "benefit" and "cost" respectively. Then she use this exact formula "npv(rate,benefit-cost)" and the result was right. But when I use it the formula only calculate the first value in the string of value.
Therefore I can't use formula like "npv(rate, benefit*(1-%change)-cost)" like she did to evaluate changes.
I have attach photos example, unfortunately I dont have photos of my teacher's work
Can anyone explain to me why and how I can fix it, much appreciate.
5 Replies

# Re: Name range in formula

Instead of images, is it possible for you to post the actual spreadsheet you have? My suspicion is that you haven't actually named the range but have just put a heading up there....but without the spreadsheet itself one can only guess.

# Re: Name range in formula

Here's an example

# Re: Name range in formula

I think what you were missing is the absolute reference for the Rate portion of the formula. I didn't check the results but see if this works.

=NPV(\$B\$2,@benefit-@cost)

The "\$" dollar signs make each formula, all the way down the column, continue to refer to B2 even though the other references are changing.

# Re: Name range in formula

@mathetes thank for the advice, I tried it but sadly it didn't work.

The formula npv(rate, value1, value2,...) in itself only accept one "rate" so \$ is unnecessary, that why I'm convinced the problem lied in name range.

# Re: Name range in formula

You were right. I hadn't used NPV before. THis morning I looked at this resource and learned how it's to be used. You would benefit from the same. https://exceljet.net/excel-functions/excel-npv-function

The way to name that range is to highlight cells D4 through D13 and then use "Insert....Name...Define" from the main menu.

The resulting formula then can read =NPV(B2,ben_cost)  or it could just as effectively read =NPV(B2,D4:D13)

I highly recommend reading that ExcelJet reference, however. It explains well the limitations of the NPV function as well as showing how to use it.