Name range in formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1844964%22%20slang%3D%22en-US%22%3EName%20range%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1844964%22%20slang%3D%22en-US%22%3EEnglish%20isn't%20my%20first%20language%20so%20sorry%20in%20advanced.%3CBR%20%2F%3EHi%2C%20I'm%20using%20Excel%202016.%20My%20teacher%20use%20the%20name%20range%20in%20NPV%20formula%20but%20somehow%20I%20can't%20use%20it%20at%20all.%3CBR%20%2F%3EIn%20details%3A%20my%20teacher%20name%202%20range%2C%20each%20has%2015%20values%2C%20%22benefit%22%20and%20%22cost%22%20respectively.%20Then%20she%20use%20this%20exact%20formula%20%22npv(rate%2Cbenefit-cost)%22%20and%20the%20result%20was%20right.%20But%20when%20I%20use%20it%20the%20formula%20only%20calculate%20the%20first%20value%20in%20the%20string%20of%20value.%3CBR%20%2F%3ETherefore%20I%20can't%20use%20formula%20like%20%22npv(rate%2C%20benefit*(1-%25change)-cost)%22%20like%20she%20did%20to%20evaluate%20changes.%3CBR%20%2F%3EI%20have%20attach%20photos%20example%2C%20unfortunately%20I%20dont%20have%20photos%20of%20my%20teacher's%20work%3CBR%20%2F%3ECan%20anyone%20explain%20to%20me%20why%20and%20how%20I%20can%20fix%20it%2C%20much%20appreciate.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1844964%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846335%22%20slang%3D%22en-US%22%3ERe%3A%20Name%20range%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F854659%22%20target%3D%22_blank%22%3E%40MyLe_jam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20images%2C%20is%20it%20possible%20for%20you%20to%20post%20the%20actual%20spreadsheet%20you%20have%3F%20My%20suspicion%20is%20that%20you%20haven't%20actually%20named%20the%20range%20but%20have%20just%20put%20a%20heading%20up%20there....but%20without%20the%20spreadsheet%20itself%20one%20can%20only%20guess.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1847197%22%20slang%3D%22en-US%22%3ERe%3A%20Name%20range%20in%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1847197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20answer.%3C%2FP%3E%3CP%3EHere's%20an%20example%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
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

@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.

@mathetes thank you for answer.

Here's an example

@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.

@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.

 

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.