Can anybody please kindly help me fix this problem with a linked array formula that I have made in a worksheet created in Excel 2010 and linked it to a worksheet created in Excel 2003. The array formula is as follows:
={SUM(INDEX(SEPTEMBER2014!BX8:BX99;LARGE(IF(SEPTEMBER2014!$D$8:$D$99=TIME(12;0;0);ROW(SEPTEMBER2014!$D$8:$D$99)-ROW(SEPTEMBER2014!$D$8)+2;"");2)):INDEX(SEPTEMBER2014!BX8:BX99;MATCH(2;1/(SEPTEMBER2014!$D$8:$D$99=TIME(12;0;0)))))}
In the worksheet SEPTEMBER2014, the value of cell BX26 is 25070 when value of cell D26 is 12:00. But in this particular worksheet where the array formula is written and linked to worksheet SEPTEMBER2014, the formula is returning a value of 100280. I have rechecked the formula many times and confirmed that it was correct, and it was working fine until I opened the worksheet in Compatibility mode in Excel 2010. Then it started showing wrong values.
Sometimes there is also a message saying "Array formulas are not valid in merged cells" whenever I try changing or editing the array formula.
Thank you very much in advance for any help.
={SUM(INDEX(SEPTEMBER2014!BX8:BX99;LARGE(IF(SEPTEMBER2014!$D$8:$D$99=TIME(12;0;0);ROW(SEPTEMBER2014!$D$8:$D$99)-ROW(SEPTEMBER2014!$D$8)+2;"");2)):INDEX(SEPTEMBER2014!BX8:BX99;MATCH(2;1/(SEPTEMBER2014!$D$8:$D$99=TIME(12;0;0)))))}
In the worksheet SEPTEMBER2014, the value of cell BX26 is 25070 when value of cell D26 is 12:00. But in this particular worksheet where the array formula is written and linked to worksheet SEPTEMBER2014, the formula is returning a value of 100280. I have rechecked the formula many times and confirmed that it was correct, and it was working fine until I opened the worksheet in Compatibility mode in Excel 2010. Then it started showing wrong values.
Sometimes there is also a message saying "Array formulas are not valid in merged cells" whenever I try changing or editing the array formula.
Thank you very much in advance for any help.
