1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Excel IF Statement Help

Discussion in 'Business Applications' started by TechieNerd32, May 2, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. TechieNerd32

    TechieNerd32 Thread Starter

    Joined:
    Oct 18, 2017
    Messages:
    10
    I am having trouble (probably with logic) on the attached spreadsheet. Column F (Total Incomplete) is a bit complicated and the formula in it currently is not correct. Here is how the logic should apply:

    Background info:
    There are 35 courses that learners need to take. If they take all 35, as indicated in column 1, they should be marked as completed (with a one in column 4. If they have not started any of them, they would have a 35 in column 3 and should be counted (with a one) in column 7. Column 2 (Incomplete) means that they started a course(s), but haven't completed them. But, column 6 is not directly associated with column 2, column 6 should count a one for anyone that does not have a 35 in column 1 Or a 35 in column 3. Meaning that they are somewhere between completely done and haven't started any yet.

    1. If column 1 is equal to column 4, a 1 should be counted one in column 5 - the formula is column 5 is CORRECT and working fine.
    2. If column 3 is equal to column 4, a 1 should be counted one in column 7 - the formula in column 7 is CORRECT and working fine.
    3. Column 6 has multiple conditions that is causing the current formula to FAIL. If there is a number value in column 2, then a 1 should be counted one in column 6. However, column 2 could be null and if column 1 is less than column 4 AND column 3 is less than column 4 then it should also count a one.
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    It is difficult but try this one in column F don't know if I've got it right but ...

    Code:
    =IF(AND(ISNUMBER(B3),B3>0),1,IF(AND(A3<D3,C3<D3),1,""))
    
     
  3. TechieNerd32

    TechieNerd32 Thread Starter

    Joined:
    Oct 18, 2017
    Messages:
    10
    Keebellah, this works perfectly in my test file (where I have stripped out the discreet learner information, so the logic is right on. However, I haven't been able to get it to work on the file that has the information all included and the only thing different is the column numbers change, so it's gotta be something I'm doing when I'm deciphering the formula and making column number changes. So I will consider this to be solved and thanks for your help so much.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Just change the A to 1 B to 2 etc, complying with your columns and do not add $ before the row numbes
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1209631

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice