Solved

Excel formula for rounding

Posted on 2013-12-11
5
249 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

751 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