+ Reply to Thread
Results 1 to 5 of 5

Formula is not working for ABSENT student in marksheet

  1. #1
    Registered User
    Join Date
    08-13-2016
    Location
    NAGPUR
    MS-Off Ver
    MS OFFICE 2007
    Posts
    49

    Formula is not working for ABSENT student in marksheet

    Respected Sir,

    I am trying with this formula to make a marksheet in excel.

    =IF(AND(ISNUMBER(A2),ISNUMBER(B2),A2>=32,B2>=8),"F",IF(AND(A2=32,OR(B2="AA",B2<8)),"T",IF(AND(OR(A2<32,A2="AA"),AND(ISNUMBER(B2),B2>=8)),"I","")))

    As per the formula, Theory paper passing marks is 32 & Practical's passing is 8.

    If a student gets the marks as above mentioned then he got the Full Exemption in the subject as "F"
    "T" exemption stands for if he gets the minimum 32 marks in Theory and less than 8 in Internal..

    So as, "I" exemption stands for if he gets the minimum 8 marks in Practical and less than 32 in Theory..
    But, if a students gets more than 32 marks in Theory and Absent (AA) in Practical, the the above formula is not working properly...
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula is not working for ABSENT student in marksheet

    =if(and(isnumber(a2),isnumber(b2),a2>=32,b2>=8),"f",if(and(a2>=32,or(b2="aa",b2<8)),"t",if(and(or(a2<32,a2="aa"),and(isnumber(b2),b2>=8)),"i","")))

  3. #3
    Registered User
    Join Date
    08-13-2016
    Location
    NAGPUR
    MS-Off Ver
    MS OFFICE 2007
    Posts
    49

    Re: Formula is not working for ABSENT student in marksheet

    Quote Originally Posted by PaulM100 View Post
    =if(and(isnumber(a2),isnumber(b2),a2>=32,b2>=8),"f",if(and(a2>=32,or(b2="aa",b2<8)),"t",if(and(or(a2<32,a2="aa"),and(isnumber(b2),b2>=8)),"i","")))
    Thanks Sir... But yet this formula is not working properly.. If a student having "AA" (Absent) in the both, i.e. Theory & Practical.. It's Showing result as "T"

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Formula is not working for ABSENT student in marksheet

    This will work:
    =IF(AND(ISNUMBER(A2),A2>=32,B2="AA"),"T",IF(AND(A2="AA",B2="AA"),"",IF(AND(ISNUMBER(A2),ISNUMBER(B2),A2>=32,B2>=8),"F",IF(AND(OR(A2="AA",A2<32),ISNUMBER(B2),B2>=8),"I",""))))

  5. #5
    Registered User
    Join Date
    08-13-2016
    Location
    NAGPUR
    MS-Off Ver
    MS OFFICE 2007
    Posts
    49

    Re: Formula is not working for ABSENT student in marksheet

    Quote Originally Posted by PaulM100 View Post
    This will work:
    =IF(AND(ISNUMBER(A2),A2>=32,B2="AA"),"T",IF(AND(A2="AA",B2="AA"),"",IF(AND(ISNUMBER(A2),ISNUMBER(B2),A2>=32,B2>=8),"F",IF(AND(OR(A2="AA",A2<32),ISNUMBER(B2),B2>=8),"I",""))))
    Thanks a Lot Sir... Now its working properly... This thread has been solved.. Thanx 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. Need more help for Marksheet formula
    By vijubhau in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 01-09-2018, 10:11 PM
  2. Need a formula for working out statistics of student results
    By Andarthiel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-27-2016, 04:07 AM
  3. Replies: 1
    Last Post: 02-06-2014, 08:07 AM
  4. Replies: 1
    Last Post: 06-03-2013, 06:23 PM
  5. Replies: 1
    Last Post: 06-03-2013, 06:14 PM
  6. formula for school marksheet
    By kbbpatidar in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-23-2012, 01:44 PM
  7. [SOLVED] Formula in Marksheet
    By Zainuddin Zakaria in forum Excel General
    Replies: 6
    Last Post: 04-20-2006, 12:50 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