Hi, I hope to get some tips in how to solve a fairly easy problem:
My sheet has parameters with a time stamp to its value. I need to make averages of the parameters for every 10th minute. This is done by some ROUND and FLOOR functions and simply categorize each time interval with a number. First time interval is number 1, second time interval is 2 and so on. This works well. See table. Problem is when two parameters within the same columns has too much time between them which makes the categorization wrong for the paramerers. I highligted this with bold letters. The data sets are big, and the parameters are many.
Solution one: Is is possible to use conditionally formatting cells and highlight them if the diffrence between the lower cell and the upper neighbour cell is greater than ten minuted (as an example)?
Solution two: Use VBA and use a double for-loop to locate cells where the difference between the lower and the upper neihbour cell is greater than than ten minutes, and set in cells to ensure every time interval is included (the value of the cell put in is equal to the value of the cell above). This is out of my range as a coder in VBA.
Thanks, grateful for every help.
My sheet has parameters with a time stamp to its value. I need to make averages of the parameters for every 10th minute. This is done by some ROUND and FLOOR functions and simply categorize each time interval with a number. First time interval is number 1, second time interval is 2 and so on. This works well. See table. Problem is when two parameters within the same columns has too much time between them which makes the categorization wrong for the paramerers. I highligted this with bold letters. The data sets are big, and the parameters are many.
Solution one: Is is possible to use conditionally formatting cells and highlight them if the diffrence between the lower cell and the upper neighbour cell is greater than ten minuted (as an example)?
Solution two: Use VBA and use a double for-loop to locate cells where the difference between the lower and the upper neihbour cell is greater than than ten minutes, and set in cells to ensure every time interval is included (the value of the cell put in is equal to the value of the cell above). This is out of my range as a coder in VBA.
25.07.2019 06:03:00 | 06.00.00 | 31 | 42,9 | 25.07.2019 05:47:28 | 05.40.00 | 29 | -1,3 |
25.07.2019 06:10:39 | 06.10.00 | 32 | 43 | 25.07.2019 05:51:22 | 05.50.00 | 30 | -1,6 |
25.07.2019 06:14:27 | 06.10.00 | 32 | 43,1 | 25.07.2019 05:55:10 | 05.50.00 | 30 | -2,1 |
25.07.2019 06:18:20 | 06.10.00 | 32 | 42,9 | 25.07.2019 05:59:00 | 05.50.00 | 30 | -2,2 |
25.07.2019 06:22:11 | 06.20.00 | 33 | 43 | 25.07.2019 06:18:14 | 06.10.00 | 31 | -2 |
25.07.2019 06:26:05 | 06.20.00 | 33 | 43,1 | 25.07.2019 06:22:05 | 06.20.00 | 32 | -1,8 |
25.07.2019 06:29:58 | 06.20.00 | 33 | 43 | 25.07.2019 06:29:51 | 06.20.00 | 32 | -2 |
Thanks, grateful for every help.