New Function: Creating a ratio using a range.

[C_Leam]

I have successfully created a ratio using the following code below:

Function RATIO(num1, num2) As Variant

With Application
RATIO = (num1 / WorksheetFunction.Gcd(num1, num2)) & ":" & (num2 / WorksheetFunction.Gcd(num1, num2))
End With

End Function

Result returns as such:

What I would like to do now is take it further and be able to take it further and allow it to do a ratio within a range. End result should look like this:

I know it means starting the code off with Function RATIO(range) As Variant. Then when I enter the formula I'll need to have one line for the first cell in the range and then a loop for all the other cells so they start with ":" followed by the number.

Any help please?

 

[Peter_SSs]

Welcome to the MrExcel board!

Why not just use worksheet functions directly?

22 09 28.xlsm
	A	B	C	D	E	F	G
1	40		10:3		40		20:6:2:1
2	12				12
3					4
4					2
Ratios

Cell Formulas
Range	Formula
C1	C1	=TEXTJOIN(":",,A1:A2/GCD(A1:A2))
G1	G1	=TEXTJOIN(":",,E1:E4/GCD(E1:E4))

 

[C_Leam]

Welcome to the MrExcel board!

Why not just use worksheet functions directly?

22 09 28.xlsm
	A	B	C	D	E	F	G
1	40		10:3		40		20:6:2:1
2	12				12
3					4
4					2
Ratios

Cell Formulas
Range	Formula
C1	C1	=TEXTJOIN(":",,A1:A2/GCD(A1:A2))
G1	G1	=TEXTJOIN(":",,E1:E4/GCD(E1:E4))

Not bad. First time I've used TEXTJOIN.
I like it, but I want to put in my own functions to save typing and time. I've done one for calculating an average without the highest and lowest values which I call an Olympic Average:
Function OLYAVG(range) As Variant
'Caculate average without min and max
With Application
OLYAVG = (.Sum(range) - .Max(range) - .Min(range)) / (.Count(range) - 2)
End With
End Function

I'll see if I can put TEXTJOIN into a VBA coding.

Cheers.

 

[Peter_SSs]

Then what about

Function RATIOS(r As Range) As String
  RATIOS = Evaluate(Replace("textjoin("":"",,#/GCD(#))", "#", r.Address))
End Function

C_Leam.xlsm
	E	F	G
1	40		20:6:2:1
2	12
3	4
4	2
Ratios

Cell Formulas
Range	Formula
G1	G1	=RATIOS(E1:E4)

 

[C_Leam]

Then what about

Function RATIOS(r As Range) As String
  RATIOS = Evaluate(Replace("textjoin("":"",,#/GCD(#))", "#", r.Address))
End Function

C_Leam.xlsm
	E	F	G
1	40		20:6:2:1
2	12
3	4
4	2
Ratios

Cell Formulas
Range	Formula
G1	G1	=RATIOS(E1:E4)

Perfect.

Thanks Peter

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.