+ Reply to Thread
Results 1 to 14 of 14

getting a single Pass/Fail result from the values in a range of cells

  1. #1
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9

    getting a single Pass/Fail result from the values in a range of cells

    Hello,

    I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

    I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

    I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

    If somebody could help me out that would be great.

    Thanks,
    Randy
    Last edited by AB_Summit; 09-27-2008 at 07:44 PM.

  2. #2
    Forum Contributor
    Join Date
    02-12-2008
    Location
    Doha-Qatar
    MS-Off Ver
    Excel 2007
    Posts
    137
    Hi,

    Highlight B1:B14 then Ctrl+D

    Regards

  3. #3
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    I tried that, but that gives me a Pass/Fail in each cell from B1 to B14, I just want one Pass or Fail indication in B1. If the value of all of the cells from A1 to A14 is less than 2 I would like B1 to read "Pass", but if any of the cells have a value greater than 2 I would like B1 to read "Fail."

    Thanks
    Randy

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Please read the Forum Rules about thread titles, and then edit yours to be descriptive of your problem.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    I'm sorry for violating the forum rules, and I realized my mistake and tried to edit the title but I am not sure how to do that. When I click on the "Edit" button at the bottom of the post it allows me to edit the body of the message but not the title. If someone can explain to me how to change the title I will gladly change it.

    Thanks
    Randy

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Randy, when you click on Edit, be sure to then click 'Go Advanced'. In the Advanced window you should be able to edit the thread title (although I believe there's a limit of 2 hours, after which you can't change it yourself). If you still cannot change it, PM a moderator who will change it for you.

    Thanks.

  7. #7
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    OK that worked, Thank you.

  8. #8
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Thanks for adjusting your title. Now, as for your problem, try this formula in B1:

    =IF(MAX(A1:A14)>=2,"Fail","Pass")

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    =IF(COUNTIF(A1:A14,">2")>0,"Fail","Pass")

  10. #10
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    Quote Originally Posted by pjoaquin View Post
    Thanks for adjusting your title. Now, as for your problem, try this formula in B1:

    =IF(MAX(A1:A14)>=2,"Fail","Pass")
    Thank you, that worked perfectly.

  11. #11
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    Quote Originally Posted by daddylonglegs View Post
    Try

    =IF(COUNTIF(A1:A14,">2")>0,"Fail","Pass")
    ...and that one works as well, thank you.

    I see now that my formula still needs some tweaking though. What I didn't account for is that some of the values in the A1:A14 range have negative numbers in them. I think I may need to incorporate the ABS function.

    Basically, if all of the cells in the A1:A14 range are between negative 2 and positive 2 it should generate a "Pass." If any of the cells in that range are less than negative 2 or greater than positive 2 it should return a "Fail."

    Any suggestions on how I could accomplish this?

    Thanks again for the help,
    Randy
    Last edited by AB_Summit; 09-27-2008 at 09:09 PM.

  12. #12
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    These two formulas should work:

    The first is an array formula, which must be confirmed by using CTRL+SHIFT+ENTER, not just ENTER. (Remember to use C+S+E after typing or editing array formulas.)

    =IF(MAX(ABS(A1:A14))>=2,"Fail","Pass")

    The second formula is non-array, and can be confirmed normally (just ENTER).

    =IF(AND(MIN(A1:A14)>-2,MAX(A1:A14)<2),"Pass","Fail")

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Adapting the COUNTIF method.....

    =IF(SUMPRODUCT(--(ABS(A1:A14)>2)),"Fail","Pass")

  14. #14
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    Both of the above solutions worked, thanks for the help.

+ 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. Replies: 9
    Last Post: 06-06-2009, 12:09 PM
  2. Associating A Range Of Cells Based On Like Values Of Other Cells
    By Tom_LR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2008, 04:14 PM
  3. Working with a range knowing the start and the end values
    By Snake Pliskin in forum Excel General
    Replies: 2
    Last Post: 10-30-2007, 12:50 PM
  4. Replies: 7
    Last Post: 10-18-2007, 11:03 AM
  5. Looping through rows in range AND comparing range cells
    By Damask in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-16-2006, 10:30 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