Trying to improve efficiency of array formula
On Nov 24, 3:42*pm, Anthony wrote:
My formula spans over 500 rows, but here is a
simple version of it:
{=SUM(IF(IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A170,
IF(A1:A5A7:A11,A1:A5,A7:A11)-A13:A17,0))}
My interpretation of what you want is:
=MAX(A1-A13,A7-A13,0) + MAX(A2-A14,A8-A14,0) +
MAX(A3-A15,A9-A15,0) + MAX(A4-A16,A10-A16,0) +
MAX(A5-A17,A11-A17,0)
I'm not suggesting that as a solution, of course. It's just a
conceptual module. More generally:
=sumproduct(max(A1:A5-A13:A17, A7:A11-A13:A17, 0))
That does not work as intended, of course. Again, just a conceptual
model. I had hoped to make SUBTOTAL work, but I have not succeeded.
|