Forum Discussion

Copper Contributor

Nov 02, 2020

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, e...

excel

mathetes

Silver Contributor

Nov 02, 2020

MyLe_jam

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.

MyLe_jam
Copper Contributor
Nov 03, 2020
mathetes thank you for answer.
Here's an example
example.xlsx9 KB
- mathetes
  Silver Contributor
  Nov 03, 2020
  MyLe_jam
  
  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.
  example_Absolute.xlsx9 KB
  - MyLe_jam
    Copper Contributor
    Nov 03, 2020
    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.