Individual 3 color scale for each row

[giz0r]

Hey guys

I've been struggling with this for a long time, and given up several times. I decided to try again, only to find this forum and hoping you can help me.

I have a list of numbers in varying amount of rows in 7 columns. I want to do 3 color scale for each individual row, highligting lowest value (above 0) with green, median with orange, highest value (below 99.999.999) with red.
Ideally I want all values colored (except 0 and 99.999.999 which should be either uncolored or grey) but only 1 in green.

I've attached an image of how I want the end result to be, I just can't figure out the conditional formatting and/or formula to do this.

 

[Sektor]

Well, is there any explanation why I should color B4 and D4 in brown and C4 and E4 in red? Is there any criteria?

 

[etaf]

Why are you showing 2 values RED in a row ???

green
=AND(A1<>"",A1=MINIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))
red
=AND(A1<>"",A1=MAXIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))
Grey
=AND(A1<>"",OR(A1=99999999,A1=0))
Orange
=A1=MEDIAN(IF($A1:$G1<>0,$A1:$G1)) -inludes 99999999 but not zero - IGNORE for now , if you exclude the Bottom number and the top number then the midean is the same as if included - I THINK
so
=A1=MEDIAN($A1:$G1) should work - i'll test

Book1
	A	B	C	D	E	F	G	H
1	99999999	23	45	14444	666666	0	0	0
2	8912	45	6	0	99999999	9	0	2
3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:H3	Expression	=A1=MEDIAN(IF($A1:$G1<>0,$A1:$G1))	text	NO
A1:H3	Expression	=AND(A1<>"",OR(A1=99999999,A1=0))	text	YES
A1:H3	Expression	=AND(A1<>"",A1=MAXIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))	text	NO
A1:H4	Expression	=AND(A1<>"",A1=MINIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))	text	YES

 

[giz0r]

Thanks for your replies. In a perfect scenario, I want lowest number (above 0) to green, second lowest should be orange, all higher numbers (except 99.999.999) should be grey.

I would settle for just 3, green lowest, orange second lowest, red thirst lowest

@etaf I tried your formulas, but can't seem to get them working exactly.

 

[etaf]

ok, so NOT the median
and you want
Minimum and 2nd minimum excluding 0
ALL higher numbers then what - the 2nd lowest to be grey?

you dhow in your example 0 and 99 999 999 to be grey

also did not answer why the 2 red

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A1:G100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(A1<>"",A1=MINIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))

Format [Number, Font, Border, Fill] - use a fill of Green
choose the format you would like to apply when the condition is true
OK >> OK

 

[giz0r]

Sorry, I realize misstyped something before.
LOWEST number (above 0) should be GREEN
2nd LOWEST should be ORANGE
All higher numbers (except 99.999.999) should be RED

0 and 99.999.999 should be grey.

 

[etaf]

Green
=AND(A1<>"",A1=MINIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))
Red
=AND(A1>SMALL(IF($A1:$H1>0,$A1:$H1),2),A1<>99999999)
Orange
=A1=SMALL(IF($A1:$H1>0,$A1:$H1),2)
Grey
=AND(A1<>"",OR(A1=99999999,A1=0))

Book1
	A	B	C	D	E	F	G
1	99999999	23	45	14444	666666	0	0
2	8912	45	6	0	99999999	9	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:G4	Expression	=AND(A1<>"",OR(A1=99999999,A1=0))	text	YES
A1:G4	Expression	=AND(A1<>"",A1=MINIFS($A1:$G1,$A1:$G1,"<>"&0,$A1:$G1,"<>"&99999999))	text	NO
A1:G4	Expression	=A1=SMALL(IF($A1:$H1>0,$A1:$H1),2)	text	YES
A1:G4	Expression	=AND(A1>SMALL(IF($A1:$H1>0,$A1:$H1),2),A1<>99999999)	text	NO

 

[giz0r]

That works! Fantastic! Thank you

 

[etaf]

you are welcome