Hi there,

JLatham graceously provided this code for me to use to sum values in my
'Daily Reading Master Log' sheet, based on a month selected on the 'Executive
Summary' sheet. The summed value is then assigned to a specif cell on the
'Executive Summary' sheet.

Issue, I need to AVERAGE the values, not SUM them, and i am not sure how to
modify the code. Could anyone help me out?

Carlee

Code:
Sub BuildExecSummary()
'called from the _Change event of the
'drop-down (combo) list on the Executive Summary (ES) sheet

Const ExecSumsheet = "Executive Summary"
Const DRMLsheet = "Daily Reading Master Log"
Const AvBsheet = "Actual vs Budget"
Dim AnnualBudgetSheet As String ' determined dynamically
Dim myErrMssg As String
Dim MonthNumber As Integer

Dim lastReadingDateRow As Long ' will be reused several times
Dim sourceRO As Long ' row offset to obtain data from any source sheet
Dim baseCell As Range
Dim bcCol As Long

'these define the 14 values to be obtained from the DRMLsheet
Dim mPLSTreated As Variant ' can handle whole or floating point values
Dim ytdPLSTreated As Variant ' can add any number types: whole or
floating point
Dim mAPFR As Variant ' monthly Average Plant Flow Rate
Dim ytdAPFR As Variant ' year to date Average Plant Flow Rate
Dim mCuPLS As Variant
Dim ytdCuPLS As Variant
Dim mPLSFerric As Variant
Dim ytdPLSFerric As Variant
Dim mCuProduced As Variant
Dim ytdCuProduced As Variant
Dim mProcAvail As Variant
Dim ytdProcAvail As Variant
Dim mMechAvail As Variant
Dim ytdMechAvail As Variant

'the variables used during processing of budget vs Actuals data
Dim amTotal As Variant ' actual monthly total
Dim bmTotal As Variant ' budgeted monthly total
Dim aytdTotal As Variant ' actual year to date total
Dim bytdTotal As Variant ' budgeted year to date total
Dim sourceCO As Long ' column offset for Budgeted vs Actuals processing
Dim bcRow As Long ' used in processing Budgeted vs Actuals
Dim abBaseCell As Range ' pointer into Annual Budget Report sheet
Dim abRow As Long
Dim budgetRow As Long
Dim actualsRow As Long


Dim maxLastRow As Long ' determines maximum rows on sheet based on Excel
version

'determine max rows on a sheet based on Excel version
maxLastRow = GetMaxLastRow()
'in the event that some bozo calls this direct from Macro list
Worksheets(ExecSumsheet).Select

'before we get deep into this, must be able to find the annual budget sheet
'if we are to complete the process
AnnualBudgetSheet = FindAnnualBudgetSheet()
If AnnualBudgetSheet = "SHEET NOT FOUND" Then
myErrMssg = "Unable to locate the Annual Budget Sheet. This
indicates that"
myErrMssg = myErrMssg & " the sheet has either become corrupted or
may have been"
myErrMssg = myErrMssg & " removed from the workbook!" & vbCrLf
myErrMssg = myErrMssg & "Contact the Technical Support group for
this Excel package for assistance."
MsgBox myErrMssg, vbOKOnly + vbCritical, "Workbook Content Error"
Exit Sub
End If

'ExecSumChoice is L5 on ES sheet
If Range("ExecSumChoice") = 13 Then
Exit Sub
End If
MonthNumber = Range("ExecSumChoice").Value
Range("B5") = "Month: " & Range("ExecSumChoice").Offset(MonthNumber, 0)
'calculate PLS Treated (m3)
'from Master Log sheet, column BK
'on the 'Daily Reading Master Log' sheet, date is in column B
'find last reading date entry
lastReadingDateRow = Worksheets(DRMLsheet).Range("B" &
maxLastRow).End(xlUp).Row
Set baseCell = Worksheets(DRMLsheet).Range("B1") 'first cell in Reading
Date column
bcCol = baseCell.Column
'this way we only have to go thru the list once
'calculating all values for each matched row
'rather than going through it 7 or 14 times - so 7 to 14x faster!
For sourceRO = 2 To lastReadingDateRow - 1 ' use as offset
If Month(baseCell.Offset(sourceRO, 0).Value) = MonthNumber Then
mPLSTreated = mPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
mAPFR = mAPFR + baseCell.Offset(sourceRO, Range("BU1").Column -
bcCol).Value
mCuPLS = mCuPLS + baseCell.Offset(sourceRO, Range("BL1").Column
- bcCol).Value
mPLSFerric = mPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
mCuProduced = mCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
mProcAvail = mProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
mMechAvail = mMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
If Month(baseCell.Offset(sourceRO, 0).Value) <= MonthNumber Then
ytdPLSTreated = ytdPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
ytdAPFR = ytdAPFR + baseCell.Offset(sourceRO,
Range("BU1").Column - bcCol).Value
ytdCuPLS = ytdCuPLS + baseCell.Offset(sourceRO,
Range("BL1").Column - bcCol).Value
ytdPLSFerric = ytdPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
ytdCuProduced = ytdCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
ytdProcAvail = ytdProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
ytdMechAvail = ytdMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
Next ' sourceRO loop

Set baseCell = Nothing ' free up resource
'now we're back to working with the active sheet: the Executive Summary
sheet
With Worksheets(ExecSumsheet)
.Range("C10") = mPLSTreated
.Range("E10") = ytdPLSTreated
.Range("C11") = mAPFR
.Range("E11") = ytdAPFR
.Range("C12") = mCuPLS
.Range("E12") = ytdCuPLS
.Range("C13") = mPLSFerric
.Range("E13") = ytdPLSFerric
.Range("C14") = mCuProduced
.Range("E14") = ytdCuProduced
.Range("C15") = mProcAvail
.Range("E15") = ytdProcAvail
.Range("C16") = mMechAvail
.Range("E16") = ytdMechAvail
End With
'
'now ready to attempt to put together the
'data needed in rows 20-26 of the Exec Summary sheet
'we could probably come up with really spiffy formulas
'to determine all of those values based on the month chosen
'but the offsets into the months makes that kind of
'difficult, plus trying to figure out ytd for mid-year
'values would be a real PITA, so as long as we're
'crunching numbers in VBA, crunch some more!!
'

'based on presumption that Jan Actual amt is in column B
'with each succeeding month being 2 columns to right of last
'so Actual amounts are in
'B, D, F, H, J, L, N, P, R, T, V and X
'but we will get actuals from 'Annual Budget Report' sheet.
'with budgeted being in
'C, E, G, I, K, M, O, Q, S, U, W and Z
Set baseCell = Worksheets(AvBsheet).Range("B5") ' [Jan] | [Actual] label
cell
bcRow = baseCell.Row
Set abBaseCell = Worksheets("Actual vs Budget").Range("D7") '
[Production] | [Jan] label cell
abRow = abBaseCell.Row

'get value sets, one at a time.

'Salaries are in rows 12 and 30
budgetRow = 12
actualsRow = 30
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C20") = amTotal
.Range("D20") = bmTotal
.Range("E20") = aytdTotal
.Range("F20") = bytdTotal
End With

'Maintenance are in rows 13 and 31
budgetRow = 13
actualsRow = 31
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C21") = amTotal
.Range("D21") = bmTotal
.Range("E21") = aytdTotal
.Range("F21") = bytdTotal
End With

'Reagents are in rows 14 and 32
budgetRow = 14
actualsRow = 32
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C22") = amTotal
.Range("D22") = bmTotal
.Range("E22") = aytdTotal
.Range("F22") = bytdTotal
End With

'Utilities are in rows 15 and 33
budgetRow = 15
actualsRow = 33
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C23") = amTotal
.Range("D23") = bmTotal
.Range("E23") = aytdTotal
.Range("F23") = bytdTotal
End With

'Plant Supplies are in rows 16 and 34
budgetRow = 16
actualsRow = 34
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C24") = amTotal
.Range("D24") = bmTotal
.Range("E24") = aytdTotal
.Range("F24") = bytdTotal
End With

'G&A are in rows 17 and 35
budgetRow = 17
actualsRow = 35
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C25") = amTotal
.Range("D25") = bmTotal
.Range("E25") = aytdTotal
.Range("F25") = bytdTotal
End With

'Other are in rows 18 and [36 and 45]
budgetRow = 18
actualsRow = 36
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
amTotal = amTotal + abBaseCell.Offset(45 - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(MonthNumber - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(45 - abRow, sourceCO -
1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C26") = amTotal
.Range("D26") = bmTotal
.Range("E26") = aytdTotal
.Range("F26") = bytdTotal
End With

End Sub

--
Carlee

Just add variables to count the number of items "summed" into each total.
Then when you write to the sheet, divide the total by the count.

--
Regards,
Tom Ogilvy


"Carlee" wrote:

Hi there,

JLatham graceously provided this code for me to use to sum values in my
'Daily Reading Master Log' sheet, based on a month selected on the 'Executive
Summary' sheet. The summed value is then assigned to a specif cell on the
'Executive Summary' sheet.

Issue, I need to AVERAGE the values, not SUM them, and i am not sure how to
modify the code. Could anyone help me out?

Carlee

Code:
Sub BuildExecSummary()
'called from the _Change event of the
'drop-down (combo) list on the Executive Summary (ES) sheet

Const ExecSumsheet = "Executive Summary"
Const DRMLsheet = "Daily Reading Master Log"
Const AvBsheet = "Actual vs Budget"
Dim AnnualBudgetSheet As String ' determined dynamically
Dim myErrMssg As String
Dim MonthNumber As Integer

Dim lastReadingDateRow As Long ' will be reused several times
Dim sourceRO As Long ' row offset to obtain data from any source sheet
Dim baseCell As Range
Dim bcCol As Long

'these define the 14 values to be obtained from the DRMLsheet
Dim mPLSTreated As Variant ' can handle whole or floating point values
Dim ytdPLSTreated As Variant ' can add any number types: whole or
floating point
Dim mAPFR As Variant ' monthly Average Plant Flow Rate
Dim ytdAPFR As Variant ' year to date Average Plant Flow Rate
Dim mCuPLS As Variant
Dim ytdCuPLS As Variant
Dim mPLSFerric As Variant
Dim ytdPLSFerric As Variant
Dim mCuProduced As Variant
Dim ytdCuProduced As Variant
Dim mProcAvail As Variant
Dim ytdProcAvail As Variant
Dim mMechAvail As Variant
Dim ytdMechAvail As Variant

'the variables used during processing of budget vs Actuals data
Dim amTotal As Variant ' actual monthly total
Dim bmTotal As Variant ' budgeted monthly total
Dim aytdTotal As Variant ' actual year to date total
Dim bytdTotal As Variant ' budgeted year to date total
Dim sourceCO As Long ' column offset for Budgeted vs Actuals processing
Dim bcRow As Long ' used in processing Budgeted vs Actuals
Dim abBaseCell As Range ' pointer into Annual Budget Report sheet
Dim abRow As Long
Dim budgetRow As Long
Dim actualsRow As Long


Dim maxLastRow As Long ' determines maximum rows on sheet based on Excel
version

'determine max rows on a sheet based on Excel version
maxLastRow = GetMaxLastRow()
'in the event that some bozo calls this direct from Macro list
Worksheets(ExecSumsheet).Select

'before we get deep into this, must be able to find the annual budget sheet
'if we are to complete the process
AnnualBudgetSheet = FindAnnualBudgetSheet()
If AnnualBudgetSheet = "SHEET NOT FOUND" Then
myErrMssg = "Unable to locate the Annual Budget Sheet. This
indicates that"
myErrMssg = myErrMssg & " the sheet has either become corrupted or
may have been"
myErrMssg = myErrMssg & " removed from the workbook!" & vbCrLf
myErrMssg = myErrMssg & "Contact the Technical Support group for
this Excel package for assistance."
MsgBox myErrMssg, vbOKOnly + vbCritical, "Workbook Content Error"
Exit Sub
End If

'ExecSumChoice is L5 on ES sheet
If Range("ExecSumChoice") = 13 Then
Exit Sub
End If
MonthNumber = Range("ExecSumChoice").Value
Range("B5") = "Month: " & Range("ExecSumChoice").Offset(MonthNumber, 0)
'calculate PLS Treated (m3)
'from Master Log sheet, column BK
'on the 'Daily Reading Master Log' sheet, date is in column B
'find last reading date entry
lastReadingDateRow = Worksheets(DRMLsheet).Range("B" &
maxLastRow).End(xlUp).Row
Set baseCell = Worksheets(DRMLsheet).Range("B1") 'first cell in Reading
Date column
bcCol = baseCell.Column
'this way we only have to go thru the list once
'calculating all values for each matched row
'rather than going through it 7 or 14 times - so 7 to 14x faster!
For sourceRO = 2 To lastReadingDateRow - 1 ' use as offset
If Month(baseCell.Offset(sourceRO, 0).Value) = MonthNumber Then
mPLSTreated = mPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
mAPFR = mAPFR + baseCell.Offset(sourceRO, Range("BU1").Column -
bcCol).Value
mCuPLS = mCuPLS + baseCell.Offset(sourceRO, Range("BL1").Column
- bcCol).Value
mPLSFerric = mPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
mCuProduced = mCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
mProcAvail = mProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
mMechAvail = mMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
If Month(baseCell.Offset(sourceRO, 0).Value) <= MonthNumber Then
ytdPLSTreated = ytdPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
ytdAPFR = ytdAPFR + baseCell.Offset(sourceRO,
Range("BU1").Column - bcCol).Value
ytdCuPLS = ytdCuPLS + baseCell.Offset(sourceRO,
Range("BL1").Column - bcCol).Value
ytdPLSFerric = ytdPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
ytdCuProduced = ytdCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
ytdProcAvail = ytdProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
ytdMechAvail = ytdMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
Next ' sourceRO loop

Set baseCell = Nothing ' free up resource
'now we're back to working with the active sheet: the Executive Summary
sheet
With Worksheets(ExecSumsheet)
.Range("C10") = mPLSTreated
.Range("E10") = ytdPLSTreated
.Range("C11") = mAPFR
.Range("E11") = ytdAPFR
.Range("C12") = mCuPLS
.Range("E12") = ytdCuPLS
.Range("C13") = mPLSFerric
.Range("E13") = ytdPLSFerric
.Range("C14") = mCuProduced
.Range("E14") = ytdCuProduced
.Range("C15") = mProcAvail
.Range("E15") = ytdProcAvail
.Range("C16") = mMechAvail
.Range("E16") = ytdMechAvail
End With
'
'now ready to attempt to put together the
'data needed in rows 20-26 of the Exec Summary sheet
'we could probably come up with really spiffy formulas
'to determine all of those values based on the month chosen
'but the offsets into the months makes that kind of
'difficult, plus trying to figure out ytd for mid-year
'values would be a real PITA, so as long as we're
'crunching numbers in VBA, crunch some more!!
'

'based on presumption that Jan Actual amt is in column B
'with each succeeding month being 2 columns to right of last
'so Actual amounts are in
'B, D, F, H, J, L, N, P, R, T, V and X
'but we will get actuals from 'Annual Budget Report' sheet.
'with budgeted being in
'C, E, G, I, K, M, O, Q, S, U, W and Z
Set baseCell = Worksheets(AvBsheet).Range("B5") ' [Jan] | [Actual] label
cell
bcRow = baseCell.Row
Set abBaseCell = Worksheets("Actual vs Budget").Range("D7") '
[Production] | [Jan] label cell
abRow = abBaseCell.Row

'get value sets, one at a time.

'Salaries are in rows 12 and 30
budgetRow = 12
actualsRow = 30
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C20") = amTotal
.Range("D20") = bmTotal
.Range("E20") = aytdTotal
.Range("F20") = bytdTotal
End With

'Maintenance are in rows 13 and 31
budgetRow = 13
actualsRow = 31
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C21") = amTotal
.Range("D21") = bmTotal
.Range("E21") = aytdTotal
.Range("F21") = bytdTotal
End With

'Reagents are in rows 14 and 32
budgetRow = 14
actualsRow = 32
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C22") = amTotal
.Range("D22") = bmTotal
.Range("E22") = aytdTotal
.Range("F22") = bytdTotal
End With

'Utilities are in rows 15 and 33
budgetRow = 15
actualsRow = 33
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C23") = amTotal
.Range("D23") = bmTotal
.Range("E23") = aytdTotal
.Range("F23") = bytdTotal
End With

'Plant Supplies are in rows 16 and 34
budgetRow = 16
actualsRow = 34
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *

Hi tom,

I am not sure how to go about doing this. Could you assist a bit further.

Many thanks in advance,
--
Carlee


"Tom Ogilvy" wrote:

Just add variables to count the number of items "summed" into each total.
Then when you write to the sheet, divide the total by the count.

--
Regards,
Tom Ogilvy


"Carlee" wrote:

Hi there,

JLatham graceously provided this code for me to use to sum values in my
'Daily Reading Master Log' sheet, based on a month selected on the 'Executive
Summary' sheet. The summed value is then assigned to a specif cell on the
'Executive Summary' sheet.

Issue, I need to AVERAGE the values, not SUM them, and i am not sure how to
modify the code. Could anyone help me out?

Carlee

Code:
Sub BuildExecSummary()
'called from the _Change event of the
'drop-down (combo) list on the Executive Summary (ES) sheet

Const ExecSumsheet = "Executive Summary"
Const DRMLsheet = "Daily Reading Master Log"
Const AvBsheet = "Actual vs Budget"
Dim AnnualBudgetSheet As String ' determined dynamically
Dim myErrMssg As String
Dim MonthNumber As Integer

Dim lastReadingDateRow As Long ' will be reused several times
Dim sourceRO As Long ' row offset to obtain data from any source sheet
Dim baseCell As Range
Dim bcCol As Long

'these define the 14 values to be obtained from the DRMLsheet
Dim mPLSTreated As Variant ' can handle whole or floating point values
Dim ytdPLSTreated As Variant ' can add any number types: whole or
floating point
Dim mAPFR As Variant ' monthly Average Plant Flow Rate
Dim ytdAPFR As Variant ' year to date Average Plant Flow Rate
Dim mCuPLS As Variant
Dim ytdCuPLS As Variant
Dim mPLSFerric As Variant
Dim ytdPLSFerric As Variant
Dim mCuProduced As Variant
Dim ytdCuProduced As Variant
Dim mProcAvail As Variant
Dim ytdProcAvail As Variant
Dim mMechAvail As Variant
Dim ytdMechAvail As Variant

'the variables used during processing of budget vs Actuals data
Dim amTotal As Variant ' actual monthly total
Dim bmTotal As Variant ' budgeted monthly total
Dim aytdTotal As Variant ' actual year to date total
Dim bytdTotal As Variant ' budgeted year to date total
Dim sourceCO As Long ' column offset for Budgeted vs Actuals processing
Dim bcRow As Long ' used in processing Budgeted vs Actuals
Dim abBaseCell As Range ' pointer into Annual Budget Report sheet
Dim abRow As Long
Dim budgetRow As Long
Dim actualsRow As Long


Dim maxLastRow As Long ' determines maximum rows on sheet based on Excel
version

'determine max rows on a sheet based on Excel version
maxLastRow = GetMaxLastRow()
'in the event that some bozo calls this direct from Macro list
Worksheets(ExecSumsheet).Select

'before we get deep into this, must be able to find the annual budget sheet
'if we are to complete the process
AnnualBudgetSheet = FindAnnualBudgetSheet()
If AnnualBudgetSheet = "SHEET NOT FOUND" Then
myErrMssg = "Unable to locate the Annual Budget Sheet. This
indicates that"
myErrMssg = myErrMssg & " the sheet has either become corrupted or
may have been"
myErrMssg = myErrMssg & " removed from the workbook!" & vbCrLf
myErrMssg = myErrMssg & "Contact the Technical Support group for
this Excel package for assistance."
MsgBox myErrMssg, vbOKOnly + vbCritical, "Workbook Content Error"
Exit Sub
End If

'ExecSumChoice is L5 on ES sheet
If Range("ExecSumChoice") = 13 Then
Exit Sub
End If
MonthNumber = Range("ExecSumChoice").Value
Range("B5") = "Month: " & Range("ExecSumChoice").Offset(MonthNumber, 0)
'calculate PLS Treated (m3)
'from Master Log sheet, column BK
'on the 'Daily Reading Master Log' sheet, date is in column B
'find last reading date entry
lastReadingDateRow = Worksheets(DRMLsheet).Range("B" &
maxLastRow).End(xlUp).Row
Set baseCell = Worksheets(DRMLsheet).Range("B1") 'first cell in Reading
Date column
bcCol = baseCell.Column
'this way we only have to go thru the list once
'calculating all values for each matched row
'rather than going through it 7 or 14 times - so 7 to 14x faster!
For sourceRO = 2 To lastReadingDateRow - 1 ' use as offset
If Month(baseCell.Offset(sourceRO, 0).Value) = MonthNumber Then
mPLSTreated = mPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
mAPFR = mAPFR + baseCell.Offset(sourceRO, Range("BU1").Column -
bcCol).Value
mCuPLS = mCuPLS + baseCell.Offset(sourceRO, Range("BL1").Column
- bcCol).Value
mPLSFerric = mPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
mCuProduced = mCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
mProcAvail = mProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
mMechAvail = mMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
If Month(baseCell.Offset(sourceRO, 0).Value) <= MonthNumber Then
ytdPLSTreated = ytdPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
ytdAPFR = ytdAPFR + baseCell.Offset(sourceRO,
Range("BU1").Column - bcCol).Value
ytdCuPLS = ytdCuPLS + baseCell.Offset(sourceRO,
Range("BL1").Column - bcCol).Value
ytdPLSFerric = ytdPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
ytdCuProduced = ytdCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
ytdProcAvail = ytdProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
ytdMechAvail = ytdMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
Next ' sourceRO loop

Set baseCell = Nothing ' free up resource
'now we're back to working with the active sheet: the Executive Summary
sheet
With Worksheets(ExecSumsheet)
.Range("C10") = mPLSTreated
.Range("E10") = ytdPLSTreated
.Range("C11") = mAPFR
.Range("E11") = ytdAPFR
.Range("C12") = mCuPLS
.Range("E12") = ytdCuPLS
.Range("C13") = mPLSFerric
.Range("E13") = ytdPLSFerric
.Range("C14") = mCuProduced
.Range("E14") = ytdCuProduced
.Range("C15") = mProcAvail
.Range("E15") = ytdProcAvail
.Range("C16") = mMechAvail
.Range("E16") = ytdMechAvail
End With
'
'now ready to attempt to put together the
'data needed in rows 20-26 of the Exec Summary sheet
'we could probably come up with really spiffy formulas
'to determine all of those values based on the month chosen
'but the offsets into the months makes that kind of
'difficult, plus trying to figure out ytd for mid-year
'values would be a real PITA, so as long as we're
'crunching numbers in VBA, crunch some more!!
'

'based on presumption that Jan Actual amt is in column B
'with each succeeding month being 2 columns to right of last
'so Actual amounts are in
'B, D, F, H, J, L, N, P, R, T, V and X
'but we will get actuals from 'Annual Budget Report' sheet.
'with budgeted being in
'C, E, G, I, K, M, O, Q, S, U, W and Z
Set baseCell = Worksheets(AvBsheet).Range("B5") ' [Jan] | [Actual] label
cell
bcRow = baseCell.Row
Set abBaseCell = Worksheets("Actual vs Budget").Range("D7") '
[Production] | [Jan] label cell
abRow = abBaseCell.Row

'get value sets, one at a time.

'Salaries are in rows 12 and 30
budgetRow = 12
actualsRow = 30
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C20") = amTotal
.Range("D20") = bmTotal
.Range("E20") = aytdTotal
.Range("F20") = bytdTotal
End With

'Maintenance are in rows 13 and 31
budgetRow = 13
actualsRow = 31
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C21") = amTotal
.Range("D21") = bmTotal
.Range("E21") = aytdTotal
.Range("F21") = bytdTotal
End With

'Reagents are in rows 14 and 32
budgetRow = 14
actualsRow = 32
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C22") = amTotal
.Range("D22") = bmTotal
.Range("E22") = aytdTotal
.Range("F22") = bytdTotal
End With

'Utilities are in rows 15 and 33
budgetRow = 15
actualsRow = 33
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C23") = amTotal
.Range("D23") = bmTotal
.Range("E23") = aytdTotal
.Range("F23") = bytdTotal
End With

'Plant Supplies are in rows 16 and 34
budgetRow = 16
actualsRow = 34
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value

There is one balue that is summed, the rest are averaged. Can this be
accomplished?
--
Carlee


"Tom Ogilvy" wrote:

Just add variables to count the number of items "summed" into each total.
Then when you write to the sheet, divide the total by the count.

--
Regards,
Tom Ogilvy


"Carlee" wrote:

Hi there,

JLatham graceously provided this code for me to use to sum values in my
'Daily Reading Master Log' sheet, based on a month selected on the 'Executive
Summary' sheet. The summed value is then assigned to a specif cell on the
'Executive Summary' sheet.

Issue, I need to AVERAGE the values, not SUM them, and i am not sure how to
modify the code. Could anyone help me out?

Carlee

Code:
Sub BuildExecSummary()
'called from the _Change event of the
'drop-down (combo) list on the Executive Summary (ES) sheet

Const ExecSumsheet = "Executive Summary"
Const DRMLsheet = "Daily Reading Master Log"
Const AvBsheet = "Actual vs Budget"
Dim AnnualBudgetSheet As String ' determined dynamically
Dim myErrMssg As String
Dim MonthNumber As Integer

Dim lastReadingDateRow As Long ' will be reused several times
Dim sourceRO As Long ' row offset to obtain data from any source sheet
Dim baseCell As Range
Dim bcCol As Long

'these define the 14 values to be obtained from the DRMLsheet
Dim mPLSTreated As Variant ' can handle whole or floating point values
Dim ytdPLSTreated As Variant ' can add any number types: whole or
floating point
Dim mAPFR As Variant ' monthly Average Plant Flow Rate
Dim ytdAPFR As Variant ' year to date Average Plant Flow Rate
Dim mCuPLS As Variant
Dim ytdCuPLS As Variant
Dim mPLSFerric As Variant
Dim ytdPLSFerric As Variant
Dim mCuProduced As Variant
Dim ytdCuProduced As Variant
Dim mProcAvail As Variant
Dim ytdProcAvail As Variant
Dim mMechAvail As Variant
Dim ytdMechAvail As Variant

'the variables used during processing of budget vs Actuals data
Dim amTotal As Variant ' actual monthly total
Dim bmTotal As Variant ' budgeted monthly total
Dim aytdTotal As Variant ' actual year to date total
Dim bytdTotal As Variant ' budgeted year to date total
Dim sourceCO As Long ' column offset for Budgeted vs Actuals processing
Dim bcRow As Long ' used in processing Budgeted vs Actuals
Dim abBaseCell As Range ' pointer into Annual Budget Report sheet
Dim abRow As Long
Dim budgetRow As Long
Dim actualsRow As Long


Dim maxLastRow As Long ' determines maximum rows on sheet based on Excel
version

'determine max rows on a sheet based on Excel version
maxLastRow = GetMaxLastRow()
'in the event that some bozo calls this direct from Macro list
Worksheets(ExecSumsheet).Select

'before we get deep into this, must be able to find the annual budget sheet
'if we are to complete the process
AnnualBudgetSheet = FindAnnualBudgetSheet()
If AnnualBudgetSheet = "SHEET NOT FOUND" Then
myErrMssg = "Unable to locate the Annual Budget Sheet. This
indicates that"
myErrMssg = myErrMssg & " the sheet has either become corrupted or
may have been"
myErrMssg = myErrMssg & " removed from the workbook!" & vbCrLf
myErrMssg = myErrMssg & "Contact the Technical Support group for
this Excel package for assistance."
MsgBox myErrMssg, vbOKOnly + vbCritical, "Workbook Content Error"
Exit Sub
End If

'ExecSumChoice is L5 on ES sheet
If Range("ExecSumChoice") = 13 Then
Exit Sub
End If
MonthNumber = Range("ExecSumChoice").Value
Range("B5") = "Month: " & Range("ExecSumChoice").Offset(MonthNumber, 0)
'calculate PLS Treated (m3)
'from Master Log sheet, column BK
'on the 'Daily Reading Master Log' sheet, date is in column B
'find last reading date entry
lastReadingDateRow = Worksheets(DRMLsheet).Range("B" &
maxLastRow).End(xlUp).Row
Set baseCell = Worksheets(DRMLsheet).Range("B1") 'first cell in Reading
Date column
bcCol = baseCell.Column
'this way we only have to go thru the list once
'calculating all values for each matched row
'rather than going through it 7 or 14 times - so 7 to 14x faster!
For sourceRO = 2 To lastReadingDateRow - 1 ' use as offset
If Month(baseCell.Offset(sourceRO, 0).Value) = MonthNumber Then
mPLSTreated = mPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
mAPFR = mAPFR + baseCell.Offset(sourceRO, Range("BU1").Column -
bcCol).Value
mCuPLS = mCuPLS + baseCell.Offset(sourceRO, Range("BL1").Column
- bcCol).Value
mPLSFerric = mPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
mCuProduced = mCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
mProcAvail = mProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
mMechAvail = mMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
If Month(baseCell.Offset(sourceRO, 0).Value) <= MonthNumber Then
ytdPLSTreated = ytdPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
ytdAPFR = ytdAPFR + baseCell.Offset(sourceRO,
Range("BU1").Column - bcCol).Value
ytdCuPLS = ytdCuPLS + baseCell.Offset(sourceRO,
Range("BL1").Column - bcCol).Value
ytdPLSFerric = ytdPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
ytdCuProduced = ytdCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
ytdProcAvail = ytdProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
ytdMechAvail = ytdMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
Next ' sourceRO loop

Set baseCell = Nothing ' free up resource
'now we're back to working with the active sheet: the Executive Summary
sheet
With Worksheets(ExecSumsheet)
.Range("C10") = mPLSTreated
.Range("E10") = ytdPLSTreated
.Range("C11") = mAPFR
.Range("E11") = ytdAPFR
.Range("C12") = mCuPLS
.Range("E12") = ytdCuPLS
.Range("C13") = mPLSFerric
.Range("E13") = ytdPLSFerric
.Range("C14") = mCuProduced
.Range("E14") = ytdCuProduced
.Range("C15") = mProcAvail
.Range("E15") = ytdProcAvail
.Range("C16") = mMechAvail
.Range("E16") = ytdMechAvail
End With
'
'now ready to attempt to put together the
'data needed in rows 20-26 of the Exec Summary sheet
'we could probably come up with really spiffy formulas
'to determine all of those values based on the month chosen
'but the offsets into the months makes that kind of
'difficult, plus trying to figure out ytd for mid-year
'values would be a real PITA, so as long as we're
'crunching numbers in VBA, crunch some more!!
'

'based on presumption that Jan Actual amt is in column B
'with each succeeding month being 2 columns to right of last
'so Actual amounts are in
'B, D, F, H, J, L, N, P, R, T, V and X
'but we will get actuals from 'Annual Budget Report' sheet.
'with budgeted being in
'C, E, G, I, K, M, O, Q, S, U, W and Z
Set baseCell = Worksheets(AvBsheet).Range("B5") ' [Jan] | [Actual] label
cell
bcRow = baseCell.Row
Set abBaseCell = Worksheets("Actual vs Budget").Range("D7") '
[Production] | [Jan] label cell
abRow = abBaseCell.Row

'get value sets, one at a time.

'Salaries are in rows 12 and 30
budgetRow = 12
actualsRow = 30
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C20") = amTotal
.Range("D20") = bmTotal
.Range("E20") = aytdTotal
.Range("F20") = bytdTotal
End With

'Maintenance are in rows 13 and 31
budgetRow = 13
actualsRow = 31
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C21") = amTotal
.Range("D21") = bmTotal
.Range("E21") = aytdTotal
.Range("F21") = bytdTotal
End With

'Reagents are in rows 14 and 32
budgetRow = 14
actualsRow = 32
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C22") = amTotal
.Range("D22") = bmTotal
.Range("E22") = aytdTotal
.Range("F22") = bytdTotal
End With

'Utilities are in rows 15 and 33
budgetRow = 15
actualsRow = 33
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C23") = amTotal
.Range("D23") = bmTotal
.Range("E23") = aytdTotal
.Range("F23") = bytdTotal
End With

'Plant Supplies are in rows 16 and 34
budgetRow = 16
actualsRow = 34
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value

Since JLatham wrote it, why not ask him to modify it.

That should be your quickest and surest solution.

recall:

Contact me at (remove spaces) HelpFrom @ jlathamsite.com


--
Regards,
Tom Ogilvy



"Carlee" wrote:

There is one balue that is summed, the rest are averaged. Can this be
accomplished?
--
Carlee


"Tom Ogilvy" wrote:

Just add variables to count the number of items "summed" into each total.
Then when you write to the sheet, divide the total by the count.

--
Regards,
Tom Ogilvy


"Carlee" wrote:

Hi there,

JLatham graceously provided this code for me to use to sum values in my
'Daily Reading Master Log' sheet, based on a month selected on the 'Executive
Summary' sheet. The summed value is then assigned to a specif cell on the
'Executive Summary' sheet.

Issue, I need to AVERAGE the values, not SUM them, and i am not sure how to
modify the code. Could anyone help me out?

Carlee

Code:
Sub BuildExecSummary()
'called from the _Change event of the
'drop-down (combo) list on the Executive Summary (ES) sheet

Const ExecSumsheet = "Executive Summary"
Const DRMLsheet = "Daily Reading Master Log"
Const AvBsheet = "Actual vs Budget"
Dim AnnualBudgetSheet As String ' determined dynamically
Dim myErrMssg As String
Dim MonthNumber As Integer

Dim lastReadingDateRow As Long ' will be reused several times
Dim sourceRO As Long ' row offset to obtain data from any source sheet
Dim baseCell As Range
Dim bcCol As Long

'these define the 14 values to be obtained from the DRMLsheet
Dim mPLSTreated As Variant ' can handle whole or floating point values
Dim ytdPLSTreated As Variant ' can add any number types: whole or
floating point
Dim mAPFR As Variant ' monthly Average Plant Flow Rate
Dim ytdAPFR As Variant ' year to date Average Plant Flow Rate
Dim mCuPLS As Variant
Dim ytdCuPLS As Variant
Dim mPLSFerric As Variant
Dim ytdPLSFerric As Variant
Dim mCuProduced As Variant
Dim ytdCuProduced As Variant
Dim mProcAvail As Variant
Dim ytdProcAvail As Variant
Dim mMechAvail As Variant
Dim ytdMechAvail As Variant

'the variables used during processing of budget vs Actuals data
Dim amTotal As Variant ' actual monthly total
Dim bmTotal As Variant ' budgeted monthly total
Dim aytdTotal As Variant ' actual year to date total
Dim bytdTotal As Variant ' budgeted year to date total
Dim sourceCO As Long ' column offset for Budgeted vs Actuals processing
Dim bcRow As Long ' used in processing Budgeted vs Actuals
Dim abBaseCell As Range ' pointer into Annual Budget Report sheet
Dim abRow As Long
Dim budgetRow As Long
Dim actualsRow As Long


Dim maxLastRow As Long ' determines maximum rows on sheet based on Excel
version

'determine max rows on a sheet based on Excel version
maxLastRow = GetMaxLastRow()
'in the event that some bozo calls this direct from Macro list
Worksheets(ExecSumsheet).Select

'before we get deep into this, must be able to find the annual budget sheet
'if we are to complete the process
AnnualBudgetSheet = FindAnnualBudgetSheet()
If AnnualBudgetSheet = "SHEET NOT FOUND" Then
myErrMssg = "Unable to locate the Annual Budget Sheet. This
indicates that"
myErrMssg = myErrMssg & " the sheet has either become corrupted or
may have been"
myErrMssg = myErrMssg & " removed from the workbook!" & vbCrLf
myErrMssg = myErrMssg & "Contact the Technical Support group for
this Excel package for assistance."
MsgBox myErrMssg, vbOKOnly + vbCritical, "Workbook Content Error"
Exit Sub
End If

'ExecSumChoice is L5 on ES sheet
If Range("ExecSumChoice") = 13 Then
Exit Sub
End If
MonthNumber = Range("ExecSumChoice").Value
Range("B5") = "Month: " & Range("ExecSumChoice").Offset(MonthNumber, 0)
'calculate PLS Treated (m3)
'from Master Log sheet, column BK
'on the 'Daily Reading Master Log' sheet, date is in column B
'find last reading date entry
lastReadingDateRow = Worksheets(DRMLsheet).Range("B" &
maxLastRow).End(xlUp).Row
Set baseCell = Worksheets(DRMLsheet).Range("B1") 'first cell in Reading
Date column
bcCol = baseCell.Column
'this way we only have to go thru the list once
'calculating all values for each matched row
'rather than going through it 7 or 14 times - so 7 to 14x faster!
For sourceRO = 2 To lastReadingDateRow - 1 ' use as offset
If Month(baseCell.Offset(sourceRO, 0).Value) = MonthNumber Then
mPLSTreated = mPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
mAPFR = mAPFR + baseCell.Offset(sourceRO, Range("BU1").Column -
bcCol).Value
mCuPLS = mCuPLS + baseCell.Offset(sourceRO, Range("BL1").Column
- bcCol).Value
mPLSFerric = mPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
mCuProduced = mCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
mProcAvail = mProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
mMechAvail = mMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
If Month(baseCell.Offset(sourceRO, 0).Value) <= MonthNumber Then
ytdPLSTreated = ytdPLSTreated + baseCell.Offset(sourceRO,
Range("BK1").Column - bcCol).Value
ytdAPFR = ytdAPFR + baseCell.Offset(sourceRO,
Range("BU1").Column - bcCol).Value
ytdCuPLS = ytdCuPLS + baseCell.Offset(sourceRO,
Range("BL1").Column - bcCol).Value
ytdPLSFerric = ytdPLSFerric + baseCell.Offset(sourceRO,
Range("BM1").Column - bcCol).Value
ytdCuProduced = ytdCuProduced + baseCell.Offset(sourceRO,
Range("BZ1").Column - bcCol).Value
ytdProcAvail = ytdProcAvail + baseCell.Offset(sourceRO,
Range("BY1").Column - bcCol).Value
ytdMechAvail = ytdMechAvail + baseCell.Offset(sourceRO,
Range("BX1").Column - bcCol).Value
End If
Next ' sourceRO loop

Set baseCell = Nothing ' free up resource
'now we're back to working with the active sheet: the Executive Summary
sheet
With Worksheets(ExecSumsheet)
.Range("C10") = mPLSTreated
.Range("E10") = ytdPLSTreated
.Range("C11") = mAPFR
.Range("E11") = ytdAPFR
.Range("C12") = mCuPLS
.Range("E12") = ytdCuPLS
.Range("C13") = mPLSFerric
.Range("E13") = ytdPLSFerric
.Range("C14") = mCuProduced
.Range("E14") = ytdCuProduced
.Range("C15") = mProcAvail
.Range("E15") = ytdProcAvail
.Range("C16") = mMechAvail
.Range("E16") = ytdMechAvail
End With
'
'now ready to attempt to put together the
'data needed in rows 20-26 of the Exec Summary sheet
'we could probably come up with really spiffy formulas
'to determine all of those values based on the month chosen
'but the offsets into the months makes that kind of
'difficult, plus trying to figure out ytd for mid-year
'values would be a real PITA, so as long as we're
'crunching numbers in VBA, crunch some more!!
'

'based on presumption that Jan Actual amt is in column B
'with each succeeding month being 2 columns to right of last
'so Actual amounts are in
'B, D, F, H, J, L, N, P, R, T, V and X
'but we will get actuals from 'Annual Budget Report' sheet.
'with budgeted being in
'C, E, G, I, K, M, O, Q, S, U, W and Z
Set baseCell = Worksheets(AvBsheet).Range("B5") ' [Jan] | [Actual] label
cell
bcRow = baseCell.Row
Set abBaseCell = Worksheets("Actual vs Budget").Range("D7") '
[Production] | [Jan] label cell
abRow = abBaseCell.Row

'get value sets, one at a time.

'Salaries are in rows 12 and 30
budgetRow = 12
actualsRow = 30
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C20") = amTotal
.Range("D20") = bmTotal
.Range("E20") = aytdTotal
.Range("F20") = bytdTotal
End With

'Maintenance are in rows 13 and 31
budgetRow = 13
actualsRow = 31
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C21") = amTotal
.Range("D21") = bmTotal
.Range("E21") = aytdTotal
.Range("F21") = bytdTotal
End With

'Reagents are in rows 14 and 32
budgetRow = 14
actualsRow = 32
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C22") = amTotal
.Range("D22") = bmTotal
.Range("E22") = aytdTotal
.Range("F22") = bytdTotal
End With

'Utilities are in rows 15 and 33
budgetRow = 15
actualsRow = 33
bmTotal = baseCell.Offset(budgetRow - bcRow, (2 * (MonthNumber - 1)) +
1).Value
amTotal = abBaseCell.Offset(actualsRow - abRow, MonthNumber - 1).Value
'get the year to date totals
'budgeted amounts have to come from every other column on the Actual vs
Budget sheet
'while Actual amounts come from sequential columns on Annual Budget
Report sheet
aytdTotal = 0 ' reset
bytdTotal = 0 ' reset
For sourceCO = 1 To (MonthNumber)
bytdTotal = bytdTotal + baseCell.Offset(budgetRow - bcRow, (2 *
(sourceCO - 1)) + 1).Value
aytdTotal = aytdTotal + abBaseCell.Offset(actualsRow - abRow,
sourceCO - 1).Value
Next
With Worksheets(ExecSumsheet)
.Range("C23") = amTotal
.Range("D23") = bmTotal
.Range("E23") = aytdTotal