Solved

Excel formula for rounding

Posted on 2013-12-11
5
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

623 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