SOLVED

Show IFNA Output As Number

%3CLINGO-SUB%20id%3D%22lingo-sub-2228451%22%20slang%3D%22en-US%22%3EShow%20IFNA%20Output%20As%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2228451%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20show%20%22101%22%20with%20a%20IFNA%20formula.%20This%20works%20great.%20But%20the%20problem%20is%2C%20that%20the%20outputted%20number%20(101)%20is%20now%20shown%20as%20a%20number%2C%20and%20can%20thereby%20not%20be%20used%20in%20a%20pivot%20table%2C%20as%20I%20would%20use.%20Is%20there%20a%20way%2C%20that%20you%20can%20force%20the%20IFNA%20output%20to%20be%20a%20number%20-%20or%20is%20there%20another%20work%20around%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2228451%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2228462%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20IFNA%20Output%20As%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2228462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1005300%22%20target%3D%22_blank%22%3E%40TobiasHyldeborg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20formula%20you%20are%20using%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I would like to show "101" with a IFNA formula. This works great. But the problem is, that the outputted number (101) is now shown as a number, and can thereby not be used in a pivot table, as I would use. Is there a way, that you can force the IFNA output to be a number - or is there another work around?

 

Thanks in advance.

4 Replies

@TobiasHyldeborg 

What is the formula you are using?

=IFNA(VLOOKUP($A2; Table2[#All]; 2; FALSE)*1; "101")
best response confirmed by TobiasHyldeborg (New Contributor)
Solution

@TobiasHyldeborg 

If you want the result to be a number, don't enclose 101 in quotes:

 

=IFNA(VLOOKUP($A2; Table2[#All]; 2; FALSE)*1; 101)

Nice, thank you!