PDA

View Full Version : Help Calculating averages based on options selected in user form



jgoods
04-16-2015, 07:11 AM
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
04-16-2015, 08:10 AM
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
04-16-2015, 08:25 AM
How can I upload my file so you can see?

jgoods
04-16-2015, 08:27 AM
13189

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

jgoods
04-16-2015, 08:30 AM
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
04-16-2015, 09:20 AM
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
04-16-2015, 09:32 AM
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
04-16-2015, 10:32 AM
13191I get 14