Nov 09 2018
09:27 AM
- last edited on
Jul 12 2019
11:16 AM
by
TechCommunityAP
Nov 09 2018
09:27 AM
- last edited on
Jul 12 2019
11:16 AM
by
TechCommunityAP
I am using the following function on a Windows machine with the latest version of Office 365 (as per the help page) but the cell with the formula just shows the equation underlined. Cell A1 has Letters and Numbers (formatted as text).
=IFS(A1="STA","STA",A1="0JK","K",A1="K","1",A1="1","2",A1="2","3",A1="3","4",A1="4","5",A1="5","6",A... |
I am perplexed... Any suggestions?
Nov 09 2018 09:37 AM
It was on comma missed
=IFS(L2="STA","STA",L2="0JK","K",L2="K","1",L2="1","2",L2="2","3",L2="3","4",L2="4","5",L2="5","6",L2="6","7",L2="7","8",L2="8","9",L2="9","10",L2="10","11",L2="11","12",L2="12","")
Nov 09 2018 10:48 AM
Even more confused now...
I corrected the missing comma before I pasted/posted the equation and the equation was still underlined and your version has the prior equation references to cell L2 and not A1 which is what I pasted into my post.
I went back to that equation cell and copied the equation text/formula to a new cell and it is no longer underlined, but the original equation cell. Is there an original memory that a cell holds onto?
Nov 09 2018 10:52 AM
and I forgot to mention that it is displaying as text (not underlined) but still not behaving as a function.
I think I get it now... The field that I was putting the equation into was formatted as a text field before I copied it to another spreadsheet. As soon as I pasted it into a virgin cell it all worked...
Nov 09 2018 11:39 AM
May be an issue with Copy/Paste/Drag_Paste as opposed to the actual Function IFS
Documented steps and the unorthodox behavior in the attached spreadsheet with a subset of data and the formula in question for debugging and testing.
Nov 09 2018 12:05 PM
SolutionThat's since your column B is formatted as text. Return format to General and re-enter the formula (F2->Enter), it works. Please see attached.
Nov 09 2018 12:25 PM
The screen was showing me the same original cell answer for the other drag copied cells. Minutes later and after posting the file here, my spreadsheet refreshed and all the calculations are correct for each cell. I checked the file uploaded and they show what I now see on my screen, not what I saw when I saved it. This behaviour feels surreal... I will take a break now to look for hidden cameras and listen for a room of people laughing hysterically at my reactions.
Nov 09 2018 12:05 PM
SolutionThat's since your column B is formatted as text. Return format to General and re-enter the formula (F2->Enter), it works. Please see attached.