CODEWITHSUNDEEP
excel-formula
user13940869
user13940869

Reputation:

EXCEL - Convert TON to KG incorrect

I have Excel 365 on MAC.

I want to convert "weight unit" in another "weight unit". =CONVERT(number; from_unit; to_unit)

Why is 1 ton = 907kg ? This should be 1000kg

Amount  Unit        Amount          Unit
1   ton =>  907,18474   kg
1   kg  =>  1000            g
1   g   =>  1000            mg

What is the correct way to convert?

Thanks, Aykut

Upvotes: -2

Views: 2447

Answers (3)

Vitalijus
Vitalijus

Reputation: 1

My solution to the problem:

=CONVERT(D22;"kg";"ton")/1,10231

Works just fine.

Upvotes: 0

user13940869
user13940869

Reputation:

Excel function did not work for unit conversion from tons (metric) to other weight units.

Excel out of the box function:

=Convert(value; from_unit; to_unit)

I would like to share my (workaround) solution with the same attributes as Excel:

=myConvert(value; from_unit; to_unit)

Public Function myConvert(value As Variant, f_unit As String, to_unit As String) As Variant
    Dim conversion As String
    Dim position As Integer
    
    Dim unit_array As Variant
    
    unit_array = Array( _
    "mg_mg", "mg_g", "mg_kg", "mg_t", _
    "g_mg", "g_g", "g_kg", "g_t", _
    "kg_mg", "kg_g", "kg_kg", "kg_t", _
    "t_mg", "t_g", "t_kg", "t_t" _
    )
    
    Dim formula_array As Variant
    
    formula_array = Array( _
    "", "/ 1000", "/ 1000000", "/ 1000000000", _
    "* 1000", "", "/ 1000", "/ 1000000", _
    "* 1000000", "* 1000", "", " / 1000", _
    "* 1000000000", "* 1000000", "* 1000", "" _
     )
    
    conversion = f_unit & "_" & to_unit
    position = array_pos(unit_array, conversion)
 
    value = Replace(value, ",", ".")
    
    If (IsNumeric(value) = False) Then
        myConvert = "not a number"
        Exit Function
    ElseIf (position < 0) Then
        myConvert = "unknown unit"
        Exit Function
    End If
  
    myConvert = Evaluate(value & " " & formula_array(position))
    
End Function


Function array_pos(my_array, my_value)
    array_pos = -1
    
    For ii = LBound(my_array) To UBound(my_array)
        If my_array(ii) = my_value Then 'value found
            array_pos = ii
            Exit For
        End If
    Next
    
End Function

Here is the test result:

1   t   =   1           t
                
1   t   =   1000            kg
1   t   =   1000000         g
1   t   =   1000000000  mg
                
1   kg  =   0,001           t
1   g   =   0,000001    t
1   mg  =   0,000000001 t
                
1   t   =   unknown unit    x      because x as unit is unknown
y   t   =   not a number    kg     because y is not a number

Upvotes: 0

mehdi sahebi
mehdi sahebi

Reputation: 38

In Britannica encyclopedia defined ton: ton, a unit of weight in the avoirdupois system equal to 2,000 pounds (907.18 kg) in the United States (the short ton) and 2,240 pounds (1,016.05 kg) in Britain (the long ton). The metric ton used in most other countries is 1,000 kg, equivalent to 2,204.6 pounds avoirdupois.

Microsoft Excel just support "ton" and "uk_ton"

Upvotes: 1

Related Questions