Formula Evaluation Issue

[zero269]

Hello,

I've just encountered a strange formula evaluation issue where only two cells using a custom formula are returning #NAME? instead of the cell value it's supposed to return.

The table currently has 127 records, and it's only two values where this issue is occurring even though the formula is exactly the same in the entire column.

When I choose Show Calculations Steps to Evaluate each portion of the formula, I can see it's reading the correct value (yellow highlights)… it's just not returning it.

I added a basic xlookup below the table to make sure those two Quiz numbers returned the correct book titles (green highlights).

If I replace any working Quiz numbers with those two effected Quiz numbers, the same issue occurs (blue highlights).

All the columns to the right are returning values for the related Quiz number.

@Peter_SSs provided me with this formula here, which has worked flawlessly since. Only changes made was with my Table Names.
Here's the complete formula:

=LET(s,LEFT(XLOOKUP([@Quiz],t_Books[Quiz],t_Books[Title],""),47),IF(LEN(s)<47,s,TEXTBEFORE(s," ",-1)))

Any help would be greatly appreciated. Thanks...

 

[Peter_SSs]

Firstly, in future, can you please post in the forum using the forum's standard font rather than applying the Segoe UI Light font?

The table currently has 127 records,

In a copy of your workbook can you remove all the rows from the formula table except the two problem rows and two rows that are working correctly? Can you also remove from the t_Books table any columns that are not relevant to this calculation and all the rows except those that are needed for the four rows left in the formula table and then post both tables with XL2BB?

 

[zero269]

Firstly, in future, can you please post in the forum using the forum's standard font rather than applying the Segoe UI Light font?


In a copy of your workbook can you remove all the rows from the formula table except the two problem rows and two rows that are working correctly? Can you also remove from the t_Books table any columns that are not relevant to this calculation and all the rows except those that are needed for the four rows left in the formula table and then post both tables with XL2BB?

Hi Peter,

Thanks for looking at this for me.

I use OneNote to draft most of more lengthy posts as they often take a day or two to finalize, which explains the font/format differences. I switched my default font from 'Segoe UI Light' to 'Segoe UI'. We'll see if this does the trick.

Although I went through the process of creating two new tables, I got the same results. So, I took a closer look at the Formula Evaluation steps and discovered that it's the TEXTBEFORE function... it's not available in Office 2021, which I installed a few days ago after reimaging my laptop.

Considering I use many of the other O365 only Functions, I'm going to have to reinstall O365 on this system…

We can see here that Excel went limp when it got to the TEXTEFORE(s," ",-1) portion of the formula:

Evaluation results:

I stumbled upon this thread which brought me to this source which I'll be adding to my OneNote references considering it shows which functions are in what version of Office… compliments of @etaf and @Fluff

• New Functions in Microsoft 365 @etaf OP
• Excel functions (alphabetical) @Fluff <<< I like this one
• Excel functions (by category) @Fluff

Thanks and best regards,

 

[Peter_SSs]

Glad you discovered the problem. I would never have guessed the problem since your profile says MS 365. If, for some reason, you do not go back to 365 you should update your profile to show the correct version(s) that you are using.

 

[zero269]

Glad you discovered the problem. I would never have guessed the problem since your profile says MS 365.

True, but in my defense it was just installed a bout 5 days ago. I reinstalled O365 last night and and happy to report that everything is back in working order.

I was unaware that M$ was placing most of their new Functions behind a paid firewall. Very disappointed to say the least... especially considering Office 2021 LTSC ProPlus sales for over $400... that only a small subset of new features would be included with it.

I'm just glad it wasn't money out of my pocket... and that I'm able to easily switch back at no expense.