Implicit intersection operator: @ breaking my formulas

[dakuhlke]

Contrary to Microsoft claiming: " It's important to note that your formulas will continue to calculate the same way they always have.", this is not the case. The introduction of the @ symbol in version 1910 is now breaking my formula:

=@IF(I17<>"Target Season","",IF(@IFERROR(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW17,'Lock @ Style-Country'!A:A,0),1),"")<>"",INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW17,'Lock @ Style-Country'!A:A,0),1),IF($G17="","",$G17)))

This formula now returns 0's instead of blanks for the AH:AH range. When I remove the red @ symbols, the formula works again and returns a blank cell when AH:AH is blank. Any ideas on a permanent fix? 

Thanks, 

David 

[#2]

@dakuhlke 

Update-- when I remove the @ in front of IFERROR, the formula works as normal, even with leaving the @ in front of the IF at the beginning. Why does this occur? 

[#3]

Are you able to provide a sample of this in context -- i.e., can you share a sample of the actual formula in the spreadsheet/workbook of which it's a part.

And, ideally, provide an English language translation of what this formula is doing, the different results it delivers under different conditions. It's hard enough reading nested IF and MATCH conditions in a formula one has written oneself; next to impossible to read what another developer has done.

[#4]

@mathetes 

Here is as much as I can provide without sacrificing proprietary company information. See screen shots attached:

The formula in question exists on the RAW DATA tab. This raw data feeds into a pivot table on the "Lock Tab". Users can change values in the RAW DATA tab by updating the values in the AH column on the "Lock Tab". If users don't enter any values in the AH column for the target season, then the RAW DATA tab should display a blank cell. With the new 1910 Version of Excel and the introduction of the @ operator, the values are coming back as "0" instead of blank. So if users are not entering a value in column AH on the "Lock tab", the RAW DATA tab shows as "0". This creates huge problems for macros that are used to copy the values from column "F" on the RAW DATA tab and overwrite the existing data in the "Target Season" field on the "Lock Tab" pivot table. I need to disable Excel from adding this @ operator...... or find a way to rewrite this formula that alleviates the issue. 

[#5]

Afraid that there's not much I can suggest. I totally appreciate your need to protect proprietary data. Just looking at the screen prints, though, doesn't help really grasp what the macros do, for just one example.

Now, you clearly know your Excel--couldn't have written that formula if that weren't the case--so although I'm going to ask a question, I'm NOT questioning your abilities with Excel.

I will offer this observation based on past experience with another internal company process, back before I retired: somebody in IT had written a very elaborate Visual Basic program to process data from an SQL query of a mainframe database, after the data were imported into Excel.

I re-wrote the spreadsheet to use built-in Excel functions (some from the D_____ series of functions, for example, designed to extract data from data tables). My spreadsheet using existing Excel function, without any macros, delivered the needed results in a fraction of the time of the macro-based system.

This of course doesn't mean the same is true for your situation at all.....and I realize that fully. But I offer it just as a question: Excel's built-in functions are amazingly powerful, so have you considered an altogether different way to use Excel to meet your particular business need? Is the macro really needed to "scrub" values from one place and put them in another?

[#6]

Hi mathetes, 

I really appreciate your thoughts and insight on the situation. The Excel file was built before I took over the responsibility for the tool. I have since been able to refactor the Visual Basic macros scripts and refine the formulas throughout the workbook. The file operates much faster and is much lighter. The team that uses this tool is very happy with the progress! The file does the job right now, and I'm currently taking the approach of: "if it's not broken, don't fix it".

However, this new Microsoft update threw a wrench into the formula functions. I was able to rewrite the formula to work around the "@" symbol operator. The only change for users is that they will need to type the word "zero" if they want to lock a forecast at 0. This is because the "@" symbol operator causes confusion for some functions' ability to differentiate between '0' and a blank cell. 

I also noticed that, without any updating (that I know of), Excel stopped inserting the "@" operator in the formulas. The Excel version is still version 1910, and I've also verified this change on other affected machines with version 1910. Very strange. I'm still waiting to hear back from Microsoft on my support ticket. I'm assuming I'm not the only one to complain about the "@" operator....

Regardless... here's the updated formula for the work-around:

=IF(I2<>"Target Season","",IF(IFERROR(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1),"y")<>"y",IF(UPPER(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1))="ZERO", 0, IF(INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1)>0, INDEX('Lock @ Style-Country'!AH:AH,MATCH($BW2,'Lock @ Style-Country'!A:A,0),1),IF(E2=0,0,IF($G2="","",$G2)))),IF($G2="","",$G2)))

[#7]

@dakuhlke I also see the same issue even with Excel version 2006 build 13001.20384). I see this issue when assigning a formula using VBA Code. Skip to the bottom for my VBA solution.

I type in a formula and press Ctrl Shift Enter (CSE) to disable the implicit intersection: My formula looks like this when you view the formula in the formula bar: Note the surrounding curly brackets that signal excel to disable Implicit Intersection.

={[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([Close Date]),0)-1,0)}

• If you Copy and Paste the formula to a different cell, the { } curly brackets will correctly be included in the pasted formula.
• If you mouse click to select the cell's formula in the formula bar, the { } brackets are immediately removed (the braces vanish when you click into the formula bar, so the resulting pasted formula is also missing the curly brackets. However, the formula copies correctly and does not add in any @ operators. So the resulting calculation is correct.
• However, try using VBA to assign the formula Excel will add in the @ operator. Although both the source and destination formulas appear correct from within Excel VBA the formula displayed in the Excel formula bar adds back the @ sign. In other words using VBA to copy a formula that contains dynamic arrays will not be correctly interpreted by Excel. 

'Source formula:
? Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Formula
=[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([Close Date]),0)-1,0) 

'VBA statement to copy formula:
Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).Formula = Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Formula

'Target formula after VBA statement to copy formula: 
? Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).Formula
=[@[Adjusted All Portfolio]] - INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([@[Close Date]]),0)-1,0) 'NOTE: YEAR([Close Date]) changes to YEAR([@[Close Date]]).

'AND the cell result will contain the following formula:
=[@[Adjusted All Portfolio]] -@ INDEX([Adjusted All Portfolio],MATCH(YEAR([@[Close Date]]),YEAR([@[Close Date]]),0)-1,0) 

'NOTES: "- INDEX" changes to "-@ INDEX"
'       "YEAR([Close Date])" changes to "YEAR([@[Close Date]])"

I conclude from the above that assigning a formula using VBA code somehow changes the formula to utilize Excel's Implicit Intersection.  

To work around the issue I use VBA to copy and paste the source cell's formula as follows:

Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Copy
Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

ALTERNATIVELY, use VBA's formula2 reference:

'VBA statement to copy formula:
Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-1, 0).Formula2 = Range("History[[#Totals],[Adjusted All Portfolio YTD Change In Value]]").Offset(-2, 0).Formula

How to add dynamic array formula in Excel VBA 

Formula vs Formula2 

[#8]

@dakuhlke  Did you try replacing the INDEX(MATCH()) nesting with a simple VLOOKUP()? Something like this:

=IF(I17<>"Target Season","",IF(IFERROR(VLOOKUP($BW17,'Lock @ Style-Country'!A:AH,34,FALSE),"")<>"",VLOOKUP($BW17,'Lock @ Style-Country'!A:AH,34,FALSE),$G17))

 I also got rid of that IF($G17="","",$G17) because maybe I'm missing something but that doesn't do anything.

[#9]

@mathetes 

In my workbook, it changed several formulas like "=IF(G2 = "Yes", "", D2)"

to

"=IF(G2 = @Yes,"",D2)"

Completely breaking the functionality of my workbook.  In each case, the referenced value was *NOT* an array, nor part of an array formula.

Fortunately, the bulletin seems to be inaccurate in at least one regard.  On save/re-open, my manual fixes of the formulas were not converted to legacy array formulas.

A method of disabling this automatic conversion should have been provided, along with an explicit warning to the user in the application that this was taking place at the time it happened.

[#10]

Thank you @Grosner the formula2 solution worked for me. The @ broke my Index(Match formula when it kept adding @ in front of my Match arrays with VBA. I replaced FormulaArray with Formula2 and it stopped. Thanks!!