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]

Cell F10 Should also be yellow and rather show instead 0 : 0 : 1

I think the cleanest looking ratio is when the lowest number in the range is displayed as "1", and the other numbers' calculated values are divided by the original lowest number. Unless there are zeros which then the values should be written as a zero / and or the other non zero values can be simplified to their lowest ratio along with placing the zero for the values that have zero.

8 0 2 = 4 0 1

 

[Snakehips]

@StrawberryDreams Doe this help?

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 Numbers	3 Numbers
2	9	23	2	1 : 2.56	4.5 : 11.5 : 1
3	23	9	2	2.56 : 1	11.5 : 4.5 : 1
4	27	3	2	9 : 1	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9 : 3
6	4	0.5	2	8 : 1	8 : 1 : 4
7	0.5	4	0	1 : 8	1 : 8 : 0
8	0.0089	0.23	0.1234	1 : 25.84	1 : 25.84 : 13.87
9	0	0	0	Nil	Nil
10	0	0	1	Nil	0 : 0 : 1
11	0	1	0	0 : 1	0 : 1 : 0
12	1	0	0	1 : 0	1 : 0 : 0
13	1	0	1	1 : 0	1 : 0 : 1
14	0	1	1	0 : 1	0 : 1 : 1
Sheet1

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

 

[StrawberryDreams]

@StrawberryDreams Doe this help?

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 Numbers	3 Numbers
2	9	23	2	1 : 2.56	4.5 : 11.5 : 1
3	23	9	2	2.56 : 1	11.5 : 4.5 : 1
4	27	3	2	9 : 1	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9 : 3
6	4	0.5	2	8 : 1	8 : 1 : 4
7	0.5	4	0	1 : 8	1 : 8 : 0
8	0.0089	0.23	0.1234	1 : 25.84	1 : 25.84 : 13.87
9	0	0	0	Nil	Nil
10	0	0	1	Nil	0 : 0 : 1
11	0	1	0	0 : 1	0 : 1 : 0
12	1	0	0	1 : 0	1 : 0 : 0
13	1	0	1	1 : 0	1 : 0 : 1
14	0	1	1	0 : 1	0 : 1 : 1
Sheet1

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

This looks squeaky clean! Let me play with it and see if anything pops up - but tbh looks bullet proof ! Cheers

 

[StrawberryDreams]

@StrawberryDreams Doe this help?

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 Numbers	3 Numbers
2	9	23	2	1 : 2.56	4.5 : 11.5 : 1
3	23	9	2	2.56 : 1	11.5 : 4.5 : 1
4	27	3	2	9 : 1	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9 : 3
6	4	0.5	2	8 : 1	8 : 1 : 4
7	0.5	4	0	1 : 8	1 : 8 : 0
8	0.0089	0.23	0.1234	1 : 25.84	1 : 25.84 : 13.87
9	0	0	0	Nil	Nil
10	0	0	1	Nil	0 : 0 : 1
11	0	1	0	0 : 1	0 : 1 : 0
12	1	0	0	1 : 0	1 : 0 : 0
13	1	0	1	1 : 0	1 : 0 : 1
14	0	1	1	0 : 1	0 : 1 : 1
Sheet1

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

Hi just thought of a question, If my Values are not in a nice horizontal or vertical range ( in different cells that are separate from one another ) will this still work if I just add each value separated by a comma , and for the sum formula just add + inbetween each cell value ?

 

[Snakehips]

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 Numbers	3 Numbers
2	9	23	2	1 : 2.56	4.5 : 11.5 : 1
3	23	9	2	2.56 : 1	11.5 : 4.5 : 1
4	27	3	2	9 : 1	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9 : 3
6	4	0.5	2	8 : 1	8 : 1 : 4
7	0.5	4	0	1 : 8	1 : 8 : 0
8	0.0089	0.23	0.1234	1 : 25.84	1 : 25.84 : 13.87
9	0	0	0	Nil	Nil
10	0	0	1	Nil	0 : 0 : 1
11	0	1	0	0 : 1	0 : 1 : 0
12	1	0	0	1 : 0	1 : 0 : 0
13	1	0	1	1 : 0	1 : 0 : 1
14	0	1	1	0 : 1	0 : 1 : 1
15				>>>>>>>>	3 : 9 : 1
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,999,A2:B2)),2)))
E2:E14	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/MIN(IF(A2:C2=0,999,A2:C2)),2)))
E15	E15	=LET(arry,HSTACK(B3,B5,A5),IF(SUM(arry)=0,"Nil",TEXTJOIN(" : ",,ROUND(arry/MIN(IF(arry=0,999,arry)),2))))

formula below in E15 for an idea on that scenario.

 

[StrawberryDreams]

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 Numbers	3 Numbers
2	9	23	2	1 : 2.56	4.5 : 11.5 : 1
3	23	9	2	2.56 : 1	11.5 : 4.5 : 1
4	27	3	2	9 : 1	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9 : 3
6	4	0.5	2	8 : 1	8 : 1 : 4
7	0.5	4	0	1 : 8	1 : 8 : 0
8	0.0089	0.23	0.1234	1 : 25.84	1 : 25.84 : 13.87
9	0	0	0	Nil	Nil
10	0	0	1	Nil	0 : 0 : 1
11	0	1	0	0 : 1	0 : 1 : 0
12	1	0	0	1 : 0	1 : 0 : 0
13	1	0	1	1 : 0	1 : 0 : 1
14	0	1	1	0 : 1	0 : 1 : 1
15				>>>>>>>>	3 : 9 : 1
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,999,A2:B2)),2)))
E2:E14	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/MIN(IF(A2:C2=0,999,A2:C2)),2)))
E15	E15	=LET(arry,HSTACK(B3,B5,A5),IF(SUM(arry)=0,"Nil",TEXTJOIN(" : ",,ROUND(arry/MIN(IF(arry=0,999,arry)),2))))

formula below in E15 for an idea on that scenario.

Thanks I'll look at that case tooo... I found something with your original formula which calculates values with zero not the way Im hoping can be done. I provided a column with the errors showing how I would like them displayed if possible while including the solutions for the other possibilities.

Basic data calculator test 9.4.xlsm
	A	B	C	D	E	F	G
1	Value 1	Value 2	Value 3	2 #	3 #	#2 want	#3 want
2	3	0	1	1 : 0	3 : 0 : 1	3: 0
3	3	1	0	3 : 1	3 : 1 : 0
4	0	2	3	0 : 1	0 : 1 : 1.5	0 : 2
5	3	0	0	1 : 0	1 : 0 : 0	3 : 0	3: 0 : 0
6	0	0	3	Nil	0 : 0 : 1		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,999,A2:B2)),2)))
E2:E6	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/MIN(IF(A2:C2=0,999,A2:C2)),2)))

 

[StrawberryDreams]

Reason Why I want the "3" to be 3 and not 1 mathematically is to describe Parts
Example 3 eggs 0 flour. 0 sugar. I still have 3 parts eggs to zero other parts not 1:0:0

but if it was 3 eggs 2 flour 4 sugar - then the ratio would be 1.5 : 1 : 2 parts of ingredients
Cheers

 

[StrawberryDreams]

Book1
	A	B	C	D	E
1	Value 1	Value 2	Value 3	2 Numbers	3 Numbers
2	9	23	2	1 : 2.56	4.5 : 11.5 : 1
3	23	9	2	2.56 : 1	11.5 : 4.5 : 1
4	27	3	2	9 : 1	13.5 : 1.5 : 1
5	3	27	9	1 : 9	1 : 9 : 3
6	4	0.5	2	8 : 1	8 : 1 : 4
7	0.5	4	0	1 : 8	1 : 8 : 0
8	0.0089	0.23	0.1234	1 : 25.84	1 : 25.84 : 13.87
9	0	0	0	Nil	Nil
10	0	0	1	Nil	0 : 0 : 1
11	0	1	0	0 : 1	0 : 1 : 0
12	1	0	0	1 : 0	1 : 0 : 0
13	1	0	1	1 : 0	1 : 0 : 1
14	0	1	1	0 : 1	0 : 1 : 1
15				>>>>>>>>	3 : 9 : 1
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=IF(SUM(A2:B2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:B2/MIN(IF(A2:B2=0,999,A2:B2)),2)))
E2:E14	E2	=IF(SUM(A2:C2)=0,"Nil",TEXTJOIN(" : ",,ROUND(A2:C2/MIN(IF(A2:C2=0,999,A2:C2)),2)))
E15	E15	=LET(arry,HSTACK(B3,B5,A5),IF(SUM(arry)=0,"Nil",TEXTJOIN(" : ",,ROUND(arry/MIN(IF(arry=0,999,arry)),2))))

formula below in E15 for an idea on that scenario.

Maybe Nesting another If error type statement with the haystack formula to define how to handle certain zero cases. I did this before using just MIN, but it became a puzzle trying to nest multiple Ifs with iferrrors to try and account for every possibility, so not real easy to read if you have to come back to it in the future and understand what is it doing.

 

[Snakehips]

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)))