Use dynamic arrays for array functions on versions of Excel that support dynamic arrays
Currently, if you use the GUI to return an array of values, the array formula will always use a static array, even on versions of Excel that support dynamic arrays. Also, if you manually enter a PI DataLink array formula as a dynamic array (i.e. you are using Enter and not Ctrl + Shift + Enter) in a version of Excel that supports them, the top cell will contain the message "Resize to show all values", even when there is enough room to show all values.
Please make PI DataLink adapt its behaviour according to the version of Excel. If the Excel version supports dynamic arrays, then array formulas generated by the GUI should use dynamic arrays, and the message "Resize to show all values" should never be used, since Excel will just generate a #SPILL! error. If the Excel version does not support dynamic arrays, then PI DataLink should continue with its current behaviour.
After extended support ends for Office 2016 and Office 2019 in 2025, all supported versions of Excel will support dynamic arrays, and all support for static arrays could then be dropped from PI DataLink.
Thank you for your feedback! Acknowledging the value that dynamic arrays will deliver, we plan to support the feature in PI DataLink 2021.
Please stay tuned for more updates, and don’t stop giving us feedback.
OSIsoft, Product Manager