CODEWITHSUNDEEP
google-sheetsconcatenationspreadsheet
Zayphod42
Zayphod42

Reputation: 33

Why is this Google Sheets Concatenate Formula giving me weird results?

I'm trying to use Google Sheets to concatenate a bit of data. It works 90% of the time, however on certain numbers, I get an odd result. I have to copy the result of this data and paste it into a financial program in a specific format and am using the concatenate formula to do this. The format the program requires is that each field be separated by one period, even if it is a dollar amount as the program will automatically move the decimal point two places to the left while it is evaluating the information. The issue is that on some numbers the formula adds two periods between the fields, which stops the evaluation of the data in our financial program.

Here is a screenshot including the formula You can see that it works with most numbers in the amount column, but with two of the amounts and several others, it adds two periods after the amount. Would you please take a look at this and see if you can help me find the issue?

Thank you!!!!

Upvotes: 1

Views: 926

Answers (2)

player0
player0

Reputation: 1

use just:

=B2&"."&ROUND(C2*100)&"."&D2

Upvotes: 0

CMB
CMB

Reputation: 5163

Looks like it's an existing floating point calculation error in Google Sheets, the multiplication by 100 did not return exact value for certain numbers but with extra very small decimal. That's why there's an additional period on the result.

As a workaround, use ROUND() upon multiplying by 100 to "snap" it to an integer.

Sample: enter image description here

References:

Floating Point Calculation Error

Upvotes: 2

Related Questions