+ Reply to Thread
Results 1 to 5 of 5

Writing an additional 'IF' into a huge 'IF' formula??

  1. 04-24-2014, 05:33 AM #1
    grholden
    grholden is offline
    Registered User
    Join Date
    04-24-2014
    Location
    Yorkshire, UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Writing an additional 'IF' into a huge 'IF' formula??

    Hello everyone,

    Screen Shot 2014-04-24 at 10.27.20.jpg

    I am working on this spreadsheet, I am a teacher.

    At the moment, in B32, I have the following IF formula that works perfectly: =IF(B28>=1590,"D*D*D*",IF(B28>=1560,"D*D*D",IF(B28>=1530,"D*DD",IF(B28>=1500,"DDD",IF(B28>=1460,"DDM",IF(B28>=1420,"DMM",IF(B28>=1380,"MMM",IF(B28>=1340,"MMP",IF(B28>=1300,"MPP",IF(B28>=1260,"PPP","FAIL"))))))))))

    However, the additional complication comes from the fact that if a student fails any unit (ie gets a 'U' on any of the units), then they actually get a 'FAIL' overall. However, at the moment, my spreadsheet and formula doesn't reflect this. For example, my spreadsheet at the moment, could suggest that a student could get top grades in 17 of the 18 units, fail the last unit and still 'pass', whereas this is not the case.

    Any assistance or suggestions are really appreciated.
    Reply With QuoteRegister To Reply
  2. 04-24-2014, 05:41 AM #2
    ragulduy
    ragulduy is offline
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Writing an additional 'IF' into a huge 'IF' formula??

    I would suggest somethign like:
    =IF(OR(B28<1260,COUNTIF(A7:A24,"U")>0),"FAIL",INDEX({"PPP","MPP","MMP","MMM","DMM","DDM","DDD","D*DD","D*D*D","D*D*D*"},MATCH(B28,{1260,1300,1340,1380,1420,1460,1500,1530,1560,1590})))
    although you would be better building a lookup table with the scores/grades.
    Last edited by ragulduy; 04-24-2014 at 05:47 AM.
    Reply With QuoteRegister To Reply
  3. 04-24-2014, 05:43 AM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Writing an additional 'IF' into a huge 'IF' formula??

    so where is the "U" grade ? looking at the picture , thats in column A

    so you could do a countif()

    =IF( Countif( A7:A24, "U")>0, "Fail", IF(B28>=1590,"D*D*D*",IF(B28>=1560,"D*D*D",IF(B28>=1530,"D*DD",IF(B28>=1500,"DDD",IF(B28>=1460,"DDM",IF(B28>=1420,"DMM",IF(B28>=1380,"MMM",IF(B28>=1340,"MMP",IF(B28>=1300,"MPP",IF(B28>=1260,"PPP","FAIL")))))))))))

    if you can attach a spreadsheet , that would be helpful , rather than an image
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 04-24-2014, 06:02 AM #4
    grholden
    grholden is offline
    Registered User
    Join Date
    04-24-2014
    Location
    Yorkshire, UK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Writing an additional 'IF' into a huge 'IF' formula??

    Quote Originally Posted by etaf View Post
    so where is the "U" grade ? looking at the picture , thats in column A

    so you could do a countif()

    =IF( Countif( A7:A24, "U")>0, "Fail", IF(B28>=1590,"D*D*D*",IF(B28>=1560,"D*D*D",IF(B28>=1530,"D*DD",IF(B28>=1500,"DDD",IF(B28>=1460,"DDM",IF(B28>=1420,"DMM",IF(B28>=1380,"MMM",IF(B28>=1340,"MMP",IF(B28>=1300,"MPP",IF(B28>=1260,"PPP","FAIL")))))))))))

    if you can attach a spreadsheet , that would be helpful , rather than an image
    That has just worked perfectly. Thanks very much for both of your responses!
    Reply With QuoteRegister To Reply
  5. 04-24-2014, 06:12 AM #5
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Writing an additional 'IF' into a huge 'IF' formula??

    your welcome
    if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Writing a macro for stock chart with scroll and 4-6 additional series
    By mayur553 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-30-2010, 07:47 AM
  2. newbie: writing a macro to facilitate a huge list.
    By xianwinwin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2010, 03:46 AM
  3. copying huge formula
    By nowfalapm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-30-2009, 07:10 PM
  4. [SOLVED] huge huge excel file... why?
    By Josh in forum Excel General
    Replies: 12
    Last Post: 02-09-2006, 06:00 PM
  5. Huge “IF” formula help.
    By sungen99 in forum Excel General
    Replies: 13
    Last Post: 12-14-2005, 09:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1