Error message _xlfn.SINGLE

Copper Contributor

I have been using the old logical functions if, and,or. They are accepted when inserted but after being filed, they show up with this error message that does not help me anyway given that the functions that I use are those mentioned above.

Here is an example:

'=SE(OU(_xlfn.SINGLE(_ida2)<_xlfn.SINGLE(_idaini);_xlfn.SINGLE(_ida2)>_xlfn.SINGLE(_idafim));_espa;SE(E(_xlfn.SINGLE(_tiev)="T";_xlfn.SINGLE(_ida2)>=_xlfn.SINGLE(_idaini);_xlfn.SINGLE(_ida2)<=_xlfn.SINGLE(_idafim));_xlfn.SINGLE(_valor);SE(E(_xlfn.SINGLE(_tiev)="U";_xlfn.SINGLE(_ida2)=_xlfn.SINGLE(_idaini));_xlfn.SINGLE(_valor);_espa)))

8 Replies

@LCSALLES985  unfortunately I'm having problems reading your formula due to translation issues but try using ctrl-shift-enter.  Some of these legacy formulas handled arrays different than the new excel and so excel apparently has 'flagged' your formula as such.

Sorry for my English. Thank you and I will be back in a couple of hours.

@LCSALLES985  your English was fine, it was just the formula itself that didn't translate, but from what I can interpret it appears you have a number of named ranges you are referencing and that is what Excel is having issue with.  Are those named ranges single cell or ranges or dynamic?  Did using ctrl-shift-enter fix the problem?

@mtarler   Unfortunatelly I only saw and read this message today.  I will try it now.

@LCSALLES985   Unfortunatelly your sugestion did not work.

@LCSALLES985 

Also unfortunatelly I am not finding more messages sent by me and answeered by the community in the past 2 days. Can you direct me to where they are?

@LCSALLES985  As I mentioned before, it appears there is something with your defined names.  can you attach a copy of the actual workbook?  As for recent messages, all I see was my message followed by your messages today.

@LCSALLES985 

Possible scenario is that formula was created on Excel with dynamic arrays, after that file was opened in Excel without dynamic arrays. That could be same your Excel if you are on semi-annual channel - during the DA deployment for this channel in July this year DA disappeared for a while. To my knowledge now the issue is fixed and semi-annual shall be fully on DA. Or it was opened on another computer.

 

You may safely remove _xlfn.SINGLE. If you are with DA (to check start typing =SORT if available or not, or any other such function, but with name for your locale. Or simply type in any empty cell =A1:A9 and check if it returns an array or not) nothing to do more. If not, you may enter it as array formula with Ctrl+Shift+Enter or change _xlfn.SINGLE on @. Result will be different, but I'm not sure which one shall be.