Simplify formula

[artz]

Hi,

After getting help for a formula to return the n last numerical value in a column, I am faced with a new problem. The sum of multiple differences, i.e., twenty in all, creates a formula that returns a "formula too large" message.

Does anyone have a way either using a UDF or cell formula to reduce the following cell formula to a working formula:

=VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-2))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-3))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-4))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-5))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-6))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-7))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-8))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-9))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-10))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-11))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-12))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-13))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-14))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-15))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-16))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-17))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-18))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-19))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-19))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-20))+VLOOKUP(9.99999999999999E+307,A:A,1)-LOOKUP(9.99E+307,A1:INDEX(A:A,MATCH(9.99E+307,A:A)-21))

It's not really complicated, it just requires summing multiple instances of the last numerical value in a column minus the second to last, last numerical value in a column minus the third to last, last numerical value in a column minus the fourth to last, etc.ultimately going back twenty values in total.

I apologize for the long code listing. There may be a better way to show it but I don't know how.

A VBA solution is what I would expect to make this type of calculation with a loop. Does anyone he Forum have an idea on how to do this?

Thanks,

Art

 

[T. Valko]

Biff,

The array formula works great, thanks a lot.

Art

You're welcome. Thanks for the feedback!