CONDITIONAL FORMATTING FOR LOWEST VALUE HIGHLIGHTS

[DEEPAK UBHE]

I have cost comparison sheet in which I need to highlight lowest 1, 2 & 3 in particular row
But in conditional formatting I could highlight only Lowest 1 value, where I need to highlight Lowest 1, Lowest 2 & Lowest 3 in single row with different colour

At the same time I am not able to paste the same format for next line
Instead need to do the same process again for each line

Please help

 

[GraH]

Hi,
What about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Item	Qty	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount
2	Item1	1	15	1052	55	1357	56	1312	84	2321	87	2179	17	1294	39	2085	53	655	20	428
3	Item2	2	24	1377	95	284	16	1251	90	1290	62	1715	24	1675	77	2923	36	831	26	2433
4	Item3	3	18	2369	69	2643	22	1517	80	1573	56	2016	63	1750	55	2529	69	1512	78	1764
5	Item4	4	65	1887	26	2771	45	1764	49	332	87	579	19	2196	71	2470	12	1600	18	1616
6	Item5	4	75	1162	35	1617	24	2953	70	2164	26	1327	66	771	56	1113	80	1868	51	589
7	Item6	5	91	1539	52	1629	47	1875	60	855	63	301	92	73	85	2912	24	704	51	1309
8	Item7	6	68	1130	29	737	89	2642	54	2218	11	2431	23	2194	30	2916	21	2273	22	2392
9	Item8	7	41	2805	81	251	94	691	65	2676	39	883	18	2964	22	439	43	1972	10	1196
10	Item9	8	73	2588	97	2688	16	2188	27	744	55	1880	20	2700	11	2763	13	465	89	733
11	Item10	9	18	2704	97	912	18	2774	40	2593	61	774	92	1755	11	1367	86	2701	79	2653
12	Item11	10	95	2015	76	132	52	1146	30	2745	76	781	89	233	99	1338	96	2892	85	1891
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C12,E2:E12,G2:G12,I2:I12,K2:K12,M2:M12,O2:O12,Q2:Q12,S2:S12	Expression	=RateRank=3	text	NO
C2:C12,E2:E12,G2:G12,I2:I12,K2:K12,M2:M12,O2:O12,Q2:Q12,S2:S12	Expression	=RateRank=2	text	NO
C2:C12,E2:E12,G2:G12,I2:I12,K2:K12,M2:M12,O2:O12,Q2:Q12,S2:S12	Expression	=RateRank=1	text	NO

Name Manager entry: RateRank=RANK.EQ(Sheet1!A1,(Sheet1!$C1,Sheet1!$E1,Sheet1!$G1,Sheet1!$I1,Sheet1!$K1,Sheet1!$M1,Sheet1!$O1,Sheet1!$Q1,Sheet1!$S1),0)

 

[Eric W]

Another option:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3
4				A		B		C		D		E		F		G		H		I
5		Item	Qty	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount
6	1	Item1	1	34500	86250	34500	86250	35300	88250	35700	89250	36900	92250	36700	91750	36175	90438			36306	90765
7	2	Item2	2	34500	69000	34200	68400	35000	70000	35500	71000	36900	73800	36200	72400	36175	72350			36106	72212
8	3	Item3	3	1	2369	1	2643	1	1517	1	1573	2	2016	2	1750	3	2529
9	4	Item4	4	1	1887	2	2771	2	1764	2	332	3	579	3	2196	4	2470
10	5	Item5	4	1	1162	2	1617	3	2953	70	2164	26	1327	66	771	56	1113
11	6	Item6	5	1	1539	2	1629	3	1875	3	855	63	301	92	73	85	2912
12	7	Item7	6	3	1130	3	737	2	2642	2	2218	1	2431	23	2194	30	2916
13	8	Item8	7	30	2805	20	251	18	691	17	2676	16	883	15	2964	14	439			11
14	9	Item9	8					15	30
15	10	Item10	9			16	32							18	36
16	11	Item11	10	95	2015	76	132	52	1146	30	2745	76	781	89	233	99	1338			76
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:U16	Expression	=(D6>0)*(MOD(COLUMN(D6)-COLUMN($D6),2)=0)*(D6<=AGGREGATE(15,6,1/(1/$D6:$T6/(MOD(COLUMN($D6:$T6)-COLUMN($D6),2)=0)),1))	text	NO
D6:U16	Expression	=(D6>0)*(MOD(COLUMN(D6)-COLUMN($D6),2)=0)*(D6<=AGGREGATE(15,6,1/(1/$D6:$T6/(MOD(COLUMN($D6:$T6)-COLUMN($D6),2)=0)),2))	text	NO
D6:U16	Expression	=(D6>0)*(MOD(COLUMN(D6)-COLUMN($D6),2)=0)*(D6<=AGGREGATE(15,6,1/(1/$D6:$T6/(MOD(COLUMN($D6:$T6)-COLUMN($D6),2)=0)),3))	text	NO

 

[Eric W]

Far simpler version of my formulas, taking advantage of the headings:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3
4				A		B		C		D		E		F		G		H		I
5		Item	Qty	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount
6	1	Item1	1	34500	86250	34500	86250	35300	88250	35700	89250	36900	92250	36700	91750	36175	90438			36306	90765
7	2	Item2	2	34500	69000	34200	68400	35000	70000	35500	71000	36900	73800	36200	72400	36175	72350			36106	72212
8	3	Item3	3	1	2369	1	2643	1	1517	1	1573	2	2016	2	1750	3	2529
9	4	Item4	4	1	1887	2	2771	2	1764	2	332	3	579	3	2196	4	2470
10	5	Item5	4	1	1162	2	1617	3	2953	70	2164	26	1327	66	771	56	1113
11	6	Item6	5	1	1539	2	1629	3	1875	3	855	63	301	92	73	85	2912
12	7	Item7	6	3	1130	3	737	2	2642	2	2218	1	2431	23	2194	30	2916
13	8	Item8	7	30	2805	20	251	18	691	17	2676	16	883	15	2964	14	439			11
14	9	Item9	8					15	30
15	10	Item10	9			16	32							18	36
16	11	Item11	10	95	2015	76	132	52	1146	30	2745	76	781	89	233	99	1338			76
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:U16	Expression	=(D6>0)*(D$4<>"")*(D6<=SMALL(IF(($D$4:$T$4<>"")*($D6:$T6>0),$D6:$T6),1))	text	NO
D6:U16	Expression	=(D6>0)*(D$4<>"")*(D6<=SMALL(IF(($D$4:$T$4<>"")*($D6:$T6>0),$D6:$T6),2))	text	NO
D6:U16	Expression	=(D6>0)*(D$4<>"")*(D6<=SMALL(IF(($D$4:$T$4<>"")*($D6:$T6>0),$D6:$T6),3))	text	NO

The numbers don't match your sample sheet. (I didn't want to type them all in - see the XL2BB tool.) But I adjusted the values to show what happens when there are ties.

 

[DEEPAK UBHE]

Very sorry to say but this is not working
Is it possible to share the sheet here
So that i can explain my problem

 

[DEEPAK UBHE]

Hi,
What about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Item	Qty	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount	Rate	Amount
2	Item1	1	15	1052	55	1357	56	1312	84	2321	87	2179	17	1294	39	2085	53	655	20	428
3	Item2	2	24	1377	95	284	16	1251	90	1290	62	1715	24	1675	77	2923	36	831	26	2433
4	Item3	3	18	2369	69	2643	22	1517	80	1573	56	2016	63	1750	55	2529	69	1512	78	1764
5	Item4	4	65	1887	26	2771	45	1764	49	332	87	579	19	2196	71	2470	12	1600	18	1616
6	Item5	4	75	1162	35	1617	24	2953	70	2164	26	1327	66	771	56	1113	80	1868	51	589
7	Item6	5	91	1539	52	1629	47	1875	60	855	63	301	92	73	85	2912	24	704	51	1309
8	Item7	6	68	1130	29	737	89	2642	54	2218	11	2431	23	2194	30	2916	21	2273	22	2392
9	Item8	7	41	2805	81	251	94	691	65	2676	39	883	18	2964	22	439	43	1972	10	1196
10	Item9	8	73	2588	97	2688	16	2188	27	744	55	1880	20	2700	11	2763	13	465	89	733
11	Item10	9	18	2704	97	912	18	2774	40	2593	61	774	92	1755	11	1367	86	2701	79	2653
12	Item11	10	95	2015	76	132	52	1146	30	2745	76	781	89	233	99	1338	96	2892	85	1891
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C12,E2:E12,G2:G12,I2:I12,K2:K12,M2:M12,O2:O12,Q2:Q12,S2:S12	Expression	=RateRank=3	text	NO
C2:C12,E2:E12,G2:G12,I2:I12,K2:K12,M2:M12,O2:O12,Q2:Q12,S2:S12	Expression	=RateRank=2	text	NO
C2:C12,E2:E12,G2:G12,I2:I12,K2:K12,M2:M12,O2:O12,Q2:Q12,S2:S12	Expression	=RateRank=1	text	NO

Name Manager entry: RateRank=RANK.EQ(Sheet1!A1,(Sheet1!$C1,Sheet1!$E1,Sheet1!$G1,Sheet1!$I1,Sheet1!$K1,Sheet1!$M1,Sheet1!$O1,Sheet1!$Q1,Sheet1!$S1),0)

not working
If i could share my sheet here to make you understand better

 

[GraH]

You can share via the XL2BB add-on.