Share via

Determine whether two values are within a certain percetage of each other.

Anonymous
2014-06-16T18:16:47+00:00

Ok, this is for all the math majors in the group.  I would assume there is a function for this in excel but I not sure what it is called.  I need to compare two values and determine whether they are within a certain percentage of each other.  Does anyone know if there is a function for this?

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2014-06-16T19:29:10+00:00

When you state "determine whether they are within a certain percentage of each other", it would help to know "a percentage of what"?. Simple math would dictate that dividing one number by the other would approach 100% as the number neared each other (from 0 to 100% one way and from ∞ to 100% from the other way. To keep in one direction you could apply MIN() and MAX().

=MIN(a1, b1)/MAX(a1, b1)

That would approach 100% as the two numbers became close to each other. To get the percentage of difference rather than the percentage of closeness subtract that from 1.

=1-MIN(a1, b1)/MAX(a1, b1)

Was this answer helpful?

5 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2014-06-16T22:58:24+00:00

    Hi,

    Suppose the first number is in cell A2 and the second one in cell B2.  In cell C2, enter =(A2/B2)<=E2

    E2 has the number (expressed in %) that you would like to compare with.

    Hope this helps.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2014-06-18T11:12:08+00:00

    Hi,

    Suppose the first number is in cell A2 and the second one in cell B2.  In cell C2, enter =(A2/B2)<=E2

    E2 has the number (expressed in %) that you would like to compare with.

    Hope this helps.

    I understand the math required to determine the persent difference between two values I was wondering if there was a function in Excel that could be used to calculate this.

    Thanks for the help.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-06-16T19:35:15+00:00

    When you state "determine whether they are within a certain percentage of each other", it would help to know "a percentage of what"?. Simple math would dictate that dividing one number by the other would approach 100% as the number neared each other (from 0 to 100% one way and from ∞ to 100% from the other way. To keep in one direction you could apply MIN() and MAX().

    =MIN(a1, b1)/MAX(a1, b1)

    That would approach 100% as the two numbers became close to each other. To get the percentage of difference rather than the percentage of closeness subtract that from 1.

    =1-MIN(a1, b1)/MAX(a1, b1)

    Thank you for the help.  I am sorry not not being very clear in my request.

    Was this answer helpful?

    0 comments