I want to more easily analyze data in either google sheets or excel.
The purpose of this project will be to write a program that presents data points that help me to find and evaluate predictable ranges of oscillation and the frequency of that oscillation over time.
This is what the program must do:
1) Analyze 1095 columns (3 years of daily numbers) across approximately 7200 individual rows of numbers.
2) Numbers will range in value from 1.00 to 999.99
3) For each individual row of values I need the program to calculate:
(a) Round down each number to the nearest .50. For example, 52.77 should be rounded down to 52.50. For example 61.20 should be rounded down to 61.00
(a) the median value of the row
(b) the low mode beneath the median in that row
(c) the high mode above the median in that row
(d) (c) minus (b)
(e) the number of times the low mode climbed to the high mode in that row
(f) the number of times the high mode declined to the low mode in that row
(g the sum of (d)+(e)
4) After the calculations listed in point (3), I will need to be able to sort the data by any columns I wish.
Some challenges to consider for points (e) and (f):
If for example the values delineated by "," below were adjacent cells in a row, notice that the values that occur in-between the low and high mode do not climb or decline in a linear way, and also sometimes values repeat. The program you write will need to be smart enough to ignore adjacent occurrences of either mode and ignore the data points in-between the low mode and high mode values when determining the number or times the low mode climbed to the high mode, and the high mode declined to the low mode.
For example, among the values below if the low mode is 2.00 and the high mode is 8.00
In the above instance:
The number of times the low mode climbed to the high mode the program should return the value "2"
The number of times the high mode declined to the low mode the program should return the value "1"