SOLVED

Excel functions returning unexpected result

%3CLINGO-SUB%20id%3D%22lingo-sub-2920738%22%20slang%3D%22en-US%22%3EExcel%20functions%20returning%20unexpected%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2920738%22%20slang%3D%22en-US%22%3E%3CP%3EDevice%20and%20OS%20platform%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20PC%2FWindows%2010%26nbsp%3B%20Pro%3CBR%20%2F%3EExcel%20product%20name%20and%20version%20number%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BMicrosoft%20365%20(Apps%20for%20enterprise)%3C%2FP%3E%3CP%3EExcel%20File%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%221%20IF%20Function.xlxs%22%20%2C%20Sheet%20%22IF%20Ex3%22%26nbsp%3B%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20the%20Functions%20Library%20in%20Cells%20H4%20and%20again%20in%20K4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMethod%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EClicking%20on%20cell%20H4%3CBR%20%2F%3EDrop%20down%20%22Logical%22%20and%20choose%20%22IF%22%3CBR%20%2F%3ELogical_test%20H3%26gt%3BH2%3CBR%20%2F%3EValue_if_true%20Increased%3CBR%20%2F%3EValue_if_false%20No%20Increase%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpected%20outcome%2FFormula%20Result%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BNo%20Increase%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EActual%20result%20%23REF!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVisual%20noted%20-%3C%2FP%3E%3CP%3EWhen%20using%20the%20Function%20Library%26gt%3B%26gt%3BIF%26gt%3B%26gt%3BFunction%20Arguments%20popup%20the%20formula%20automatically%20put%20Quotation%20marks%20around%20true%20and%20false%20values%20%3CSTRONG%3EEXCEPT%3C%2FSTRONG%3E%20where%20the%20worn%20NO%20forms%20part%20of%20the%20argument.%3C%2FP%3E%3CP%3Ei.e.%20formula%20returned%20when%20IF%20popup%20closed%20as%20%3DIF(H3%26gt%3BH2%2C%22Increased%22%2CNo%20Increase)%20instead%20of%20expected%20%3DIF(H3%26gt%3BH2%2C%22Increased%22%2C%22No%20Increase%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20quotation%20marks%20are%20%3CSTRONG%3Emanually%20inserted%3C%2FSTRONG%3E%20then%20the%20result%20returns%20as%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20the%20Functions%20Library%20facility%20is%20being%20used%2C%20requiring%20manual%20input%20of%20quotation%20marks%20is%20not%20something%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20only%20when%20the%20Value_if_true%20or%20Value_if_false%20is%20%3CSTRONG%3ENo%3C%2FSTRONG%3E%20that%20this%20happens.%20Not%20returns%20as%20expected%20as%20does%20any%20other%20word%20-%20see%20K5%20and%20K6.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2920738%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2920811%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20functions%20returning%20unexpected%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2920811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1205304%22%20target%3D%22_blank%22%3E%40Daithi_O_Broin%3C%2FA%3E%26nbsp%3BYou%20forgot%20the%20quote%20marks%20around%20%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3Eno%20increase%3C%2FFONT%3E%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EThis%20is%20what%20you%20have%20in%20H4%20right%20now%3A%3C%2FP%3E%3CP%3E%3DIF(H3%26gt%3BH2%2C%22Increased%22%2Cno%20Increase)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2920831%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20functions%20returning%20unexpected%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2920831%22%20slang%3D%22en-US%22%3EHi%20Riny.%20I%20know%20that%20there%20should%20be%20quote%20marks.%20The%20issue%20is%20that%20when%20using%20the%20Functions%20Library%20popup%20for%20the%20IF%20function%2C%20the%20Function%20Arguments%20Popup%20automatically%20puts%20quote%20marks%20on%20anything%20put%20into%20the%20Value_if_%20boxes%20unless%20what%20is%20written%20in%20is%20No%20.%20This%20is%20what%20I%20am%20trying%20to%20sort%20out.%20Why%20does%20the%20Popup%2FFunctions%20arguments%20not%20automatically%20put%20the%20quote%20marks%20in%3F%3C%2FLINGO-BODY%3E
New Contributor

Device and OS platform      PC/Windows 10  Pro
Excel product name and version number       Microsoft 365 (Apps for enterprise)

Excel File                     "1 IF Function.xlxs" , Sheet "IF Ex3"  attached

 

Using the Functions Library in Cells H4 and again in K4

 

Method

Clicking on cell H4
Drop down "Logical" and choose "IF"
Logical_test H3>H2
Value_if_true Increased
Value_if_false No Increase

 

Expected outcome/Formula Result         No Increase


Actual result #REF!

 

Visual noted -

When using the Function Library>>IF>>Function Arguments popup the formula automatically put Quotation marks around true and false values EXCEPT where the worn NO forms part of the argument.

i.e. formula returned when IF popup closed as =IF(H3>H2,"Increased",No Increase) instead of expected =IF(H3>H2,"Increased","No Increase")

 

When the quotation marks are manually inserted then the result returns as expected.

 

As the Functions Library facility is being used, requiring manual input of quotation marks is not something expected.

 

It is only when the Value_if_true or Value_if_false is No that this happens. Not returns as expected as does any other word - see K5 and K6.

5 Replies

@Daithi_O_Broin You forgot the quote marks around no increase.

This is what you have in H4 right now:

=IF(H3>H2,"Increased",no Increase)

Hi Riny. I know that there should be quote marks. The issue is that when using the Functions Library popup for the IF function, the Function Arguments Popup automatically puts quote marks on anything put into the Value_if_ boxes unless what is written in is No . This is what I am trying to sort out. Why does the Popup/Functions arguments not automatically put the quote marks in?

@Daithi_O_Broin Actually, I never use that function dialogue box. But I tried it now and the quote marks are included automatically in my case, for both the if_true and if_false values. 

 

Don't know why it doesn't work for you.

Thanks Riny. I will look further.
best response confirmed by allyreckerman (Microsoft)
Solution

@ thought I should post this as I found the issue myself.

Examining the workbook - which I had received from a colleague - I discovered that under the Formulas Tab in the Name Manager that the word no had aligned with the Value #REF! Deleting this seemed to clear the issue.