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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Good[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]0.0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.125[/TD]
[TD]0.13[/TD]
[/TR]
[TR]
[TD]0.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.375[/TD]
[TD]0.38[/TD]
[/TR]
[TR]
[TD]0.5 and so on...[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.