Need aid creating a User-defined FUNCTION to format a number

[WuAhUuU]

I need help creating a user-defined function for the simple purpose of formating numbers.

Example
I have the following number:
119.50
I want the function to format the number to the following form:
119-16 (16/32 is 0.5 in the decimal, so the base for all calculations will be 32.

Another example:
I have the following number:
119.171875
I want the function to format the number to the following form:
119-05 + (the plus just says that 5.5 should be divided by 32)

The reason I want this is that if the number reaches a certain decimal it just becomes to long and ugly on sight.

My basic train of thought was to separate the integer from the decimal. And then work my way through the decimal.
1st got the decimal and multiplied by 32 to get the whole number from the decimal in the form of 0.25-31.75 (if the decimal from the decimal leftover is 0.25 =1/4, 0.5 = +, 0.75 = 3/4). So a number such as 5.75 would look like this -05 3/4. After that I simply put together many CONCATENATE functions to join the whole number and the formatted decimal. Need help in creating such function.

I have a huge code, but I actually want to create a function to automatically format the number and use less memory on the sheet.

Here is the code but worthless I might add:

=CONCATENATE(INT(O23),"-",IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))," ",IF(((O23-INT(O23))*32)-IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))=0.25,"1/4",IF(((O23-INT(O23))*32)-IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))=0.5,"+",IF(((O23-INT(O23))*32)-IF(INT(((O23-INT(O23))*32))<10,CONCATENATE(0,INT(((O23-INT(O23))*32))),INT(((O23-INT(O23))*32)))=0.75,"3/4",""))))

 

[jasonb75]

Is there any reason why you can't just change the cell format to fraction?

Something like # ?/? or #- ?/32

 

[WuAhUuU]

The reason is that it is a format used by the financial markets. So /32 will not work for all decimals.

Example:
119-16 = 119.5 (in decimal form)
119-16+ = 119.515625 (in decimal form)
119-16 3/4 = 119.5234375 (in decimal form)

Formatting everything to /32 will not give me the right market pratice format.

Thank you

 

[WuAhUuU]

I actually started to do something like this:

Public Function format32(number1 As Variant) As String
Dim format As Integer
Dim number As Integer
Dim decimal2 As String
format = 32
number = number1
Select Case decimal2
    Case 0.25
        decimal2 = " 1/4"
    Case 0.5
        decimal2 = " +"
    Case 0.75
        decimal2 = " 3/4"
End Select
format32 = number
End Function

 

[jasonb75]

I'm assuming that your concatenate formula is giving the desired results, if that can be shortened and made more efficient without compromising the results, would that be acceptable, or would you still prefer the UDF option?

 

[WuAhUuU]

I would prefer a UDF since it uses less memory and also can be transfered to other spreadsheets.

Thank you

 

[WuAhUuU]

The above function is still not properly calibrated since I am looking for a a definition for a variable similar to the =INT() funtion. This funtion get the integer even if the decimal is 0.99999999.

So =INT(119.9999999), I get the integer 119. That is what I need.

Here is more code:

Public Function format32(number1 As Variant) As String
Dim format1 As Integer
Dim number As Integer
Dim decimal3 As String
format1 = 32
number = number1
decimal1 = number1 - number
decimal2 = decimal1 * format1
    Select Case decimal3
        Case 0.25
            decimal3 = " 1/4"
        Case 0.5
            decimal3 = " +"
        Case 0.75
            decimal3 = " 3/4"
    End Select
format32 = number
End Function

 

[jasonb75]

I've done a bit of tweaking with the formula version before trying to code anything, not sure that I fully understand how the criteria for 16+ or 16 3/4 works, but think I got the general idea of it.

Please test this formula with some of your figures to verify the results, if this is correct then I'll have a go at coding it for you.

=SUBSTITUTE(INT(O23)&"- "&TEXT((MOD(O23,1)*128)/4,"# ?/?"),"1/2","+")

edit: no, there's not a big chunk of formula missing, it really is that short

 

[WuAhUuU]

Found the solution to the Int() problem. Here is the code continued:

Public Function format32(number1 As Variant) As String
Dim format1 As Integer
Dim decimal3 As String
 
format1 = 32
number = number1
numberi = Int(number)
decimal1 = number1 - numberi
decimal2 = decimal1 * format1
 
    Select Case decimal3
        Case 0.25
            decimal3 = " 1/4"
        Case 0.5
            decimal3 = " +"
        Case 0.75
            decimal3 = " 3/4"
    End Select
 
format32 = decimal1 [COLOR=darkgreen]'JUST TO TEST FUNCTION - NOT ACTUAL END FUNTION[/COLOR]
 
End Function

 

[WuAhUuU]

The code works, however for "new" values less than 10 a 0 should preceed the number. As you can see I started the function. Maybe we can combine both funtions for the perfect outcome.

Thank you