Home

Correction

%3CLINGO-SUB%20id%3D%22lingo-sub-556629%22%20slang%3D%22en-US%22%3ECorrection%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-556629%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20meaning%20of%20%23VALUE!%20.%20In%20Financial%20Function%2C%20FV.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-556629%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560788%22%20slang%3D%22en-US%22%3ERe%3A%20Correction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339747%22%20target%3D%22_blank%22%3E%40Oji-C-1615%3C%2FA%3E%26nbsp%3B%2C%20FV%20returns%20%23VALUE!%20error%20if%20one%20of%20the%20arguments%20is%20not%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561143%22%20slang%3D%22en-US%22%3ERe%3A%20Correction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561143%22%20slang%3D%22en-US%22%3E%3CP%3EFV(%221%25%22%2C%2260%22%2C%22-100%22%2C%22-1000%22%2C%221%22)%20works%20just%20fine%2C%20despite%20the%20fact%20that%20the%20parameters%20are%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%40Oij-C%2C%20if%20you%20have%20a%20question%20about%20your%20use%20of%20an%20Excel%20function%2C%20it%20behooves%20you%20to%20show%20us%20how%20you%20are%20using%20the%20function%20(copy%20from%20the%20Formula%20Bar)%2C%20as%20well%20as%20any%20cell%20values%20that%20you%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20%23VALUE%20error%20might%20indicate%20that%20a%20parameter%20is%20or%20references%20text%20that%20Excel%20does%20not%20recognize%20as%20numeric.%26nbsp%3B%20The%20format%20of%20the%20cell%20does%20not%20matter.%26nbsp%3B%20Use%20a%20formula%20of%20the%20form%20%3DISNUMBER(--A1)%20to%20determine%20if%20A1%20is%20numeric%20or%20can%20be%20interpreted%20as%20numeric.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20common%20causes%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20separators%2C%20typically%20period%20(%22.%22)%20and%20comma%20(%22%2C%22)%2C%26nbsp%3B%20are%20not%20consistent%20with%20the%20configuration.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20The%20%22number%22%20contains%20non-breaking%20spaces%20(CHAR(160)).%26nbsp%3B%20Use%20a%20formula%20of%20a%20formula%20of%20the%20following%20form%20to%20remove%20unwanted%20spaces%20and%20non-breaking%20spaces%3A%3C%2FP%3E%3CP%3E%3D--SUBSTITUTE(SUBSTITUTE(A1%2CCHAR(160)%2C%22%22)%2CCHAR(32)%2C%22%22)%3C%2FP%3E%3CP%3ECopy%20the%20result%2C%20and%20paste-value%20into%20the%20original%20cell%20(A1%2C%20in%20this%20example).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561632%22%20slang%3D%22en-US%22%3ERe%3A%20Correction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3B%2C%20here%20is%20automatic%20conversion.%3C%2FP%3E%0A%3CPRE%3E%3D%225%22%2B%223%22*%224%22%3C%2FPRE%3E%0A%3CP%3Ealso%20works%20fine%20despite%20we%20use%20text%20representation%20of%20numbers.%3C%2FP%3E%0A%3CP%3EHowever%2C%20both%3C%2FP%3E%0A%3CPRE%3E%3DFV(%221%25%22%2C%2260%22%2C%22-100%22%2C%22-1000%22%2C%22One%22)%0Aand%0A%3DFV(%221%25%22%2C%2260%22%2C%22-100%22%2C%22-1000%22%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Ereturn%20%23VALUE!.%20The%20latest%20variant%20is%20quite%20common.%20If%20the%20last%20parameter%20is%20returned%20by%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(A1%26gt%3BB1%2C1%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eotherwise%20it%20returns%20empty%20string%20instead%20of%20zero%2C%20we%20have%20%23VALUE!%20if%20use%20the%20value%20in%20such%20cell%20as%20parameter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561841%22%20slang%3D%22en-US%22%3ERe%3A%20Correction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561841%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Sir%2C%20I%20got%20it%20later.%20I%20removed%20the%20text%20features%2C%20it%20returned%20the%20answer.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Oji-C-1615
New Contributor

What is the meaning of #VALUE! . In Financial Function, FV.

4 Replies

@Oji-C-1615 , FV returns #VALUE! error if one of the arguments is not number.

Highlighted

FV("1%","60","-100","-1000","1") works just fine, despite the fact that the parameters are text.

 

@Oij-C, if you have a question about your use of an Excel function, it behooves you to show us how you are using the function (copy from the Formula Bar), as well as any cell values that you reference.

 

A #VALUE error might indicate that a parameter is or references text that Excel does not recognize as numeric.  The format of the cell does not matter.  Use a formula of the form =ISNUMBER(--A1) to determine if A1 is numeric or can be interpreted as numeric.

 

Some common causes:

 

1. The separators, typically period (".") and comma (","),  are not consistent with the configuration.

 

2. The "number" contains non-breaking spaces (CHAR(160)).  Use a formula of a formula of the following form to remove unwanted spaces and non-breaking spaces:

=--SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(32),"")

Copy the result, and paste-value into the original cell (A1, in this example).

@Joe User , here is automatic conversion.

="5"+"3"*"4"

also works fine despite we use text representation of numbers.

However, both

=FV("1%","60","-100","-1000","One")
and
=FV("1%","60","-100","-1000","")

return #VALUE!. The latest variant is quite common. If the last parameter is returned by formula like

=IF(A1>B1,1,"")

otherwise it returns empty string instead of zero, we have #VALUE! if use the value in such cell as parameter.

Thank you Sir, I got it later. I removed the text features, it returned the answer.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies