How do I write a formula to display missing numbers from 1 to 10?

[arthurz11]

 

[barry houdini]

According to ChatGPT.....

"Is TOCOL function available in Excel 2021"

No, the TOCOL function is not available in Excel 2021. It was introduced in Excel for Microsoft 365 (formerly Office 365) and is part of the dynamic array functions that are specific to the subscription version. Excel 2021 does not have the dynamic array functions like TOCOL, UNIQUE, FILTER, or SORT.

If you want to use the TOCOL function, you would need to switch to a version of Excel that supports dynamic arrays, such as Excel for Microsoft 365 or Excel 2021 on a subscription plan.

Let me know if you need help with alternatives or workarounds!

 

[MARK858]

 

[Eric W]

That's weird. Because when I looked it up in Bing

I guess Bing is not perfect. I have 2021 installed on a laptop, and I checked it before I posted. Unless there was an update I missed or something. I just upgraded to 2024, and it is in there. But even there, some of the functions aren't exactly the same. I have BYROW, but only if I use a LAMBDA, the eta structure is not available.

 

[Alex Blakenburg]

@Eric W & @Scott Huish - in terms of which version has what, I find Russell Proctor's Better Solutions Site the easiest reference to use.

Excel Functions Updates

Excel Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[fahad_ibnfurjan]

=IFERROR(SMALL(IFERROR(ROW($A$1:$A$10)/(COUNTIF($A$1:$A$10,ROW($A$1:$A$10))=0),""),ROW($A$1:$A$10)),"")

 

[arthurz11]

An non-volatile option

=LET(s,SEQUENCE(10),FILTER(s,ISNA(XMATCH(s,A2:A10))))

Works great!! Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.