# Conditionally formatting with if statement

#### Showhoff

##### New Member
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.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
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

Replies
2
Views
68
Replies
8
Views
102
Replies
1
Views
74
Replies
8
Views
258
Replies
1
Views
50