+ Reply to Thread
Results 1 to 12 of 12

IF formula to calculate a deviation of +- .5%

  1. #1
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    IF formula to calculate a deviation of +- .5%

    Hi all,

    I'm creating a spread sheet and I need a formula to calculate a deviation of half a percent of error.

    My current formula will only calculate the error if its a positive number .0500 but if its negative like -6.003 it still recognizes it as good which it isn't.

    Any help would be greatly appreciated.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,991

    Re: IF formula to calculate a deviation of +- .5%

    Build an If at the beginning

    =IF(A1<0,"","Your Formula")
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    Re: IF formula to calculate a deviation of +- .5%

    Hi Jeff

    Thanks for your help, here's what I've got, which works on the +.5% but as soon as it dips into the negative numbers it registers as fail even though the deviation is still within the +-.5%

    =IF(F41<0,"Fail",IF(F41<C41,"Pass","Fail"))

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,991

    Re: IF formula to calculate a deviation of +- .5%

    Hi mikey,

    How about a quick sample workbook with the values you have and what you expect?

  5. #5
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    Re: IF formula to calculate a deviation of +- .5%

    No problem let me get it doctored up for ya and will post shortly

    Thanks again and much appreciated

  6. #6
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    Re: IF formula to calculate a deviation of +- .5%

    Here it is and let me know if you need me to explain in more detail

    Thanks again
    Attached Files Attached Files

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,991

    Re: IF formula to calculate a deviation of +- .5%

    I'm sorry mikey, but not quite clear on what you're needing here.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,616

    Re: IF formula to calculate a deviation of +- .5%

    In E38 for example
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Is that what you are looking for?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    Re: IF formula to calculate a deviation of +- .5%

    Sorry about that.

    the error is half a percent, .5% but the as long as the error in column G is with in the range whether it be positive or negative it would pass.

    ex 20.000 is the reference so 20.081 (%error in G57 would be 0.500) and 19.920 (%error in G57 would be -0.500) these are still good an reference a pass in E41 and G41 for the As found and As left tables. Anything outside of that would reference a Fail.

    Hope that makes more sense

  10. #10
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    Re: IF formula to calculate a deviation of +- .5%

    Hi ChemistB,

    You my friend are getting a Christmas card this year!! Do you like puppies or kittens? thank you so much that worked!

    Thanks to JeffreyBrown as well for your help too
    Last edited by jeffreybrown; 03-26-2020 at 04:07 PM. Reason: removed full quote

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    9,991

    Re: IF formula to calculate a deviation of +- .5%

    You are very welcome and thanks for the feedback. We are happy to help, but Brian did the real work in-which I am very thankful to him.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  12. #12
    Registered User
    Join Date
    09-16-2008
    Location
    Canada
    MS-Off Ver
    Excel 2003 (silly work wont upgrade)
    Posts
    71

    Re: IF formula to calculate a deviation of +- .5%

    Will do, and thanks again to both of you gentleman for the quick help. Stay safe

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1