Forum Discussion
Prevent Excel From Converting to Legacy Array
Hello everyone,
I am working on an excel file that will be uploaded to Oracle BI publisher (version 12C) which is compatible with excel 2003. As such I am building this file as a .xls file type in compatibility mode. I am on windows 10 running 365 for Enterprise, version 2208
I have a formula that is working when I create the file which is an array formula:
=SUMPRODUCT(SUMIF('Data Dump 1'!AU:AU,'Data Dump 1'!$FW:$FW,'Data Dump 1'!AR:AR))
This formula is summing the value in AR when any value listed in FW is in AU.
The issue I am having is whenever I go to save the file I get a compatibility error. Most of these errors are not an issue when running through BI Publisher as the formulas remain unchanged and run normally when we open the file. However, with the above formula (and others like it), Excel converts the file to a legacy array formula which does not work in BI publisher and causes the file to fail.
In previous versions of excel (2016 I think) this conversion did not occur and any array formulas run through BI publisher are still working today. If I try to edit them while using excel 365 to update the file, they break in the same way.
Is there a way I can prevent the conversion to a legacy array or is there another formula that would attain the same result but is not an array? I would like to avoid using a sumif + sumif + sumif... method for each FW value as this would cause a significant decrease in performance as FW could have thousands of values.
I unfortunately no longer have access to excel 2016 otherwise I would use that to finalize the file.
Thank you in advance for your help!
Thank you for this. Unfortunately index / match formulas also result in the same loss of functionality. That being said I did find a work around this morning. I essentially used a helper column on another sheet that needed to be created anyways so I added the helper column as part of that data.
I do appreciate the confirmation that preventing the conversion to a legacy array formula is not possible.
Thank you again!
2 Replies
In Excel 365, there is not a direct way to prevent the conversion to a legacy array formula. Legacy array formulas are automatically created when saving a workbook in an older Excel file format (such as .xls) or when using certain compatibility settings.
However, you can try a workaround to achieve the same result without using an array formula. Instead of using a single array formula, you can use a combination of SUMIFS and INDEX/MATCH functions.
Here is an example:
- Assuming the data range in 'Data Dump 1'!AU:AU contains the values to match against, 'Data Dump 1'!$FW:$FW contains the criteria values, and 'Data Dump 1'!AR:AR contains the values to sum, you can use the following formula:
=SUMIFS('Data Dump 1'!AR:AR, 'Data Dump 1'!AU:AU, INDEX('Data Dump 1'!$FW:$FW, MATCH(1, ('Data Dump 1'!AU:AU<>"") * ('Data Dump 1'!AU:AU='Data Dump 1'!$FW:$FW), 0)))
- This formula uses INDEX/MATCH to retrieve the matching values in 'Data Dump 1'!$FW:$FW based on the criteria in 'Data Dump 1'!AU:AU. Then, SUMIFS is used to sum the corresponding values in 'Data Dump 1'!AR:AR based on the matched criteria.
By using this approach, you should be able to avoid using an array formula while still achieving the desired result. Please note that you may need to adjust the cell references and range sizes based on your specific data layout.
Although this workaround may be more computationally intensive than the array formula, it should still provide a reasonable performance compared to using multiple SUMIF formulas for each FW value. The texts and steps were created by the AI.
Hope this will help you.
Thank you for this. Unfortunately index / match formulas also result in the same loss of functionality. That being said I did find a work around this morning. I essentially used a helper column on another sheet that needed to be created anyways so I added the helper column as part of that data.
I do appreciate the confirmation that preventing the conversion to a legacy array formula is not possible.
Thank you again!
