Conditionally formatting with if statement

Showhoff

New Member
Joined
Mar 1, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.

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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,245
Office Version
  1. 365
Platform
  1. Windows
With CF you can use

+Fluff.xlsm
ABCD
1Date timeTime
225.07.2019 05:47:2805:40:0029-1,3
325.07.2019 05:51:2205:50:0030-1,6
425.07.2019 05:55:1005:50:0030-2,1
525.07.2019 05:59:0005:50:0030-2,2
625.07.2019 06:18:1406:10:0031-2
725.07.2019 06:22:0506:20:0032-1,8
825.07.2019 06:29:5106:20:0032-2
List
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D8Expression=$B2>$B1+1/144textNO
 

Watch MrExcel Video

Forum statistics

Threads
1,114,257
Messages
5,546,806
Members
410,759
Latest member
Bufnercash
Top