Solved

Excel formula for rounding

Posted on 2013-12-11
5
210 Views
Last Modified: 2013-12-30
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
Comment
Question by:Conernesto
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
MROUND should help!
=MROUND(A1,0.2)

Open in new window

I think you want:
=IF(MROUND(B1,0.2)=MROUND(A1,0.2),"OK","This total does not tie to A1")

Open in new window

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),"$",""))

Open in new window

0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
Comment Utility
if you want to check to see if A1 is within a value of A2 then use this

=if(ABS(A1-A2) < 'your value' ,"YES","NO")
0
 

Author Comment

by:Conernesto
Comment Utility
I want the amount to be OK if the difference in A1 and B2 is $100.00 or less.
0
 
LVL 15

Expert Comment

by:ChloesDad
Comment Utility
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

by:Angelp1ay
Comment Utility
Nice answer ChloesDad.
I was clearly not understanding the question!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now