CALCULATION IN ARRAY FORMULA Options

[Karthik Bhat]

Hi Manish

 This error occurs due to the limitation on Array formulas. In older versions of Excel the Array formulas can't calculate results over large cell ranges (the formula tries to validate and check for items in column A B H I to give you the result).  Here is the official comment from MS on this error: http://support.microsoft.com/kb/132221

Solution:

A)     Change the range of cells from A:A to A1: A1000 (assuming that the sap data is not more than 999 rows). The new formula would look like :

=SUM(IF('SAP GL'!$A$1:$A$1000='Daily Collection Report'!$B$6,IF('SAP

GL'!$B$1:$B$1000='Daily Collection Report'!$A8,IF('SAP GL'!$I$1:$I$1000='Daily Collection

Report'!$B$5,'SAP GL'!$H$1:$H$1000,0))))

 

B) Your file in its current setup takes a long time to calculate because of numerous array formulas. Use a Pivot table as it is more efficient.

 

C) Use a sumif formula to do this you will require an additional column in "SAP GL".

 

1. Write following formula in column O. O2 =A2&B2&I2. Pull down this formula till the last row.

2. Write a Sumif formula in 'Daily Collection Report' and you get the same result. It is efficient but will require additional formulas in the SAP GL sheet. Sample file attached.

 

Hope this helps.

 --
Thanks
Karthik Bhat

--
You received this message because you are subscribed to the Google Groups "ExcelFiles_India" group.
To post to this group, send email to excelfil...@googlegroups.com.
To unsubscribe from this group, send email to excelfiles_ind...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/excelfiles_india?hl=en.