# Multiple IF Conditions

#### Jamm_027

##### Board Regular
Here is my formula:

= if(A103<>"New",(B104-B103),if(A102<>"New",(B104-B102),if(A101<>"New",(B104-B100),(B104+B105)))

This worked perfectly for me at first. Now I need more than 7 if conditions. Actually I need for the if conditions to continue in this manner and cover the entire column. How do I modify this formula or should I be using something instead of if statements?

thanks,

jamm

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### nbrcrunch

##### Well-known Member
If by "cover the whole column" you mean you need a condition for each and every row, then you will never get an IF statement to handle that. Even the character limit of the field will run out before you finish the formula.

Try telling us in English what you are trying to accomplish. Post a sample of your data. I'm got a hunch that your conditions are not mutually exclusive, even though that is the way the formula reads.

#### Scott Huish

##### MrExcel MVP
If there was a pattern to the subtraction, it could be made much simpler. For instance you start with A103 and if that's not new you subtract B103, the next one follows this pattern A102 if not new subtract B102, but then you compare A101, but subtract B100, not B101.

Then you add at the end as a FALSE condition, Why?

What are you trying to do exactly?

#### Jamm_027

##### Board Regular
Unfortunately, my company blocks all downloads so I am unable to post an example of my spreadsheet.

Let me try to explain in English. Ignore the cell reference used above. I was just trying to simplify before. Here is how my sheet is set up:

Column A = Employee Name
Column B = Disciplinary Infraction
Column C = Date of Infraction
Column D = Number of days between infraction date
Column E = Number of potential points
Column F = Actual Points

Each time an employees commits an infraction (late, left early, unexcused absence) their supervisor makes an entry. I have a macro that then places the entry on the first available row on the sheet and sorts by column A and then column C (ascending). What is left is grouped by employee name and in chronological order and we have another page that totals their discipline points.

Let's say an employee get an infraction for being late. This is 1 point. according to policy if an employee goes more than 90 days without another infraction they get a 1/2 point back. This is why I compare and subtract the number of days in between rows.

Example:

...........A.................B..............................C.........D...........E.........F
1......John Doe......Unexcused Absence......12/1/08....0...........2.........1.5
2......John Doe......Left Early....................3/9/09.....98..........1.........1

Cell F1 is reduced by .5 because D2 is greater.

Now something new has been added to our policy and this is where my original question came into play. For our legal department we also have to track "Excused Absences" The partner does not get any points for these, but we have to make an entry. Here is what is happening now:

...........A.....................B.........................C..........D.......E........F
1.....John Doe......Unexcused Absence.....12/1/08.....0........2........2
2.....John Doe......Excused Absence........12/15/08....14......0........0
3.....John Doe......Excused Absence........01/21/09....37......0........0
4.....John Doe......Left Early..................03/09/09....12......1.........1

Now my formula in column D is still is still subtracting just from the previous row giving me the number of days but in this example the formula needs to ignore any entry that is an "Excused Absence" and C1 should be subtracted from C4 giving a total of 98 days and therefore F2 becomes 1.5 just like in the example before.

Wow, I hope someone can make heads or tails of all that.

jamm

#### nbrcrunch

##### Well-known Member
Good gravey! Those rules sound as bad as the ones at AT&T.

#### nbrcrunch

##### Well-known Member
Does the half-point credit take place only once after the first 90 day period or do they continue to get credit on a rolling basis. Say for example an employee has two incidents. In 90 days they have none, so they get 0.5 back for a balance of 1.5 incidents. Another 90 days goes by do they get another half-point credit so the balance is only 1?

Are the "points" ever cleared? (Heck, even the Israelites had their Jubulee year when all debts were erased.).

Are there any other circumstances that would clear a point or part thereof?

#### Jamm_027

##### Board Regular
nbrcrunch,

Yes, you are correct with your first assumption. The employee can get multiple 90 day credits. Lets say they have a 2 point infraction after 90 days without another fraction it becomes 1 1/2. Then after 180 it would become 1 point and so on. I use this formula to cover this:

=IF(I522>359,2,IF(I522>269,1.5,IF(I522>179,1,IF(I522>89,.5,0))))

Also, all infractions fall off after 1 year. I have the following code in the workbook module that covers this rule.

Code:
``````Sub WorkBook_Open()

Application.ScreenUpdating = False

Sheets("sheet1").Select
Columns("A:D").Select
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim lngS1LastRow As Long
Dim lngS2LastRow As Long
Dim FilteredRange As Range
Dim rng As Range
Range("A2").Select

lngS1LastRow = Worksheets("Log").Range("A" & Rows.Count).End(xlUp).Row
lngS2LastRow = Worksheets("Archives").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Log").Select

With Worksheets("Log")
.AutoFilterMode = False
With Range("A1:F" & lngS1LastRow)
.AutoFilter
.AutoFilter Field:=6, Criteria1:="Yes"
End With
End With

With Worksheets("Log").AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng Is Nothing Then
With Worksheets("Log")
.AutoFilterMode = False
'Application.ScreenUpdating = True
MsgBox "All disciplinary actions older than 1 year have been removed!"
Sheets("Home").Select
Exit Sub
End With
Else
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Archives").Range("A" & lngS2LastRow + 1)
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Delete Shift:=xlUp
MsgBox "All disciplinary actions older than 1 year have been removed!"

End If

'Msg = "Are you ready to update all installed accounts?"
'ans = MsgBox(Msg, vbOKCancel)
'If ans = vbCancel Then Exit Sub

Worksheets("Log").AutoFilterMode = False
Worksheets("Home").Select

Application.ScreenUpdating = True

End Sub``````

Replies
3
Views
543
Replies
3
Views
118
Replies
3
Views
122
Replies
6
Views
226
Replies
4
Views
544

1,191,697
Messages
5,988,163
Members
440,131
Latest member
EricMoz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back