rounding problem

[boumbo]

Cell A2 value:- 3503470.54999995 but when formated it is 3503 T
Cell A3 Value:- 2725770.01999996 but when formated it is 2726 T

When i do "=A2-A3" in A4 it is 778 T

it should be 777 T...

How do i change the formula in A4 to show 777 T instead of 778 T... this is rounding problem due to formatting.

 

[JamesMcS]

Have you played around with the formatting? How is it formatted at the moment?

 

[chergh]

778 is the correct answer with rounding why would you want to change it???

 

[boumbo]

778 is the correct answer with rounding why would you want to change it???

because when i send this report to management team, they are picky people... They take out their calculator and get 777. they see only the numbers on the report not on the excel cells. they print the report and check my numbers. I always have a problem with this and wondering if there is a formula/solution for that.

for them they do 3503 -2726 = 777. they want to see it in thousands in the report. this is the number formating they want.

 

[chergh]

I would just put in large text that a difference of 1 is due to rounding.

I also put the following in code to stop them from wasting paper by printing

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

 

[boumbo]

I would just put in large text that a difference of 1 is due to rounding.

I also put the following in code to stop them from wasting paper by printing

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

thanks. but it does not help me... i will be fired if i do that.. . management team get together around a table with the report printout to discuss strategy etcc...

is any other solution for the rounding problem?

 

[chergh]

You will probably have to divide all your numbers by one thousand and then in your results cell use =ROUNDDOWN(A2-A3)

 

[boumbo]

this is the number formatting i am using:-

_($* #,##0,_);_($* (#,##0,);_($* "-"??_);_(@_)


how can i make this simpler and avoid rounding problems?

if i divide it by 1000 and then round down ..... how do i change the number format above?? I am scared to mess everything up...