YTL Excel #127: AVERAGE When Hurdle Met

Hi,
Is there a way of getting excel to work out monthly commission on the following basis:

Commission rates:
0 - 20,000 - 10%
20,001 - 40,000 - 15%
40,001 - 60,000 - 20%
60,001 - 25%

In any 3 months commission is paid monthly depending on sales per month over 3 months commision hurdle.
eg: Month 1 Sales of 15k is 15k@10%=1500 commission
Month 2 sales of 20k will have commision of 5k@10%(to reach 20k hurdle) and 15k@15% =total of 2750,
Month 3 sales of 10k will have commission of  5k @15 % (40k hurdle) & 5k at 20% total of 1750

Any help would be appreciated
Thanks

I have hit a significant roadblock in my project and would be most appreciative of assistance from the members here.

I have included the entire sub here, although the error is isolated to a particular line in the code, highlighted in red.

The block of code in green is intended to delete any drawing objects falling within the cell range C23:E26. When executed, and crtcount < 1, I am receiving an "Application-defined or Object-defined error." As you will notice I use this identical block (sans the cell ranges) without error, likely because the diacount and fldcount values > . They do not trigger the deletion.

What is odd is that when the code breaks, it's the reference worksheet that is in focus, not wsht, which is where the objects are. I will throw out a guess that this may be where the problem lie, but I'm at a loss.

Code:

Sub file_exists()
Application.EnableEvents = False

Dim wsht As Worksheet
Dim wshs As Worksheet
Set wsht = Workbooks("Open1.xls").Worksheets("Title")
Set wshs = Workbooks("Open1.xls").Worksheets("Sheet1")

With wsht.Range("D11")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
End With
With wsht
    If Dir("E:\SportsOps 2009\Data\" & sfname)  "" Then                               'REFERENCE FILE EXISTS
        .Unprotect
        For Each PicObj In .Shapes
        TheLeft = PicObj.TopLeftCell.Address
        Set isect = Application.Intersect(.Range(TheLeft), .Range("B16:E38"))
        If Not isect Is Nothing Then PicObj.Delete
        Next PicObj
        .Range("F14") = 1
        With wshs
            .Unprotect
            .Range("G3:J25").Copy Destination:=wsht.Range("B16:E38")
        End With
        Application.CutCopyMode = False
        Workbooks.Open Filename:="E:\SportsOps 2009\Data\" & sfname
        Set wsh_dia = Workbooks(sfname).Worksheets("Dia_temp")
        Set wsh_fld = Workbooks(sfname).Worksheets("Flds_Temp")
        Set wsh_crt = Workbooks(sfname).Worksheets("Crts_Temp")
        diacount = WorksheetFunction.Count(wsh_dia.Columns("E"))
        fldcount = WorksheetFunction.Count(wsh_fld.Columns("E"))
        crtcount = WorksheetFunction.Count(wsh_crt.Columns("E"))
        .Range("B18").Value = diacount
            If diacount < 1 Then
                For Each PicObj In .Shapes
                TheLeft = PicObj.TopLeftCell.Address
                Set isect = Application.Intersect(.Range(TheLeft), .Range("C17:E20"))
                If Not isect Is Nothing Then PicObj.Delete
                Next PicObj
                With wshs
                    .Range("R4:T7").Copy Destination:=wsht.Range("C17:E20")
                End With
            End If
        .Range("B21").Value = fldcount
            If fldcount < 1 Then
                For Each PicObj In .Shapes
                TheLeft = PicObj.TopLeftCell.Address
                Set isect = Application.Intersect(.Range(TheLeft), .Range("C20:E23"))
                If Not isect Is Nothing Then PicObj.Delete
                Next PicObj
                With wshs
                    .Range("R7:T10").Copy Destination:=wsht.Range("C20")
                End With
            End If
        .Range("B24").Value = crtcount
         If crtcount < 1 Then
                For Each PicObj In .Shapes
                TheLeft = PicObj.TopLeftCell.Address
                Set isect = Application.Intersect(.Range(TheLeft), .Range("C23:E26")) 
                 If Not isect Is Nothing Then PicObj.Delete 
                 Next PicObj 
                With wshs
                    .Range("R10:T13").Copy Destination:=wsht.Range("C23")
                End With
            End If
        Workbooks(sfname).Close
        Application.EnableEvents = True
        .Protect
        Exit Sub
    Else                                                                                ' FILE DOES NOT EXITS
        .Unprotect
        Dim wshCSV As Worksheet
        Dim csvcount As Integer
        Dim csvdate As Date
        csvcount = 0
        csvdate = 0
        For Each PicObj In .Shapes
        TheLeft = PicObj.TopLeftCell.Address
        Set isect = Application.Intersect(.Range(TheLeft), .Range("B16:E38"))
        If Not isect Is Nothing Then PicObj.Delete
        Next PicObj
        .Range("F14") = 3
        Workbooks.Open Filename:="E:\SportsOps 2009\CLASS_DUMP\schedule.csv"
        Set wshCSV = Workbooks("schedule.csv").Worksheets("schedule")
        .Activate
        With wshs
            .Unprotect
            .Range("B3:e13").Copy Destination:=wsht.Range("B16:E26")
            .Protect
        End With
        Application.CutCopyMode = False
        csvcount = WorksheetFunction.Count(wshCSV.Columns("M"))
        csvdate = WorksheetFunction.Max(wshCSV.Columns("J"))
        Workbooks("schedule.csv").Close
        .Range("D17").Value = csvdate
        .Range("D18").Value = csvcount
    End If
End With
Application.EnableEvents = True
ActiveSheet.Protect

End Sub

I have had similar problems crop up all of a sudden. I have another similar thread ... http://www.mrexcel.com/forum/showthread.php?t=388669 that remains unresolved.
Similar to the other, the error isn't reporoduced when I step through the code and observe the actions on the worksheet to which it applies.

JENN

I have a data entry worksheet called 'Entry', and a worksheet that calculatates totals called 'Total', within a workbook. Using the data in the 'Entry' tab, I want to Average all the items in Column A with the exception of the header and put it into cell A1 on the 'Total' tab. I DO want the Average to calculate any zero's, but I do not want it to consider blanks to be zeros. Please help! Thanks!!

Help !

I'm interested in calculating the average of a single dynamic cell in excel any ideas?

Problem : Need to calculate the "average traded price" of a stock using the "last trade" information. The "last trade" data is dynamic and need to calculate and be able to see the average price intraday.

Many thanks

I have been trying to 'average if' some data...even using the CSE function and cannot get my 'ifs' to work the way I want to. Here is what I am trying to do: I have populated column A with data, each cell representing the previous years sales on that day for the entire month (A1:A31). In column B, I add in each days actual sales each morning. I want to show the Month To Date average for sales vs last year. This is easy to do in column B, because I add the new data daily and it averages that amount with the previous days. The problem is averaging column A only through the day where I have entered sales data from this year. I am having trouble with averaging column A MTD vs the whole month of data. Is there a way to 'average if' last years sales through when the column next to it is populated with actual sales data?

Hopefull this makes sense.

Thanks in advance,

Scott

I've searched the forum and couldn't really find anything to help with this.

I need to caculate an average of three cells. (A1 C1 D1) but if a cell contains $0 then it will exclude that cell from the calculation.

The fact that the cell's are continous seems to be the reason why I can't get around this

Hello, thanks in advance to anyone who reads this and attempts to help.


I have a very large data set in which specific cells need to be averaged. The cells that should be averaged are in a predictable locations relative to one another.

For instance, Cells A3 and A14 need to be averaged, and then cells A25 and A36, and then A47 and A58 and so up to A400 or so. My current formula will average A3,A14, but then averages A14 with A25 which is not what I want.

Do any formulas come to mind to solve this? Perhaps averaging the original cell and 11 after it, skip 11 cells and average that cell and 11 after, etc...

I am not sure how to accomplish this. Anything pointing me in the right direction would be much appreciated! Please let me know if the original .xlsx file is need.

Thanks again.

Hello all,

Alright, this one "seems" tough to me but to you pros, I'm sure it'll be cake!

Here's what I need...

On the worksheet example below, there are 3 columns and 16 rows of data.

The average of all the rows in column C is $1,707.13

I would like a macro that finds the average of column c and deletes any rows (yes, the entire row) that are less than 50% of the average in column c (not including the header row of course.

So, 50% of the average in this case is $853.57 which means only top 3 rows would be kept (plus header row).



Customer Number Character Payment ABC1249 Minny Mouse $12,505.00 ABC1257 Daffy Duck $9,908.00 ABC1254 Mickey Mouse $1,425.00 ABC1262 Snoopy $645.00 ABC1248 Mickey Mouse $600.00 ABC1252 Daffy Duck $504.00 ABC1261 Minny Mouse $265.00 ABC1247 Daisy Duck $250.00 ABC1258 Daffy Duck $199.00 ABC1253 Daisy Duck $180.00 ABC1260 Mickey Mouse $160.00 ABC1255 Minny Mouse $159.00 ABC1251 Donald Duck $155.00 ABC1250 Snoopy $125.00 ABC1256 Snoopy $122.00 ABC1259 Daisy Duck $112.00


Thanks much!

I don't use formulas very often. I am trying to do an average of a column. I do =average(b2:b13) - this works fine until I change data in the cells and the number in my cell with the formula does not change. How do I get it to change with the data changes?