9 Replies Latest reply on May 18, 2016 4:48 PM by Ashish Chaudhari

# Round function not working correctly with Excel data

I am having trouble with the Round function working correctly.  Source data is Excel.  Two examples below:

Data In:   .893   (in Excel with 10 decimals, value is 0.8930000000)

Function:    Str(Round([Data In], 2) )

Result:   .8929

Data In:   1.6     (in Excel with 20 decimals, value is 1.60000000000000000000)

Function:    Str(Round([Data In], 2) )

Result:   1.6000000000000001

Is this a bug, or am I doing something wrong?

• ###### 1. Re: Round function not working correctly with Excel data

Hi Dave,

Have you read Jonathan Drummey's post on the ROUND() function?

"What’s happening here is that Tableau is using bankers’ rounding, also known as “round half to even.” A number like -0.5 is rounded to the next nearest even number, so it goes to 0, while 1.5 goes to 2. The reason why Tableau does this is that bankers’ rounding is generally more accurate than the round half away from zero rule."

Basically, don't confuse rounding for number formatting, and remember that FLOAT data types are funny.

The whole post is worth a read and very in-depth.

1 of 1 people found this helpful
• ###### 2. Re: Round function not working correctly with Excel data

Hi Dave,

This is interesting and not something I have seen before. But, I can verify that it is happening since I got the same result, though for .893 I got .8900000001 instead of .8929. The error appears to occur with the rounded value is converted to a string. Leaving it as a numeric value does not show the error at the very end. Also, this appears to only happen when the trailing zeros are included in the excel data. I've included a screenshot of my results. The .25 and 1.25 were entered without trailing zeros in excel.

-Marc

1 of 1 people found this helpful
• ###### 3. Re: Round function not working correctly with Excel data

Hi Dave,

I have used the same numbers that you have given in the case and Im doing round using the calculation as well as format options (middle field).

Let me know if this is the result that you are looking for,

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 4. Re: Round function not working correctly with Excel data

Attaching sample tableau workbooks as well as excel data. Attempt 1

Refer sheet 3 in tableau workbook.

1 of 1 people found this helpful
• ###### 5. Re: Round function not working correctly with Excel data

Hi All,

Thanks for responding!  I am impressed with the Tableau community participation.  I did read through all of the suggestions, but unfortunately none seem to address my particular challenge.  Regarding the Data In, none of the values actually have trailing zeroes.  I just viewed the Excel data using 10-20 decimal precision to look for any "hidden" deep decimal values - none.  Ashish, your contribution was the closest to a possible solution, but my overall challenge presenting a lot of KPIs with various formats (integers, decimals, percentages, etc) in a single cross-tab.  I am trying to solve it by converting everything to strings with appropriate formatting -- definitely clumsy and calc intensive, but client is very particular.  I am developing using Excel data source, but will switch to MSSQL soon, hopefully with better options on data prep.  Again, thanks everyone.

• ###### 6. Re: Round function not working correctly with Excel data

Hey Dave,

Your reply stirred a vague memory of an article that might help:

Try out:

STR (Round (ATTR (<Field Name>),<Number of decimal places>) )

Or

LEFT(STR([<Number Field>]), [<Desired length>])

Again, this appears to be an issue with the CAST function when working with FLOAT data types.

• ###### 7. Re: Round function not working correctly with Excel data

Hi Nicholas,  I will try this tomorrow.  Have to leave from my Tacoma location now - going to the Seattle TUG meeting along with 100,000 Beyoncé fans going to Safeco Field

• ###### 8. Re: Round function not working correctly with Excel data

Wow, I didn't know the Seattle TUG had gotten so big that they could fill Safeco, let alone hang out with Bey!

• ###### 9. Re: Round function not working correctly with Excel data

Thanks Dave.