+ Reply to Thread
Results 1 to 4 of 4

Pass/Fail Formula Issue

  1. #1
    Registered User
    Join Date
    07-07-2020
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2016
    Posts
    3

    Pass/Fail Formula Issue

    Hello all. I have been struggling to get an Excel workbook formatted the way I need. So essentially what I am trying to do is have excel sheet quizzes for a student I'm tutoring. Column A will have questions. Column B will be where the student types answers (free form, mostly one word answers). Column C will grade each answer as "Pass" or "Fail". The answer key will be in Column A of Sheet2.

    So my goal is to have the cell in the grading column (C) be blank if the reference answer cell in Column B is blank, say "Pass" if the reference cell contains the same answer as the corresponding answer key cell (i.e. Sheet1!B1=Sheet2!A1), and say "Fail" if it contains any other wrong answer. After consulting my local IT department they said the following formula should do what I want:

    =IF(ISBLANK(B1),"",IF(B1=Sheet2!A1,"Pass","Fail")

    While the first part works fine and the cell stays blank if the answer cell is blank, it says "Fail" even when the correct answer is typed.

    Any ideas of where we went wrong?

    Thanks in advance,
    CJB

  2. #2
    Registered User
    Join Date
    07-07-2020
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Pass/Fail Formula Issue

    NEVERMIND.

    I realized I had accidentally put a space after the answer in the answer key, which is why it was marking it a "Fail". Everything is working fine now. Feel really dumb now lol.
    Last edited by cjb0723; 07-07-2020 at 02:40 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010
    Posts
    894

    Re: Pass/Fail Formula Issue

    Hi
    This part is wrong:

    =IF(ISBLANK(B1),"",IF(B1=Sheet2!A1,"Pass","Fail")

    It should search the whole column and not just in cell A1 like this:
    =IF(ISBLANK(B1),"",IF(B1=Sheet2!A:A,"Pass","Fail")

  4. #4
    Registered User
    Join Date
    07-07-2020
    Location
    Philadelphia, PA, USA
    MS-Off Ver
    Excel 2016
    Posts
    3

    Re: Pass/Fail Formula Issue

    Thank you. I was able to extend the formula to the rest of the column after I figured out the issue.

+ 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. formula for pass/fail/undefined
    By BITS Co. Ltd. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2018, 06:25 AM
  2. [SOLVED] Formula for pass,fail,credit(if,and,or)
    By cathal1292 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-02-2016, 06:01 AM
  3. Formula Help with Pass/Fail function
    By bna1219 in forum Excel General
    Replies: 1
    Last Post: 12-10-2014, 09:57 AM
  4. [SOLVED] Need help with formula to return value of Pass or Fail
    By bearligirl89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-15-2013, 01:06 PM
  5. [SOLVED] Need a formula for pass/fail based on no. of Y answers but automatically fail if 3a is N
    By Researcher1111 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-15-2013, 10:20 PM
  6. Help with Pass / Fail Formula
    By milovisk in forum Excel General
    Replies: 4
    Last Post: 03-20-2012, 02:57 PM
  7. Formula Help with Pass/Fail function
    By Spyroviper in forum Excel General
    Replies: 3
    Last Post: 02-04-2009, 01:02 PM

Tags for this Thread

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