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:
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
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:
Code:
=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