Find MIN/MAX Value with conditional formatting in given block of DATA - then repeat

[Igorzjeh]

I've been wraping my head around this one for couple of days, might be that I am just looking at this in a wrong way.

Here is my problem:
- i have data blocks that are divided by empty cells in columns G and H. Column G has to show MAX value in green and Column H needs to show LOWEST value in red - and then repeat this for each block of data
- Perhaps the helper column can be Column C - where you can see when the data blocks are separated by letter S or L + empty cells
- other thing to point out is that columns G and H have formula inside which deletes the data in those columns if there is an X in Column B for that row - hence the empty cells that divide the data

Here is a small sample of data that usually has 100k+ rows.

I hope you guys can help, tnx!

cjelovit ott.xlsx
	A	B	C	D	E	F	G	H
1	time	IZBORNIK	SIDE	Unos BITNO	1 TEST	2 TEST	1 % test	2% test
2	2021-12-01T21:45:00+01:00	X	S	0,207636	0,000846	-0,00149
3	2021-12-01T22:00:00+01:00		S	0,207636	0,001516	-0,00044	-341,74%	-46797,08%
4	2021-12-01T22:15:00+01:00		S	0,207636	0,001646	-0,00046	-355,04%	-44778,64%
5	2021-12-01T22:30:00+01:00		S	0,207636	0,001836	0,000536	342,40%	38716,09%
6	2021-12-02T08:45:00+01:00		S	0,207636	0,001736	0,001066	162,83%	19472,51%
7	2021-12-02T09:00:00+01:00		S	0,207636	0,001826	0,000896	203,76%	23165,81%
8	2021-12-02T09:15:00+01:00		S	0,207636	0,001586	0,000766	207,01%	27095,78%
9	2021-12-02T09:30:00+01:00	X	L	0,212779	0,008221	-0,00646
10	2021-12-03T12:15:00+01:00		L	0,212779	-0,00225	-0,00295	76,26%	-7216,26%
11	2021-12-03T14:45:00+01:00		L	0,212779	-0,0025	-0,00367	68,11%	-5799,99%
12	2021-12-03T15:00:00+01:00		L	0,212779	-0,00279	-0,00368	75,81%	-5784,23%
13	2021-12-03T15:15:00+01:00		L	0,212779	-0,00334	-0,00484	69,00%	-4397,52%
14	2021-12-03T15:30:00+01:00		L	0,212779	-0,00338	-0,00503	67,19%	-4231,37%
15	2021-12-03T15:45:00+01:00		L	0,212779	-0,00443	-0,00632	70,09%	-3367,50%
16	2021-12-03T16:00:00+01:00	X	S	0,206508	0,001298	-0,00082
17	2021-12-03T16:15:00+01:00		S	0,206508	0,000888	-0,00046	-192,01%	-44669,05%
18	2021-12-06T20:15:00+01:00		S	0,206508	0,033138	0,031718	104,48%	651,08%
19	2021-12-06T21:30:00+01:00		S	0,206508	0,032158	0,030528	105,34%	676,46%
20	2021-12-06T21:45:00+01:00		S	0,206508	0,033138	0,030608	108,27%	674,69%
21	2021-12-06T22:00:00+01:00		S	0,206508	0,033238	0,032128	103,45%	642,77%
22	2021-12-06T22:15:00+01:00		S	0,206508	0,032998	0,031718	104,04%	651,08%
23	2021-12-06T22:30:00+01:00		S	0,206508	0,032038	0,030598	104,71%	674,91%
24	2021-12-06T22:45:00+01:00	X	L	0,176049	0,003251	-0,00024
25	2021-12-06T23:00:00+01:00		L	0,176049	0,003671	0,000971	378,07%	18131,01%
26	2021-12-09T14:45:00+01:00		L	0,176049	0,001421	0,000611	232,57%	28814,06%
27	2021-12-09T15:00:00+01:00		L	0,176049	0,001861	0,000941	197,77%	18709,05%
28	2021-12-09T15:15:00+01:00		L	0,176049	0,001761	-0,00031	-569,87%	-56970,66%
29	2021-12-09T15:30:00+01:00		L	0,176049	0,000181	-0,00155	-11,68%	-11365,21%
30	2021-12-09T15:45:00+01:00		L	0,176049	-0,00117	-0,00295	39,64%	-5969,75%
31	2021-12-09T16:00:00+01:00		L	0,176049	-0,00154	-0,00362	42,53%	-4864,55%
32	2021-12-09T16:15:00+01:00	X	S	0,171654	0,001164	-0,00296
33	2021-12-09T16:30:00+01:00		S	0,171654	0,001344	-0,00056	-241,71%	-30871,41%
34	2021-12-09T16:45:00+01:00		S	0,171654	-0,00034	-0,00217	15,51%	-7924,82%
35	2021-12-09T17:00:00+01:00		S	0,171654	-0,00029	-0,00154	18,62%	-11175,18%
36	2021-12-09T17:15:00+01:00		S	0,171654	-0,00055	-0,00248	22,05%	-6932,63%
37	2021-12-09T17:30:00+01:00		S	0,171654	-0,00185	-0,00322	57,40%	-5337,45%
38	2021-12-09T17:45:00+01:00		S	0,171654	-0,00236	-0,00407	57,94%	-4221,66%
39	2021-12-09T18:00:00+01:00		S	0,171654	-0,00356	-0,00715	49,76%	-2402,09%
40	2021-12-09T18:15:00+01:00		S	0,171654	-0,00461	-0,01125	40,96%	-1526,35%
41	2021-12-09T18:30:00+01:00		S	0,171654	-0,00332	-0,00539	61,57%	-3187,02%
cjelovit ott

Cell Formulas
Range	Formula
G2:G41	G2	=IF(B2=ISBLANK(FALSE),E2/F2,"")
H2:H41	H2	=IF(B2=ISBLANK(FALSE),D2/F2,"")

 

[DanteAmor]

Hi and welcome to MrExcel board!

Note:
Change in formulas 42 to the last cell with data (+1) on your sheet.
For the maximum format:

=G2=MAX(OFFSET(G$1,LARGE(IF(G$2:G2="",ROW(G$2:G2)),1),0,SMALL(IF(G3:G$42="",ROW(G3:G$42)),1)-LARGE(IF(G$2:G2="",ROW(G$2:G2)),1)))

Applies to:
=$G$2:$G$42

For the minimum format:

=H2=MIN(OFFSET(H$1,LARGE(IF(H$2:H2="",ROW(H$2:H2)),1),0,SMALL(IF(H3:H$42="",ROW(H3:H$42)),1)-LARGE(IF(H$2:H2="",ROW(H$2:H2)),1)))

Applies to:
=$H$2:$H$42


The formulas are array formulas, you may have problems with the performance of your sheet.
If the response is very slow, then you should find another formula or maybe a VBA solution.