Rounding Issues

[Chase91]

Hello, this is my first post on these forums, so please let me know if there is anyway I can improve my post.

So we receive interest rate values in Excel spreadsheets constantly. These interest rates are always (and must be) in multiples of 1/8, so 0, .125, .25, .375, and so on. However, oftentimes, they come in and 3 decimal values (thousandths) are rounded to 2 decimals (hundredths), e.g., 0, .13, .25, .38.

I cannot simply increase the decimal, as it seems they are such as pasted values. I can think of doing a find & replace, as in if .13, then replace with .125...but that seems like a terribly inefficient solution.

Does anyone have any solution in either Access or Excel? These spreadsheets simply get imported into Access as well.

Good	Bad
0.0
0.125	0.13
0.25
0.375	0.38
0.5 and so on...

<tbody>
</tbody>

I appreciate in advance any direction, and suggestions for improvements on my future posting(s).
I am using Excel (and Access) 2013.

Thank you,
Chase

 

[Jonmo1]

Which version of Excel do you have?

Try
=MROUND(A1,1/8)

This works in all versions 2003+, however it's part of the Analysis Toolpack.
This is installed by default in XL2007+, but in 2003 you'd have to install it manually from Tools - Addins.

 

[bbott]

Welcome to the board.

Take a look at the FLOOR function. For example:

=FLOOR(A1,1/8)

will round down the value in A1 to the nearest eighth (0.13 to 0.125, 0.38 to 0.375, etc).

 

[Newkiebrown]

So do you want a column that returns what it should be? i.e. 0.13 = 0.125?

if so I would suggest something like:

=INT(B2/0.125)*0.125

Where b2 is the potentially rounded number

 

[Newkiebrown]

nice, never used floor. I shall try and remember that one

 

[Chase91]

Welcome to the board.

Take a look at the FLOOR function. For example:

=FLOOR(A1,1/8)

will round down the value in A1 to the nearest eighth (0.13 to 0.125, 0.38 to 0.375, etc).

This function definitely works, but for whatever reason, I can't get it to work exactly right. As in...

If I type "4.13" into cell A1 and in B1, I type "=FLOOR(A1,1/8)", it will return "4.125", which is perfect.

But as for the interest rates already in the spreadsheet...

Cell A1 has "4.25%" typed into it and is formatted as a percentage.
If I type "=FLOOR(A1,1/8)" into B1, it returns "0.00%" and I have no idea why.

I really appreciate yours and everyone else's answers so far.

 

[bbott]

That's because 4.25 formatted as a percent is actually the number 0.0425, so it rounds it down to 0. Try:

=FLOOR(A1*100,1/8)

 

[Chase91]

Welcome to the board.

Take a look at the FLOOR function. For example:

=FLOOR(A1,1/8)

will round down the value in A1 to the nearest eighth (0.13 to 0.125, 0.38 to 0.375, etc).

This is also a great solution (like the FLOOR function posted below), but I run into the exact same problem as with FLOOR. I can use MROUND just fine on a clean, brand-new spreadsheet, but when I try and apply MROUND to the "4.13%" value in the spreadsheets we receive (by the way, the 4.13% is also formatted AS a percentage), my MROUND function returns "0.00%"...

 

[Chase91]

Brilliant bbott...thank you very much!!

 

[bbott]

Brilliant bbott...thank you very much!!

You're welcome.