Increase OR Decrease the complete value

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
Column B is updated with RTD

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

Thanx in adv

I am using Excel 2007
Sheet1

BC
2162.597623162.60
355.8445698755.80
42.0052.00
5162.544623162.50
62.1052.10

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 84px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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).)
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top