A Different way to Calculate Ratios using GDC & or MIN ?

[StrawberryDreams]

Simply put , what is the best formula to write Ratios for both 2 numbers, and 3 or more numbers ?

I've tried using GDC and MIN on both 2 and 3 number ratios and both formulas can be broken. They either don't properly get the lowest number as 1, or they show DIV/O errors, or GDC panics when using decimals. Is there a bullet proof formula so I can get any possibility to work ?

Is there a better way to do what I want ? You can see in the example the yellow cells are the "problem cells."

Basic data calculator test 9.4.xlsm
	A	B	C	D	E	F	G
1	Value 1	Value 2	Value 3	Ratio using GDC ( Value 1 & 2 )	Ratio using MIN ( Value 1 & 2 )	Ratio formula for three or more numbers ? GCD	Ratio formula for three or more numbers ? Using MIN
2	9	23	2	9 : 23	1 : 2.56	9 : 23 : 2	4.5 : 11.5 : 1
3	23	9	2	23 : 9	2.56 : 1	23 : 9 : 2	11.5 : 4.5 : 1
4	27	3	2	9 : 1	9 : 1	27 : 3 : 2	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9	1 : 9 : 3	1 : 9 : 3
6	4	0.5	2	1 : 0.125	8 : 1	2 : 0.25 : 1	8 : 1 : 4
7	0.5	4	0	0.125 : 1	1 : 8	0.125 : 1 : 0	#DIV/0!
8	0.0089	0.23	0.1234	#DIV/0!	1 : 25.84	#DIV/0!	1 : 25.84 : 13.87
9	0	0	0	Nil	#DIV/0!	Nil	#DIV/0!
10	0	0	1	Nil	#DIV/0!	Nil	#DIV/0!
11	0	1	0	0 : 1	#DIV/0!	0 : 1 : 0	#DIV/0!
12	1	0	0	1 : 0	#DIV/0!	1 : 0	#DIV/0!
13	1	0	1	1 : 0	#DIV/0!	1 : 0	#DIV/0!
14	0	1	1	0 : 1	#DIV/0!	0 : 1 : 1	#DIV/0!
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(B2=0,IF(A2=0,"Nil",A2&" : "&0),A2/GCD(A2:B2)&" : "&B2/GCD(A2:B2))
E2:E14	E2	=ROUND(A2/MIN(A2,B2),2) & " : " & ROUND(B2/MIN(A2,B2),2)
F2:F14	F2	=IF(B2=0,IF(A2=0,"Nil",A2&" : "&0),A2/GCD(A2:C2)&" : "&B2/GCD(A2:C2)&" : "&C2/GCD(A2:C2))
G2:G14	G2	=ROUND(A2/MIN(A2,B2,C2),2) & " : " & ROUND(B2/MIN(A2,B2,C2),2) & " : " & ROUND(C2/MIN(A2,B2,C2),2)

 

[StrawberryDreams]

Try like

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 #	3 #
2	3	0	1	3 : 0	3 : 0 : 1
3	3	1	0	3 : 1	3 : 1 : 0
4	0	2	3	0 : 2	0 : 2 : 3
5	3	0	0	3 : 0	3 : 0 : 0
6	0	0	3	Nil	0 : 0 : 3
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,1,A2:B2)),2)))
E2:E6	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/MIN(IF(A2:C2=0,1,A2:C2)),2)))

Seems the code works great when it's just for the 2's . I tried for comparison putting up my countif formula which does the same as yours,
But for the 3's....Still its having issues when there is a Zero and the two other numbers are greater then 1 it seems.

Basic data calculator test 9.4.xlsm
	A	B	C	D	E	F	G	H
1	Value 1	Value 2	Value 3	2's - my count if formula	3's - my count if Formula	Your newest 2's	Your newest 3's	Should be for the 3's
2	3	0	1	3 : 0	3 : 0 : 1	3 : 0	3 : 0 : 1
3	3	1	0	3 : 1	3 : 1 : 0	3 : 1	3 : 1 : 0
4	1	3	0	1 : 3	1 : 3 : 0	1 : 3	1 : 3 : 0
5	0	2	3	0 : 2	0 : 2 : 3	0 : 2	0 : 2 : 3	0 : 1 : 1.5
6	3	2	0	1.5 : 1	3 : 2 : 0	1.5 : 1	3 : 2 : 0	1.5 : 1 : 0
7	3	0	2	3 : 0	3 : 0 : 2	3 : 0	3 : 0 : 2	1.5 : 0 : 1
8	0	0	3	0 : 0	0 : 0 : 3	Nil	0 : 0 : 3
9	2	4	8	1 : 2	1 : 2 : 4	1 : 2	1 : 2 : 4
10	0	4	8	0 : 4	0 : 4 : 8	0 : 4	0 : 4 : 8	0 :1 : 2
11	1	0	8	1 : 0	1 : 0 : 8	1 : 0	1 : 0 : 8
12	4	3	2	1.33 : 1	2 : 1.5 : 1	1.33 : 1	2 : 1.5 : 1
13	4	0	2	4 : 0	4 : 0 : 2	4 : 0	4 : 0 : 2	2 : 0 : 1
Sheet3

Cell Formulas
Range	Formula
D2:D13	D2	=IF(COUNTIF(A2:B2,"=0"), A2 & " : " & B2, ROUND(A2/MIN(A2,B2),2) & " : " & ROUND(B2/MIN(A2,B2),2) )
E2:E13	E2	=IF(COUNTIF(A2:C2,"=0"), A2 & " : " & B2 & " : " & C2, ROUND(A2/MIN(A2,B2,C2),2) & " : " & ROUND(B2/MIN(A2,B2,C2),2) & " : " & ROUND(C2/MIN(A2,B2,C2),2) )
F2:F13	F2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,1,A2:B2)),2)))
G2:G13	G2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/MIN(IF(A2:C2=0,1,A2:C2)),2)))

 

[Snakehips]

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	Your newest 2's	New for the 3's
2	3	0	1	3 : 0	3 : 0 : 1
3	3	1	0	3 : 1	3 : 1 : 0
4	1	3	0	1 : 3	1 : 3 : 0
5	0	2	3	0 : 2	0 : 1 : 1.5
6	3	2	0	1.5 : 1	1.5 : 1 : 0
7	3	0	2	3 : 0	1.5 : 0 : 1
8	0	0	3	Nil	0 : 0 : 1
9	2	4	8	1 : 2	1 : 2 : 4
10	0	4	8	0 : 4	0 : 1 : 2
11	1	0	8	1 : 0	1 : 0 : 8
12	4	3	2	1.33 : 1	2 : 1.5 : 1
13	4	0	2	4 : 0	2 : 0 : 1
Sheet3

Cell Formulas
Range	Formula
D2:D13	D2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,1,A2:B2)),2)))
E2:E13	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/SMALL(A2:C2,(COUNTIF(A2:C2,0)+1)),2)))

Good Night!

 

[StrawberryDreams]

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	Your newest 2's	New for the 3's
2	3	0	1	3 : 0	3 : 0 : 1
3	3	1	0	3 : 1	3 : 1 : 0
4	1	3	0	1 : 3	1 : 3 : 0
5	0	2	3	0 : 2	0 : 1 : 1.5
6	3	2	0	1.5 : 1	1.5 : 1 : 0
7	3	0	2	3 : 0	1.5 : 0 : 1
8	0	0	3	Nil	0 : 0 : 1
9	2	4	8	1 : 2	1 : 2 : 4
10	0	4	8	0 : 4	0 : 1 : 2
11	1	0	8	1 : 0	1 : 0 : 8
12	4	3	2	1.33 : 1	2 : 1.5 : 1
13	4	0	2	4 : 0	2 : 0 : 1
Sheet3

Cell Formulas
Range	Formula
D2:D13	D2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,1,A2:B2)),2)))
E2:E13	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/SMALL(A2:C2,(COUNTIF(A2:C2,0)+1)),2)))

Good Night!

thanks again for all your input and time !