Why does Excel Add-in show error #VALUE! when trying to pull data periods longer than a month?

Download as pdf : 
Products: MES Excel Addins 
Last Updated: 11-Jun-2021
Versions: 
Article ID: 000096724
Primary Subject: 

Problem Statement:
When trying to pull long periods of data using the Historical Values in the Excel Add-in on Microsoft Excel 2013, the user gets "#VALUE!" in the cells the data was supposed to be showed, although receives a “Success” message (see image below). This document explains why this happens and how to overcome this issue.
User-added image
 

Solution:
This happens because Microsoft Office 2013 has limitation of 65536 rows for array formula. So, for instance, if the user system’s pulls data in a tag for every 1 minute, that means 65536 rows can hold up to around 45 days of data, thus Excel shows the error "#VALUE!" when that limit is reached.
In order to avoid that, there are two options:
  1. The user can either upgrade to a more recent version of Microsoft Office (the 2016 version, has increased the limit to more than 1 million rows) or
  2. Uncheck Output results as an array in the Advanced… option that appears on the Excel Add-in right left menu. This way, the output can be the same as Excel worksheet's max size and the user can still use his/her same Office version. To uncheck that, these are the instructions:
    1. After clicking on Historical Values in the Aspen Process Data tab, a menu will show up on your right side of the screen. Click on Advanced…:
User-added image
 
    1. Uncheck the box that says Output results as an away and then click on OK:
User-added image
 
    1. Close Excel, reopen it and pull the data using Historical Values again.

KeyWords:
#VALUE!
Historical Values
Array formula