Solved

# Excel formula for rounding

Posted on 2013-12-11
210 Views
I have two values on an excel spreadsheet Cell A1 is equal to \$12.56 and cell B1 is equal to \$12. I need a formula that will compare B1 to A1 and give me a message "OK" if the amounts tie. The amounts will generally not tie due to round ding. Can the formual accomodate a rounding difference of 20 and still get the OK message? Below is the formula that I am using. Due to the .56 difference, its giving me the message "This total does not tie to A1"

IF(B1=A1,"OK","This total does not tie to A1"
0
Question by:Conernesto
• 2
• 2

LVL 11

Expert Comment

MROUND should help!
``````=MROUND(A1,0.2)
``````
I think you want:
``````=IF(MROUND(B1,0.2)=MROUND(A1,0.2),"OK","This total does not tie to A1")
``````
If you want to get fancy and make the message dynamic too you can use this (which when copied down a column will give the correct A1, A2, etc.):
``````=IF(MROUND(B1,0.2)=MROUND(A1,0.2),"OK","This total does not tie to "&SUBSTITUTE(CELL("address",A1),"\$",""))
``````
0

LVL 15

Accepted Solution

if you want to check to see if A1 is within a value of A2 then use this

0

Author Comment

I want the amount to be OK if the difference in A1 and B2 is \$100.00 or less.
0

LVL 15

Expert Comment

Then is my equation use 100 for 'your value' and change it to a <= sign. The use of ABS will ensure that it doesn't matter if A1 > A2 or vice versa
0

LVL 11

Expert Comment

I was clearly not understanding the question!
0

## Featured Post

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…