Long way around

[C W Loper]

Column A, cells 1 through 41, numeric data which is populated from another sheet. Not every cell contains a number, some are blank.
Cell A1 is blank
Cell A2 is blank
Cell A3 has number 222
Cell A10 has number 241
Cell A16 has number 200
Trying to figure out a formula or Array to look at Column A and, beginning with whichever cell has the first integer, A3, highlight the next cell with an integer in Column A, IF it is within seven cells from A3.
Basically, I have four sample results for the month and, have to report the average of all four. UNLESS, any two sample results were collected within seven days of each other, then I have to average the two and report as that average along with the others.

 

[James006]

Hi,

For what you are describing should be rather simple to solve ... provided a sample file is available ...

 

[C W Loper]

Hi,

For what you are describing should be rather simple to solve ... provided a sample file is available ...

James006;
Yeah, I realized, a copy of the sheet would have been WAY better after I posted. Please see attached. Sorry for picture, still trying to figure out XL2BB, can't seem to get it to work.

 

[C W Loper]

Hi,

For what you are describing should be rather simple to solve ... provided a sample file is available ...

James006;
Between all of the interruptions this morning and IT security settings on my PC, that process was painful. either way, I hope this helps. I haven't figured out how to execute copying two areas of two sheets, if you need the source sheet I can send that separately.

Operations.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Jan (A)	Jan (B)
2	Day	Flow, MGD	mg/l	mg/l
3	1	1.58				Required to sample 4 times per month, report monthly and weekly averages.
4	2	1.62
5	3	1.73	222	209		For reporting, if the samples are greater than seven days apart, then the averages are a typical average calculation. However, if any samples fall within the same seven day range, then those have to be averaged and then added to the rest of the average calculation. Not my rules, State regulation.
6	4	1.67
7	5	1.77
8	6	1.61
9	7	1.61				Referring to the scenario presented in Column C, (Jan A), the average calculation would be standard, since samples were collected greater than seven days apart, cell (C35).
10	8	1.56
11	9	1.73
12	10	1.67	262	280
13	11	1.62				Referring to Column D, (Jan B), note samples were collected on the tenth, (D12) and the sixteenth, (D18). Therefore, those two results will count as two samples but reported as one value, AVERAGE. The monthly average now changes to, =AVERAGE(D5,D26,D38) resulting in a reported value of 244.3, cell (D39).
14	12	1.57
15	13	1.58
16	14	1.64
17	15	1.64
18	16	1.78		226		The single formula, array, conditional format, whatever, I am trying to create is following:
19	17	1.71	259			1	Look at range D3:D33, starting with the first NON BLANK cell (D5) determine if there are any numeric values which fall within any consecutive seven days in Column D (D12 and D18).
20	18	1.66
21	19	1.67				2	IF any cells in Column D meet the first condition, HIGHLIGHT those two cells and return the AVERAGE in cell (D38) below.
22	20	1.70
23	21	1.77				3	Cell (D38) will return the AVERAGE of Column D replacing values of the two HIGHLIGHTED cells with the value provided in (D37).
24	22	1.64
25	23	1.63				4	Here's the fun part. The data on Sheet 2 is populated from Wastewater Database. The database runs from January through December of the current year. The data in Column C would typically on a completely different sheet for the respective month's report. Column D, would be on a following separate sheet as the next respective month's report and so on. The sample data does not always fall on the same dates each month, I may have a month where data begins on row 3, and the following month beginning on row 1. It's a rare occurence samples are collected within a seven day range of each other. Would it be easier to have the formula start with the first data value in the column and HIGHLIGHT every seventh row? I could visually check if there are values in the highlighted cells and do the math manually.
26	24	1.64	310	271
27	25	1.66
28	26	1.55
29	27	1.58
30	28	1.67
31	29	1.62
32	30	1.53
33	31	1.68
34	Min	1.53	222.0	209.0
35	Avg	1.69	263.3	246.5
36	Max	1.78	310.0	280.0
37	Total	51.08
38				253.0
39		Adjusted Average		244.3
40
Sheet 2

Cell Formulas
Range	Formula
B3:D33	C3	=IF('Wastewater Database'!D6="","",'Wastewater Database'!D6)
B34:D34	C34	=MIN(C3:C33)
B35:C35	C35	=AVERAGE(C5,C12,C19,C26)
D35	D35	=AVERAGE(D5,D12,D18,D26)
B36:D36	C36	=MAX(C3:C33)
B37	B37	=SUM(B3:B33)
D38	D38	=AVERAGE(D12,D18)
D39	D39	=AVERAGE(D5,D26,D38)

 

[James006]

Hi,

First of all, let me congratulate for your extremely clear explanations ...!!!
No doubt, this talent of yours will always serve you right ...

Had a look at a potential array formula, but never managed to make it work as anticipated ...

As a consequence, would suggest the following two macros :
1. An Event Macro for your Sheet2 to trigger the launch of a tailor-made macro
2. A standard macro in any module to perform your "Column Analysis"

first, the event macro :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Event Macro for Sheet2  -  Restricted Area : Columns C & D (i.e Columns 3 & 4)
If Target.Column < 2 Or Target.Column > 4 Then Exit Sub
' Launch Macro
MyAvg (Target.Column)
Cancel = True
End Sub

and the standard macro:

Sub MyAvg(ByVal j As Long)
' C W Loper - Long Way Around - MrExcel Forum Feb 1 2023
Dim i As Long, k As Long
Dim arr(0 To 4) As Integer
Dim lcount1 As Integer, lcount2 As Integer
Dim lsum1 As Double, lsum2 As Double, lavg1 As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
arr(0) = 33
    For i = arr(0) To 3 Step -1
        If Not IsEmpty(Cells(i, j)) Then
            k = k + 1
            arr(k) = Cells(i, j).Row
            If arr(k - 1) - arr(k) <> 7 Then
                Cells(i, j).Interior.Color = RGB(255, 192, 0)
                lcount1 = lcount1 + 1
                lsum1 = lsum1 + Cells(i, j).Value
                lavg1 = lsum1 / lcount1
                With Cells(38, j)
                    .Value = Format(lavg1, "#,##0.0")
                    .Interior.Color = RGB(255, 192, 0)
                    .Font.Bold = True
                End With
            Else
                lcount2 = lcount2 + 1
                lsum2 = lsum2 + Cells(i, j).Value
                If lavg1 > 0 Then
                    With Cells(39, j)
                        .Value = Format((lsum2 + lavg1) / (lcount2 + 1), "#,##0.0")
                        .Interior.Color = RGB(255, 192, 0)
                        .Font.Bold = True
                    End With
                End If
            End If
        End If
    Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help

 

[C W Loper]

Hi,

First of all, let me congratulate for your extremely clear explanations ...!!!
No doubt, this talent of yours will always serve you right ...

Had a look at a potential array formula, but never managed to make it work as anticipated ...

As a consequence, would suggest the following two macros :
1. An Event Macro for your Sheet2 to trigger the launch of a tailor-made macro
2. A standard macro in any module to perform your "Column Analysis"

first, the event macro :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Event Macro for Sheet2  -  Restricted Area : Columns C & D (i.e Columns 3 & 4)
If Target.Column < 2 Or Target.Column > 4 Then Exit Sub
' Launch Macro
MyAvg (Target.Column)
Cancel = True
End Sub

and the standard macro:

Sub MyAvg(ByVal j As Long)
' C W Loper - Long Way Around - MrExcel Forum Feb 1 2023
Dim i As Long, k As Long
Dim arr(0 To 4) As Integer
Dim lcount1 As Integer, lcount2 As Integer
Dim lsum1 As Double, lsum2 As Double, lavg1 As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
arr(0) = 33
    For i = arr(0) To 3 Step -1
        If Not IsEmpty(Cells(i, j)) Then
            k = k + 1
            arr(k) = Cells(i, j).Row
            If arr(k - 1) - arr(k) <> 7 Then
                Cells(i, j).Interior.Color = RGB(255, 192, 0)
                lcount1 = lcount1 + 1
                lsum1 = lsum1 + Cells(i, j).Value
                lavg1 = lsum1 / lcount1
                With Cells(38, j)
                    .Value = Format(lavg1, "#,##0.0")
                    .Interior.Color = RGB(255, 192, 0)
                    .Font.Bold = True
                End With
            Else
                lcount2 = lcount2 + 1
                lsum2 = lsum2 + Cells(i, j).Value
                If lavg1 > 0 Then
                    With Cells(39, j)
                        .Value = Format((lsum2 + lavg1) / (lcount2 + 1), "#,##0.0")
                        .Interior.Color = RGB(255, 192, 0)
                        .Font.Bold = True
                    End With
                End If
            End If
        End If
    Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help

James006;
Thanks for the response, currently, the workbook is not using Macros but I plan on converting to .xlsm once I get all of the little bugs worked out. I will run the code you provided and see how that does. If I come up with something, or it actually does what I am looking for, would you like to see a copy?
Also, while I have you on the hook, do you know if there is a way to have a .xlsm workbook automatically pull data from a non .xlsm workbook located in MS OneDrive?
Thanks Again

 

[James006]

Hi again,

Thanks for your Thanks
Once you have tested the macros, feel free to share your comments.

Regarding your other question about how to pull data from a OneDrive Excel File,
for the sake of simplicity, would recommend to go with Power Query :

https://excelexciting.com/get-data-from-onedrive-excel-file-using-power-query/

Hope this will help

 

[James006]

Glad to see you have found a solution