Running Sum - Floating Sum

[RobertCotton]

Our sales department was given the task of "upselling" inventory items that had no usage for 6 months. If an item had no usage for 6 months and they were able to sell at least 15 of the item in the nxt 6 months they get a bonus.

In my spreadsheet Column A is the inventory item number and Columns B - Y are the monthly usage totals.

What I need to do is look for 6 consecutive months of zero usage and then sum the next 6 months usage. If any 6 month range sums to zero and the next 6 months range sums to >= 15 they will receive a bonus.

Any ideas on how I can accomplish this?

 

[shg]

If your data starts in A2, then in A13 and copy down,

=IF(AND(SUM(A2:A7) = 0, SUM(A8:A13) >= 15), "Bonus", "")

 

[Rhino373]

It would be something like this. This assumes you have column headers through column "Y". It puts the word "bonus" in column "Z" if during the inventory item's period a bonus should occur. If multiple periods an item would be bonus eligible occurs, it puts "bonus" in the next column. If an item goes with no sales for 8 months, it only begins to count sales during the first month with a sale.

You don't really need the ColumnNumberToLetter() function if you changed this up a little. You can call the location by cell instead. If used exactly as written you would need it.

Function DetermineBonus()
FindLastRow = Range("A100000").End(xlUp).Row
FindLastColumn = Range("ZZ" & 1).End(xlToLeft).Column
LastColumnLetter = ColumnNumberToLetter(FindLastColumn + 1)
For x = 2 To FindLastRow
    For y = 2 To FindLastColumn - 6
    With ActiveSheet
        Set Rng = .Range(.Cells(x, y), .Cells(x, y + 5))
    End With
        If Application.WorksheetFunction.Sum(Rng) = 0 Then
            If ActiveSheet.Cells(x, y + 6) <> 0 Then
 
                With ActiveSheet
                    Set Rng = ActiveSheet.Range(.Cells(x, y + 6), .Cells(x, y + 11))
                End With
                If Application.WorksheetFunction.Sum(Rng) > 14 Then
                    FindNewLastColumn = Range("ZZ" & x).End(xlToLeft).Column
                    Range(ColumnNumberToLetter(FindNewLastColumn + 1) & x).Value = "Bonus"
                End If
            End If
        End If
    Next
Next
End Function
Public Function ColumnNumberToLetter(ByVal lngNumber As Long) As String
    ColumnNumberToLetter = Split(ThisWorkbook.Worksheets(1).Columns(lngNumber).Address, ":")(0)
End Function

 

[RobertCotton]

Thank you for the suggestions.

I copied your code into a new module in my workbook and then went back to the spreadsheet and called the funtion in column Z [ =DetermineBonus() ] but the result is a #VALUE! error. I do have column headings in columns a-z.

What am I doing wrong?

Thanks for the help.

 

[Rhino373]

Don't assign this to a cell like that. Run it as a macro. If 2007 from the Developer tab.

 

[AlphaFrog]

This is a User Defined Function (UDF) that returns True or False

Function IsBonus(rng As Range) As Boolean
    Dim i As Long
    For i = 1 To rng.Count - 12
        If WorksheetFunction.Sum(rng(i).Resize(, 6)) = 0 And _
           WorksheetFunction.Sum(rng(i).Resize(, 6).Offset(, 6)) >= 15 Then
                IsBonus = True
                Exit For
        End If
    Next i
End Function

Put a formula like this in a cell.
=IsBonus(B2:Y2)

 

[AlphaFrog]

EDIT to my previous post:

This ...
For i = 1 To rng.Count - 12

Should be this...
For i = 1 To rng.Count - 11

 

[RobertCotton]

Thank you. Now I understand option 1 and it works. Option 2 works as well. Again, Thanks for the help