Hello Everyone, I am currently streaming financial data into excel which updates on a minute by minute basis. Every minute a new row of data is automatically inputted based on market activity. Is there any formula/function which would expand to cover the new data as the amount of cells increases? Is there any way that I can have it so that the function/formula only looks at the last 20 or so cells, so its like a moving/rolling formula/function?
Thank you very much,
Ed
Totally possible.
Convert your range into a table first, and then your referenced cells will update as new data is entered to it. Select Data -> Insert -> Table
What are you doing with the last 20 cells?
Here's a sum of the last 20, using Funk Master Tony Valko's formula:
=SUM(IF(ISNUMBER(Table1[Column1]),IF(Table1[Column1]>0,IF(ROW(Table1[Column1])>=LARGE(IF(ISNUMBER(Table1[Column1]),IF(Table1[Column1]>0,ROW(Table1[Column1]))),20),Table1[Column1]))))
This is an array formula confirmed with Ctrl+Shift+Enter, instead of exiting the cell with just Enter.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
yes, that is possible
e.g. =SUM(A1:A20) and drag down.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Non array solution
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks