Increase OR Decrease the complete value

[hsandeep]

Column B is updated with RTD

Output required in Column C as:
If 3^rd decimal place >=5, then INCREASE the 2^nd decimal place by 1 value & COMPLETE VALUE to nearest .05 multiple
ELSE DECREASE the 2^nd decimal place by 1 value & COMPLETE VALUE to nearest .05 multiple

Thanx in adv

I am using Excel 2007
Sheet1

	B	C

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 84px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]162.597623[/TD]
[TD="align: right"]162.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]55.84456987[/TD]
[TD="align: right"]55.80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]2.005[/TD]
[TD="align: right"]2.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]162.544623[/TD]
[TD="align: right"]162.50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]2.105[/TD]
[TD="align: right"]2.10[/TD]

</tbody>

 

[joeu2004]

First, by your logic, I believe 55.84456987 and 162.544623 should round to 55.85 and 162.55, not 55.80 and 162.50 as you wrote. Rounding to 2 dp, they become 55.84 and 162.54. Then the nearest multiple of 0.05 is 55.85 and 162.55.

By your logic, you might write =MROUND(ROUND(B2,2),0.05).

But I have not found an example where that is any different from simply =MROUND(B2,0.05).

In any case, I deprecate the use of MROUND with a non-integer second parameter, unless it is a power of 2. Otherwise, binary arithmetic anomalies might result in infinitesimal differences.

For example, whether we use MROUND(ROUND...,0.05) or simply MROUND(...,0.5), the calculated 162.60 is not the same binary representation as the constant 162.60. That may or may not impact you, depending on formulas in dependent cells. For example, =MATCH(162.60,C2,0) returns #N/A, indicating that they do not match, despite appearances because Excel formats only up to 15 significant digits (rounded).

Instead, you might consider =MROUND(ROUND(100*B2,0),5)/100.

(Although I believe that does work, I think it would be prudent to write =ROUND(MROUND(ROUND(100*B2,0),5)/100,2).)

 

[hsandeep]

First, by your logic, I believe 55.84456987 and 162.544623 should round to 55.85 and 162.55, not 55.80 and 162.50 as you wrote. Rounding to 2 dp, they become 55.84 and 162.54. Then the nearest multiple of 0.05 is 55.85 and 162.55.

You are CORRECT. 55.84456987 and 162.544623 should round to 55.85 and 162.55, not 55.80 and 162.50

Formula needed

 

[joeu2004]

You are CORRECT. 55.84456987 and 162.544623 should round to 55.85 and 162.55, not 55.80 and 162.50
Formula needed

Formulas provided. Reread response #2 .

If you have examples where my formulas fail, please provide them.