Multiple IF Conditions

[Jamm_027]

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

 

[nbrcrunch]

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]

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]

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.

thanks in advance for any advice,

jamm

 

[nbrcrunch]

Good gravey! Those rules sound as bad as the ones at AT&T.

Still thinking about your post.

 

[nbrcrunch]

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]

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.

Sub WorkBook_Open()

Application.ScreenUpdating = False

Sheets("sheet1").Select
Columns("A:D").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        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