Rounding issue

[londa_vba]

Hello
I am trying to show numbers in my chart to one decimal place. My issue arises because the numbers in Columns A and B have to =15 and Excel insists on rounding to one decimal rather than truncating the number to one decimal. So I have instances where column A has 3.75 rounded to 3.8 and column B has 11.25 rounded to 11.3. It displays at 3.8 and 11.3 which does not = 15 exactly. Please assist. I would prefer to not use VBA just use a formula or setting changes.

 

[6StringJazzer]

If you want to truncate numbers to one decimal place you will need two more columns and plot your data from those columns.

=INT(A1*10)/10
=INT(B1*10)/10

BUT I think you are asking the wrong question if you need these numbers to add up to exactly 15. If you truncate 3.75 to 3.7 and 11.25 to 11.2 they add up to 14.9. Still not 15.

The only way to avoid this is to absorb the rounding error. If you do it in the second number then you will need two columns like this:

=INT(A1*10)/10
=15-A1

 

[londa_vba]

If you want to truncate numbers to one decimal place you will need two more columns and plot your data from those columns.

=INT(A1*10)/10
=INT(B1*10)/10

BUT I think you are asking the wrong question if you need these numbers to add up to exactly 15. If you truncate 3.75 to 3.7 and 11.25 to 11.2 they add up to 14.9. Still not 15.

The only way to avoid this is to absorb the rounding error. If you do it in the second number then you will need two columns like this:

=INT(A1*10)/10
=15-A1

If you want to truncate numbers to one decimal place you will need two more columns and plot your data from those columns.

=INT(A1*10)/10
=INT(B1*10)/10

BUT I think you are asking the wrong question if you need these numbers to add up to exactly 15. If you truncate 3.75 to 3.7 and 11.25 to 11.2 they add up to 14.9. Still not 15.

The only way to avoid this is to absorb the rounding error. If you do it in the second number then you will need two columns like this:

=INT(A1*10)/10
=15-A1

Yes you are right the second portion of your answer solves my issues. Thank you so much.

 

[6StringJazzer]

It depends on what your data means but you might also considering explicitly rounding the first number instead of truncating to one place:

=ROUND(A1,1)