Help Calculating averages based on options selected in user form

[jgoods]

I have a spreadsheet with columns size,date, number, and amount.
I've created a userform with two sets of option buttons, one which has small medium and large, and the other has the variable to analyze, or number or amount. There is also a list box with the 12 months of the year.

How do I calculate, for example, the average amount (in $) for the small shirts sold in january, given that those are the options selected in my user form?

[Paul_Hossler]

A sample workbook would help, but I faked 3 months of data in the attachment

Something like thing might give you some ideas

There are faster ways (SUMPRODUCT for example) but this is simpler, and has almost NO error checking

Mostly I wasn't sure how consistent you data is

Option Explicit

Private Sub cbGo_Click()
    Dim rData As Range
    Dim iRow As Long
    Dim iTotalCount As Long
    Dim dblTotalAmount As Double
    
    Dim iMonthNumber As Long
    
    iTotalCount = 0
    dblTotalAmount = 0#
    
    iMonthNumber = Me.lbMonths.ListIndex + 1
    
    Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
    For iRow = 2 To rData.Rows.Count
        
        With rData.Rows(iRow)
                    
            If Me.obSmall.Value And LCase(.Cells(1).Value) = "small" Then
                If Month(.Cells(2).Value) = iMonthNumber Then
                    iTotalCount = iTotalCount + 1
                    dblTotalAmount = dblTotalAmount + .Cells(4).Value
                End If
            
            ElseIf Me.obMedium.Value And LCase(.Cells(1).Value) = "medium" Then
                If Month(.Cells(2).Value) = iMonthNumber Then
                    iTotalCount = iTotalCount + 1
                    dblTotalAmount = dblTotalAmount + .Cells(4).Value
                End If
            
            
            ElseIf Me.obLarge.Value And LCase(.Cells(1).Value) = "large" Then
                If Month(.Cells(2).Value) = iMonthNumber Then
                    iTotalCount = iTotalCount + 1
                    dblTotalAmount = dblTotalAmount + .Cells(4).Value
                End If
            
            End If
        End With
    Next iRow
    
    MsgBox "Total Count = " & iTotalCount
    MsgBox "Total Amount = " & dblTotalAmount
    
    If dblTotalAmount > 0# Then
        MsgBox "Average = " & (dblTotalAmount) / iTotalCount
    End If
End Sub
Private Sub UserForm_Initialize()
    Me.obSmall.Value = True
    Me.lbMonths.AddItem "Jan"
    Me.lbMonths.AddItem "Feb"
    Me.lbMonths.AddItem "Mar"
    Me.lbMonths.AddItem "Apr"
    Me.lbMonths.AddItem "May"
    Me.lbMonths.AddItem "Jun"
    Me.lbMonths.AddItem "Jul"
    Me.lbMonths.AddItem "Aug"
    Me.lbMonths.AddItem "Sep"
    Me.lbMonths.AddItem "Oct"
    Me.lbMonths.AddItem "Nov"
    Me.lbMonths.AddItem "Dec"
End Sub

[jgoods]

How can I upload my file so you can see?

[jgoods]

homework6.xlsm

Where 1 refers so small, 2 to medium, 3 to large

[jgoods]

I tried taking yours and applying it to mine, but I keep getting invalid use of "Me" keyword and I'm not quite sure what that means or what to do to fix it.

I need to get the average number and amount for either one month or multiple months. I had it on mine where you can select multiple months from the user form.

I do appreciate your help though! I'm so lost...

[Paul_Hossler]

This is apparently for a homework assignment, so you'll have to do a lot for yourself

We can answer questions and provide hints, but not more than that (no one wants to incur the wrath of a teacher)


1. The was no code to run when you click the execute button

2. You should use a CommandButton on the UF, not a ToggleButton

3. The statement assumes that the block of data starts in A1. Yours seems to start in A3

Set rData = Worksheets("Sheet1").Cells(1, 1).CurrentRegion

4. On my small example, I renamed the UF controls to something meaningful (e.g. obSmall), instead of the generic OptionButton1. I don't think you made them compatible

5. My code looked for text "small" etc. not 1, 2 or 3

6. I'm guessing that the "Me" error was because I put initialization code into the UF (which would be the 'Me') and you didn't

7. I always like to use Option Explicit at the top of modules -- Online help has a good write up


Here's another example using your data, but you still have work to do:

It still only handles a single month, so you'll need to read the correct ListBox properties and do the math for each selected month.

The results are displayed as part of the UF, so you probably want to use Public variables in the standard module


A good way to see what's happening is to put a breakpoint on a line -- macro will run to that and stop, then you can single step through the rest of the code. I put a little picture in the example in case I'm not clear

[jgoods]

Thank you so much! I'm running through it now and adjusting...
one thing though, when I select small and january I get a different number than if I manually average each small from january... I'm looking at the code and the logic/math seems appropriate, I can't figure out the discrepancy there.

The total count from the program is 11 when there's 14....

[Paul_Hossler]

Capture.JPGI get 14