# Solved Excel IF Statement Help

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

Not open for further replies.

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:

• ###### Test1.xlsx
File size:
11 KB
Views:
2

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,""))
```

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.

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