Display rounding adjustment before getting the end result

[fandy123]

Hi. I would like to ask if there is any formula to show the rounding adjustment before getting the end result.

i would like to set it to:
if the 2nd decimal places is
X.X1 & X.X6, then -0.01
X.X2 & X.X7, then -0.02
X.X3 & X.X8, then +0.02
X.X4 & X.X9, then +0.01
for X.X5 it will remain as 0.05
and X.X0 it will remain as 0.00.

For example, if now after i sum up the figures from cell A1 to cell A10, it is 12,345.67
i wish to show a rounding figure adjustment of -0.02
which thus will end up getting 12,345.65.

which mean, figures will be showed as
in cell A11, 12.345.67
in cell A12, -0.02
in cell A13, 12.345.65

How would the formula be in cell A12? (I don't want to use the rounding formula in order to get the figure in cell A13 as i was told to display the rounding adjustment before getting the end result)

Hope the is someone who can help me to solve this problem. It has bothered me for few days. :'(

Thank you.

[:) Sixthsense :)]

.


=floor(a11,0.05)

[fandy123]

HI Sixthsense ,

Thank you for replying. However that is not the formula that i want because it does not showing the rounding adjustment before getting the end result.

Btw, it rounded to the nearest 0.05, which is different from what i am asking in the thread.

Anyway, thank you for trying to help me.

[samba_ravi]

Attach sample file with desired result

[:) Sixthsense :)]

May be this... ?


=A11+-0.02

[vlady]

i came up with this longer. not able to think to shorten up but the logic i think would help.
extract 2nd place value by int/mod
subtract 5 if value is greater than 5 leaving a table for 1-5 use for lookup

=A2+IFERROR(LOOKUP(INT(MOD(A2*10^2,10))+IF(INT(MOD(A2*10^2,10))>5,-5,0),{1,2,3,4,5},{-0.01,-0.02,0.02,0.01,0}),0)

[fandy123]

i came up with this longer. not able to think to shorten up but the logic i think would help.
extract 2nd place value by int/mod
subtract 5 if value is greater than 5 leaving a table for 1-5 use for lookup

=A2+IFERROR(LOOKUP(INT(MOD(A2*10^2,10))+IF(INT(MOD(A2*10^2,10))>5,-5,0),{1,2,3,4,5},{-0.01,-0.02,0.02,0.01,0}),0)

Hi vlady,

Thank you for the reply. I've tried it in my worksheet but it seems only works for some cells only.
like if in cell A11, the figure is 1,027.62
in cell A12, the rounding adjustment is (0.01) instead of (0.02).
i thought i was because of the sum figures from A1 to A10 but when i am testing by only keying 1,027.62 in cell B1, it still came out with the rounding adjustment of (0.01) in cell B2.

[vlady]

Please Login or Register  to view this content.

i change it to Mid()

[WilMo]

Slightly old thread, but a trick not yet mentioned is using an array formula. I've fought this challenge in the past and know how frustrating in can be! This will let you calculate the rounded-summation, the sum-of-rounded-elements, and their difference in a single cell.

By way of example: If "Exact" is in cell B2, and each value in the "Rounded" column is rounded to 2-digits (e.g. =ROUND(B2,2)), then the "0.03" in the table is created with the formula:

Please Login or Register  to view this content.

The brackets are not typed: they show up by editing in the cell (double-click or [F2]) and using [ctl][shift][enter] to make it an array formula.

The Totals are the normal sums of the columns.

	Exact	Rounded
	1.004	1.00
	1.004	1.00
	1.004	1.00
	1.004	1.00
	1.004	1.00
	1.004	1.00
	1.004	1.00
rounding adj.		0.03
Total	7.028	7.03

This can be generalized to use different rounding methods or numbers digits, just have to keep it consistent between the rows and the array formula.

HTH,
-W