Computing Monthly Weighted Average

[jazz2409]

Hello, I am trying to compute the monthly weighted average based on a table. The first table with data has weekly dates, but the table where I need to show the monthly weighted average has months.


26688

It has to compute the weighted average for each owner on a monthly basis (count of Y/sum of Y and N)
These tables are dynamic in count.

[SamT]

three COUNTIF()s should work
The If parameters will be Owner = Owner, Week Date>Previous Month Date AND Week Date=<Current Month Date,

The first Month's formula that refers to the first Week will need to use only Week Date=<Current Month Date

[Paul_Hossler]

User defined function

Again, since it looks like you're using a macro to generate the input data, you can incorporate the logic and just generate the monthly average matrix without ws formulas or a UDF




Option Explicit


Function MonthAvg(Data As Range, Person As String, AsOfMonth As Date) As Variant
Dim r As Long, c As Long
Dim N As Long, Y As Long

Application.Volatile

MonthAvg = CVErr(xlErrNum)

On Error GoTo NiceExit

With Data
For r = 2 To .Rows.Count

If .Cells(r, 2).Value = Person Then
For c = 3 To .Columns.Count
If Month(.Cells(1, c).Value) = Month(AsOfMonth) And Year(.Cells(1, c).Value) = Year(AsOfMonth) Then
If .Cells(r, c).Value = "Y" Then
Y = Y + 1
ElseIf .Cells(r, c).Value = "N" Then
N = N + 1
End If
End If
Next c
End If
Next r
End With


If (Y + N) > 0 Then MonthAvg = Y / (Y + N)


NiceExit:
End Function

[jazz2409]

Hmm I am trying to find where you incorporated this function but I can't find it :(

[Bob Phillips]

This formula does it

=SUMPRODUCT(($B$2:$B$31=$N2)*(TEXT($C$1:$K$1,"yymm")=TEXT(DATEVALUE("01-"&$O$1),"yymm"))*($C$2:$K$31="Y"))/SUMPRODUCT(($B$2:$B$31=$N2)*(TEXT($C$1:$K$1,"yymm")=TEXT(DATEVALUE("01-"&O$1),"yymm"))*($C$2:$K$31<>""))

but I would suggest that it is time to re-design this application, it is horrendously slow.

[jazz2409]

This formula does it

=SUMPRODUCT(($B$2:$B$31=$N2)*(TEXT($C$1:$K$1,"yymm")=TEXT(DATEVALUE("01-"&$O$1),"yymm"))*($C$2:$K$31="Y"))/SUMPRODUCT(($B$2:$B$31=$N2)*(TEXT($C$1:$K$1,"yymm")=TEXT(DATEVALUE("01-"&O$1),"yymm"))*($C$2:$K$31<>""))

but I would suggest that it is time to re-design this application, it is horrendously slow.

Hello, my actual workbook works well.. Calculates in about 2 minutes (8k rows of raw data).. I'm not sure what the problem with this sample workbook is :banghead:

Everything in this specific sheet is dynamic so I don't think I can use a formula this time.. I kind of want to test Paul H's code but I don't understand what I should put after the function name, like what's Data as Range? Is it the range where the Y's and N's are or where I want the end result to show? Person as String, AsOfMonth, etc

[Paul_Hossler]

I kind of want to test Paul H's code but I don't understand what I should put after the function name, like what's Data as Range? Is it the range where the Y's and N's are or where I want the end result to show? Person as String, AsOfMonth, etc

I think the examples in my attachment will show you how the UDF was used


26697

[Bob Phillips]

Everything in this specific sheet is dynamic so I don't think I can use a formula this time.

Surely, formulae are the most dynamic thing on a spreadsheet?

[Paul_Hossler]

1. I have to set calculation to manual just to get it to open today

2. It looks like you're using VBA to insert formulas, instead of just using VBA to calculate values and out them on worksheet

3. If a cell's value needs a formula, can't you just leave the formula there apriori and not keep reinserting it


It looks like you start with ws 'Raw' (just a guess since I didn't look at the macros much). I assume WS table is generated from that

I'd use a macro to process 'raw' adding mm/yy field as Values, not a Formula. Compute and add other fields if necessary to make Monthly and Weekly reportable using pivot tables

If you do need pretty formatting, use a macro and the PT to make the report worksheet

[jazz2409]

Hmm my actual workbook isn't like this, it opens right away and it's not doing the calculating thingy.. I'm not sure why this sample workbook is acting this way.. I'd love to do it just like your function but I don't know how.. I can't even write a looping statement from scratch.. Well I can but just very basic ones. I am really just a newbie and I am still learning.. Of course I'd like to learn as quickly as I can I just find it difficult to juggle work and single parenting at the same time.. In fact I could not focus on just my job because I am attending to my children while working at home :banghead: they don't want to see formulae, they want everything done in a single button click.

[jazz2409]

This formula does it

=SUMPRODUCT(($B$2:$B$31=$N2)*(TEXT($C$1:$K$1,"yymm")=TEXT(DATEVALUE("01-"&$O$1),"yymm"))*($C$2:$K$31="Y"))/SUMPRODUCT(($B$2:$B$31=$N2)*(TEXT($C$1:$K$1,"yymm")=TEXT(DATEVALUE("01-"&O$1),"yymm"))*($C$2:$K$31<>""))

but I would suggest that it is time to re-design this application, it is horrendously slow.


Hello, this is the one I used.


I already understand how Paul H's UDF works, it's just that when I tried to use it in .FormulaR1C1 the whole thing crashed.