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 ? I know my formula can be improved.

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)

 

[Fluff]

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

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.