Oct 22 2019 01:34 PM
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
Oct 22 2019 01:50 PM
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?
Oct 22 2019 01:52 PM
Oct 22 2019 02:11 PM
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.
Oct 22 2019 02:29 PM
Nov 07 2019 10:19 AM
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)))
Jul 20 2020 09:58 AM - edited Jul 20 2020 11:03 AM
@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)}
'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
Jul 20 2020 01:28 PM
@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.
Feb 18 2021 10:07 AM
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.
Mar 05 2021 05:39 AM