+ Reply to Thread
Results 1 to 11 of 11

IF Function using multiple cells

  1. #1
    Registered User
    Join Date
    05-01-2015
    Location
    los angeles, california
    MS-Off Ver
    professional pus 2010
    Posts
    19

    IF Function using multiple cells

    Hello,

    I am having a difficulty creating the correct formula for the following scenario:

    10.02% lets say it is in cell A1
    11.49% cell A2
    13.03% cell A3

    I need to compare all three percentages and their differences can not exceed +/- 2.00%. Can anyone provide the correct formula to use? I am using the following:
    =IF(OR((A1-A2)2%,(A1-A3)>2%, (A2-A3)>2%),"Check","OK +/-2")

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: IF Function using multiple cells

    May be:

    =IF(SUMPRODUCT(--(ABS(A2:A3-A1:A2)>0.02))>0,"Check","OK +/- 2%")
    Quang PT

  3. #3
    Registered User
    Join Date
    05-01-2015
    Location
    los angeles, california
    MS-Off Ver
    professional pus 2010
    Posts
    19

    Re: IF Function using multiple cells

    Hi, Thank you for your response. I received a #VALUE! error when entering your suggested formula. Please note that I'm trying to make sure that all values against each other do not exceed +/- 2%. Therefore, A1-A2 is less than +/-2%, A1-A3 is less than +/-2% and A2-A3 is less than +/-2%. If any of them exceeds the tolerance, I want a "check"outcome.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: IF Function using multiple cells

    Not testing, but try to confirm the inputing by Ctrl-shft-enter rather than enter only?

  5. #5
    Registered User
    Join Date
    05-01-2015
    Location
    los angeles, california
    MS-Off Ver
    professional pus 2010
    Posts
    19

    Re: IF Function using multiple cells

    Still did not work

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: IF Function using multiple cells

    Please Login or Register  to view this content.
    Try this
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Registered User
    Join Date
    05-01-2015
    Location
    los angeles, california
    MS-Off Ver
    professional pus 2010
    Posts
    19

    Re: IF Function using multiple cells

    Quote Originally Posted by popipipo View Post
    Please Login or Register  to view this content.
    Try this
    Hi, This equation gave me the result of OK +/-2% which is incorrect because 10.02%-13.03% =3.01%

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: IF Function using multiple cells

    Either a bug in Excel (not likely) or something wrong with your data entry. I put popipipo's formula into Quattro Pro with your given data, and I get the "check" output.

    I don't know what the most likely error is. Check your data entry and make sure the data is actually entered as numbers and are somehow not text strings, or some other incorrect format. Format the cells as general and make sure the actual values in the cells are .1002, .1149, and .1303 and NOT 10.02, 11.49, 13.03. Make sure you copied the formula correctly (especially the direction of the less than operator). It looks to me like popipipo's formula should work. If it really does not, I suspect that there is some other error in your spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  9. #9
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Indonesia
    MS-Off Ver
    2010
    Posts
    177

    Re: IF Function using multiple cells

    Hello,

    Nubie try to help.

    i think you tried to find difference from one result compare min and max for whole data set.
    you need to make the difference as absolute value.

    assume the data range is in A1 to A3. put this formula in A1 ( you can copy paste the formula from A1 cell to A2 and A3 as well, as i already locked the range).

    try this code:
    Please Login or Register  to view this content.
    Last edited by qiyusi; 05-02-2015 at 01:16 AM.

  10. #10
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: IF Function using multiple cells

    This equation gave me the result of OK +/-2%
    Not in my example
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-01-2015
    Location
    los angeles, california
    MS-Off Ver
    professional pus 2010
    Posts
    19

    Smile Re: IF Function using multiple cells

    Thank you all for all your help! I went back to the data set and applied the formula and it worked

    =IF(MAX(I147:I149)-MIN(I147:I149)<=0.02,"OK +/- 2%", "Check")

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using the If function with multiple cells
    By wantucce in forum Excel General
    Replies: 1
    Last Post: 09-24-2014, 03:34 PM
  2. Using the If function with multiple cells
    By wantucce in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 09-24-2014, 03:34 PM
  3. If function for multiple cells?
    By Car71 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2014, 06:22 AM
  4. Replies: 5
    Last Post: 01-20-2009, 11:56 AM
  5. Multiple cells in cells function
    By Adam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2006, 12:10 PM

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