Hello, I am trying to use a formula in OpenRefine and it is not giving me a result to decimal places.
I entered: cells[“column 1”].value/60 + cells[“column 2”].value
e.g. Column 1 = 18, column 2 = 45
18/60 + 45 should return 45.30
but it gives only 45 without the decimal places.
Thanks
Amanda
Hi Amanda,
the concept you are stumpling over is the differentiation between integers and floating point numbers in programming languages (you are currently using the programming language GREL in OpenRefine)…
To tell OpenRefine that you want an floating point number as the result of a division you have to use at least one floating point number in the division. So in your example you would have to change 60 to 60.0.
cells["column 1"].value/60.0 + cells["column 2"].value
For details see the OpenRefine documentation on math functions.
Awesome, that worked! Thank you. How do I get the number returned to only two decimal places? Some returned were only 1 decimal place and some were returned with more than 10 decimal places.
If you want to controll the format of the data displayed you have to convert the numbers to a string format.
In your case that would be:
(cells["column 1"].value/60.0 + cells["column 2"].value).toString("%.2f")
The following part is completely, absolutely, enormously wrong and just kept as humorous reference.
Note that this will just clip the number after the 2nd decimal.
If you want to have the numbers rounded on the 2nd decimal before formatting you need a more complex formula like:
(round((cells["column 1"].value/60.0 + cells["column 2"].value) * 100)/100.0).toString("%.2f")
The reason for the multiplication and division by 100 is, that GREL currently only supports rounding on integers
If you want to have the numbers rounded on the 2nd decimal before formatting you need a more complex formula like:
(round((cells["column 1"].value/60.0 + cells["column 2"].value) * 100)/100.0).toString("%.2f")
I don't think you need to jump through so many hoops. The string formatting does rounding implicitly, e.g.
0.4999.toString("%2.2f") = "0.50"
The case that requires special handling is the opposite. If you want truncation instead of rounding, you'd need to use something like
0.4999.toString()[0,4]
Tom
Yes, absolutely... classical example of brain failure while multitasking.