Solved

Excel formula for rounding

Posted on 2013-12-11
5
225 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
ID: 39712880
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
ID: 39712901
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
ID: 39712906
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
ID: 39712915
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
ID: 39712933
Nice answer ChloesDad.
I was clearly not understanding the question!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

920 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

12 Experts available now in Live!

Get 1:1 Help Now