Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
user ahersey3 said:How can I find the largest interval between occurrences of a number in a column?
We are doing some statistical analysis of numbers and we once did what we are trying to do using a SQL databases and some stored procedures to calculate it but we're trying to slim down and use some spreadsheets for it. Basically we have a cloumn say 'A' and it is populated with the numbers 0-9 like so:
1
2
5
8
2
0
9
3
7
8
5
4
5
7
2
4
5
9
0
4
2
Now I'm trying to find the largest interval between the occurrence of 2 in the list for example. By looking at the list you can see the intervals are
3, 10, and 6 by starting at the first 2 and counting rows until the next 2.
I'm trying to find a very simple way to look at the column and find the largest interval between the occurrences of the number 2. I was hoping there was some sort of built in function I could put info into and get the answer 10 so I can put that function into another cell instead of wrting a script but I'm afraid I might need to use a script to create my own function. I can do it with a calculation sheet that has columns that using filter and max and stuff easily get me my results but that seems a bit old fashioned and bulky considering I'd need multiple columns to do the calculations for various numbers.
I'm thinking a script would be the best way that way I could just send the number I'm working on to the script and the script would calculate the max interval for me and return 10. The problem is I'm new to google spreadsheets and scripts and I'm stuck trying to figure out how I would send a filtered cloumn to the script so that only the row ids of the number I'm looking for would go to the script. Can someone point me in the right direction with an example?
-----------------------------------------------------------
No comments:
Post a Comment