Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel formula for rounding

Posted on 2013-12-11
5
Medium Priority
?
270 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

580 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