Nov 03 2021 05:04 AM
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.
Nov 03 2021 05:21 AM
@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)
Nov 03 2021 05:28 AM
Nov 03 2021 05:37 AM
@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.
Nov 03 2021 05:39 AM
Nov 07 2021 11:43 PM
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.
Nov 07 2021 11:43 PM
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.