How is calculation made without seeing the formula

[eawad]

I am not sure how this is done, im sure its simple but I cannot figure it out.. =nper is used in a cell or references to nper or @nper in formulas but no actually syntax for the nper function visible yet it still returns a value. can anyone point me in the right direction as to how this is done. This is used in a loan amortisation sheet. Any assistance would be greatly appreciated.

 

[Michael M]

you could go to the formula tab>>formula auditing>>trace precedents....to see where the connections to the Nper lie
OR
check to make sure it isn't run via a macro
But there MUST be a a set of variable attached to the NPER or it simply won't work

NPER Function

The Excel NPER function is a financial function that returns the number of periods for a loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount.

exceljet.net

 

[Michael M]

another thing to look at is.....if the sheet is protected, the formulas are probably hidden !

 

[eawad]

Thank you for the pointers.. I did check previously and the sheet is not protected. I was also not able to use trace precedents as its greyed out and cannot be used in this instance. I have a screen shot of how the formula looks like and I also dont think its run through a macro. very puzzling..

 

[Michael M]

Can you post the entire formula ....NOT a screenshot please

 

[Alex Blakenburg]

Just a random thought here but if you hit ctrl+F3 calling up the name manager, is in the list of range names ?

 

[eawad]

Thanks Alex, that is exactly where I found it, it was a saved name that held the formula. I really appreciate all your help with this puzzle.

 

[Alex Blakenburg]

No problem, thanks for the feedback and glad we could help.

 

[Michael M]

@Alex Blakenburg
I would have thought a formula used as a named range would be an illegal operation in Excel ??
AND
I wouldn't have used it as such anyway......that's asking for trouble...

 

[Alex Blakenburg]

@Michael M
The @ without [] is one of the things that made me think of it.
If the OP did not have 365 I would have thought that it might have been just a range not a formula but in 365 "=nper" should have spilled or tried to.
It would have been handy to see the actual range name screen / formula.
I do think using a range name that is the same name as in existing function is a particularly bad idea.

I have seen it done once before.
Jeff Lenning Excel University in his Senior's course reduced this,
=IFERROR(CHOOSE(VLOOKUP($I11,bud_methods,2,0),$H11/4,D11,(D11/'Exercise 2 Headcount Calcs'!C$37)*'Exercise 2 Headcount Calcs'!G$37,D11*(1-dec_rate),VLOOKUP($B11,bud_manual,MATCH(J$10,INDEX(bud_manual,1,0),0),0),0),0)

Down to this:
=@CHOOSE(@Method,Spread_PY,Match_PY,Headcount,Decrease,Manual,Zero)

Using the range names which include the following:

Name	Refers to
Headcount	=('Exercise 5 Calcs to Range Names'!XEY1/'Exercise 2 Headcount Calcs'!XEX$37)*'Exercise 2 Headcount Calcs'!XFB$37
Manual	=VLOOKUP('Exercise 5 Calcs to Range Names'!$B1,bud_manual,MATCH('Exercise 5 Calcs to Range Names'!A$19,INDEX(bud_manual,1,0),0),0)
Method	=VLOOKUP('Exercise 5 Calcs to Range Names'!$I1,bud_methods,2,0)

Not something I can really see myself using but is it really any different to using the new Lambda function.