How to determine best months for Products

[Aussiebear]

In the attached workbook, I have 25 Yearly groupings of data (Rows - daily Minimum and Maximum temperatures ) and (Columns - Months). To the right of this, is my first attempt to determine which months for the Year 2000, have increased risk levels for Products A & B. Both Products are temperature constrained, with Product A will not work if temp is at or below 10°C, and is hazardous to the colony at temps 29.5°C or above, and Product B will not work at or below 15°C or at 40°C or above. In the right most data group, Columns AB:AM, you can see that I am attempting to find the number of days in the month, days in the month with Temps above 29.5°C, those days found expressed as a % of days in the month. Note I didn't bother calculating the risk level for 40°C because its extremely rare for our location to reach this result.

Then in the lower part of the right most data group, I then determine the days in the month for three seperate temps ( 10°C, 12°C & 15°C), then converted those days found into % of days per month.
The 12°C is as a result of attempting to build in a safety margin of 2°C for Product A. The main reason behind this that cold temps take considerably longer to increase, in our location due to shorter daylight lengths. This may result in 8 to 10 hours (33% to 42% of each 24 hour period, potentially allowing the product to work) of temps above the minimum limit. Varroa don't start their work day at 8am and knock off at 5pm. This will have a major limiting factor for the efficacy of the Product when applied.

I could rebuild this data group for each year but this will be extremely time consuming, and with all the formulas, very resource consuming as well. Is there a better method?


Eventually I need to compare the yearly results by establishing a whole of period (25 years data) to build sufficient evidence of which months are consistently Extremely High, Potentially or Minimal risk for the application of either Product A or B.

Can anyone assist me in either suggesting a method or a series of methods whereby I can achieve this result?

[Paul_Hossler]

You can play with this

I did a macro to rearrange your data into a more pivot table friendly arrangement and used a pivot table to do the heavy lifting

My spot checking the data seems to be OK

Capture.JPG

Option Explicit

Dim wsData As Worksheet, wsInput As Worksheet

Const tempHigh As Double = 29.5
Const tempLow1 As Double = 10
Const tempLow2 As Double = 12
Const tempLow3 As Double = 15

Sub Reformat()
    Dim r As Long, c As Long, y As Long, m As Long, d As Long, o As Long, z As Long, dmon As Long
    Dim dt As Date
    Dim mn As Double, mx As Double
    Dim rSort As Range, rSort1 As Range
    
    Application.ScreenUpdating = False


    'Set up worksheets
    Set wsInput = Worksheets("Sheet1")
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Data").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0


    Worksheets.Add.Name = "Data"
    Set wsData = Worksheets("Data")


    'format Data
    With wsData
        o = 1
        .Cells(1, 1).Value = "Date"
        .Cells(o, 2).Value = "Type"
        .Cells(o, 3).Value = "Count"
        o = o + 1
    End With
    
    With wsInput
        z = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        For r = 1 To z
            
            If Len(.Cells(r, 1).Value) = 0 Then GoTo NextR
            
            If IsNumeric(.Cells(r, 1).Value) Then       '   this is a year
                y = .Cells(r, 1).Value
'                Stop
        
            ElseIf VarType(.Cells(r, 1).Value) = vbString Then  '   this is 1st, 2nd, etc.
                
                If Not IsNumeric(Left(.Cells(r, 1).Value, Len(.Cells(r, 1).Value) - 2)) Then GoTo NextR '   this isGraph or something
                
                d = Left(.Cells(r, 1).Value, Len(.Cells(r, 1).Value) - 2)       '   this is the day
        
                For c = 0 To 11
                
                    m = c + 1   '   this is the month
                    
                    dmon = Day(DateSerial(y, m + 1, 0))
                    
                    If Len(.Cells(r, 2 * c + 2).Value) = 0 And Len(.Cells(r, 2 * c + 3).Value) = 0 Then GoTo NextC
                
                    dt = DateSerial(y, m, d)          '   OK to put data
                    
                    If Len(.Cells(r, 2 * c + 2).Value) <> 0 Then
                        mn = .Cells(r, 2 * c + 2).Value   '   min
                        If mn <= tempLow1 Then
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "Days Below " & tempLow1 & Chr(176)
                            wsData.Cells(o, 3).Value = 1
                            o = o + 1
                        
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "%Days Below " & tempLow1 & Chr(176)
                            wsData.Cells(o, 3).Value = 1# / dmon
                            o = o + 1
                        End If
                        
                        If mn <= tempLow2 Then
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "Days Below " & tempLow2 & Chr(176)
                            wsData.Cells(o, 3).Value = 1
                            o = o + 1
                        
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "%Days Below " & tempLow2 & Chr(176)
                            wsData.Cells(o, 3).Value = 1# / dmon
                            o = o + 1
                        End If
                        
                        If mn <= tempLow3 Then
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "Days Below " & tempLow3 & Chr(176)
                            wsData.Cells(o, 3).Value = 1
                            o = o + 1
                        
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "%Days Below " & tempLow3 & Chr(176)
                            wsData.Cells(o, 3).Value = 1# / dmon
                            o = o + 1
                        End If
                    End If
                    
                    If Len(.Cells(r, 2 * c + 3).Value) <> 0 Then
                        mx = .Cells(r, 2 * c + 3).Value   '   max
                        If mx >= tempHigh Then
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "Days Above " & tempHigh & Chr(176)
                            wsData.Cells(o, 3).Value = 1
                            o = o + 1
                        
                            wsData.Cells(o, 1).Value = dt
                            wsData.Cells(o, 2).Value = "%Days Above " & tempHigh & Chr(176)
                            wsData.Cells(o, 3).Value = 1# / dmon
                            o = o + 1
                        End If
                    End If
                    
NextC:
                Next c
            End If
        
NextR:
            Next r
    End With
    
            
    'define named range
    ThisWorkbook.Names.Add Name:="Data", RefersTo:=wsData.Cells(1, 1).CurrentRegion
    
    'sort
    With wsData
        Set rSort = .Cells(1, 1).CurrentRegion
        Set rSort1 = rSort.Cells(2, 1).Resize(rSort.Rows.Count - 1, rSort.Columns.Count)
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=rSort.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange [Data]
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    
    Worksheets("Analysis").PivotTables(1).PivotCache.Refresh
    
    Application.ScreenUpdating = True
    
    MsgBox "All Done"


End Sub

[Aussiebear]

Thank you Paul. From this I've been able to develop additional tables based on Months per year, for different temps over the 25 year period.

However it opens up a multitude of questions...... about where I'm headed.

[georgiboy]

Just for fun, I thought I would challenge myself to create a formula to do the same. Like I said, just for fun and not the practical solution Paul has offered up.

=LET(
   yr,A1,
   rng,B3:Y33,
   dim,DAY(EOMONTH(DATE(yr,SEQUENCE(,12),1),0)),
   lz,IF(rng="","",rng),
   d,HSTACK(TOCOL(CHOOSECOLS(lz,SEQUENCE(,12,1,2)),,1),TOCOL(CHOOSECOLS(lz,SEQUENCE(,12,2,2)),,1)),
   dt,TOCOL(DATE(yr,SEQUENCE(,12),SEQUENCE(31)),,1),
   t,HSTACK(dt,d),
   data,FILTER(t,(INDEX(t,,2)<>"")+(INDEX(t,,3)<>"")),
   sdta,HSTACK(data,IF(INDEX(data,,3)>=29.5,1)),
   abv29half,DROP(PIVOTBY(,MONTH(INDEX(sdta,,1)),INDEX(sdta,,4),COUNT),1,-1),
   abv29halpc,TEXT(abv29half/dim,"0%"),
   tdta,HSTACK(data,IF(INDEX(data,,2)<=10,1)),
   below10,DROP(PIVOTBY(,MONTH(INDEX(tdta,,1)),INDEX(tdta,,4),COUNT),1,-1),
   below10pc,TEXT(below10/dim,"0%"),
   udta,HSTACK(data,IF(INDEX(data,,2)<=12,1)),
   below12,DROP(PIVOTBY(,MONTH(INDEX(udta,,1)),INDEX(udta,,4),COUNT),1,-1),
   below12pc,TEXT(below12/dim,"0%"),
   vdta,HSTACK(data,IF(INDEX(data,,2)<=15,1)),
   below15,DROP(PIVOTBY(,MONTH(INDEX(vdta,,1)),INDEX(vdta,,4),COUNT),1,-1),
   below15pc,TEXT(below15/dim,"0%"),
   tbl,VSTACK(abv29half,abv29halpc,below10,below10pc,below12,below12pc,below15,below15pc),
   final,IF(VALUE(tbl)=0,"",tbl),
   mnths,HSTACK("",TEXT(DATE(yr,SEQUENCE(,12,1,1),1),"mmm")),
   rws,TOCOL({">= 29.5",">= 29.5 %","<= 10","<= 10 %","<= 12","<= 12 %","<= 15","<= 15 %"}),
   VSTACK(mnths,HSTACK(rws,final))
)

[Paul_Hossler]

Just for fun, I thought I would challenge myself to create a formula to do the same. Like I said, just for fun and not the practical solution Paul has offered up.

1: That makes my head hurt

2: I'd never be able to type or copy that in correctly

3: GoTo 1

[Aussiebear]

Thank you Georgiboy. Unfortunately your results differ from Paul's, so I started physically checking.

It seems that it only errors in the first row of "day" figures for >=29.5°C. All other rows are in agreement.

For example

Year	Month	Your Results	Physical check
2002	Nov	9	10
2003	Feb	5	6
2004	Sep	2	1
2005	Aug	1	0
2005	Sep	1	0
2005	Oct	9	8
2006	Mar	4	5
2006	Apr	1	0
2006	May	1	0

I didn't check any further down the data.

[Paul_Hossler]

Don't know if it affects anything, but F402 in the Post#1 file had a bit of anomolous data so it would show as a string and not a number


Capture.JPG

[Aussiebear]

There was a fair bit of that going on during the cut and Paste of information. The figure should have been 13.4

[Paul_Hossler]

There was a fair bit of that going on during the cut and Paste of information. The figure should have been 13.4

I made the correction when I was testing the macro since I got a runtime error trying to assign it to a Long

I don't know if that would cause a disconnect when using the formula since it might mess up the count

[Aussiebear]

The data from the BOM came out as Text, then after multiple attempts to force it to Numbers format, eventually it started to look right.

[p45cal]

See attached, Sheet1 (2), columns AB onwards.
For Product A 'Bad' means that for each day: either Min temp was 10 degrees or below, OR Max temp was 29.5 degrees or above. 'OK' means everything else.
For Product B 'Bad' means that for each day: either Min temp was below 15 degrees, OR Max temp was 40 degrees or above. 'OK' means everything else.
Days where there are not 2 temperatures are not included in the percentages.

2025-03-26_031544.jpg

[Aussiebear]

Very nice P45cal. I love the end result, but in not being proficient in Pivot Tables, I can only trust in the results. Thank you also for your effort.

[georgiboy]

Indeed F402 that Paul mentioned was being counted wrong, additional to that, the blanks were being counted as above 29.5. I corrected the formula by adding the VALUE function in the part that builds the temporary tables:

sdta,HSTACK(data,IF(VALUE(INDEX(data,,3))>=29.5,1)),

As I said before, this was just for fun and a PT is defo a better option here.

=LET(   yr,A1,
   rng,B3:Y33,
   dim,DAY(EOMONTH(DATE(yr,SEQUENCE(,12),1),0)),
   lz,IF(rng="","",rng),
   d,HSTACK(TOCOL(CHOOSECOLS(lz,SEQUENCE(,12,1,2)),,1),TOCOL(CHOOSECOLS(lz,SEQUENCE(,12,2,2)),,1)),
   dt,TOCOL(DATE(yr,SEQUENCE(,12),SEQUENCE(31)),,1),
   t,HSTACK(dt,d),
   data,FILTER(t,(INDEX(t,,2)<>"")+(INDEX(t,,3)<>"")),
   sdta,HSTACK(data,IF(VALUE(INDEX(data,,3))>=29.5,1)),
   abv29half,DROP(PIVOTBY(,MONTH(INDEX(sdta,,1)),INDEX(sdta,,4),COUNT),1,-1),
   abv29halpc,TEXT(abv29half/dim,"0%"),
   tdta,HSTACK(data,IF(VALUE(INDEX(data,,2))<=10,1)),
   below10,DROP(PIVOTBY(,MONTH(INDEX(tdta,,1)),INDEX(tdta,,4),COUNT),1,-1),
   below10pc,TEXT(below10/dim,"0%"),
   udta,HSTACK(data,IF(VALUE(INDEX(data,,2))<=12,1)),
   below12,DROP(PIVOTBY(,MONTH(INDEX(udta,,1)),INDEX(udta,,4),COUNT),1,-1),
   below12pc,TEXT(below12/dim,"0%"),
   vdta,HSTACK(data,IF(VALUE(INDEX(data,,2))<=15,1)),
   below15,DROP(PIVOTBY(,MONTH(INDEX(vdta,,1)),INDEX(vdta,,4),COUNT),1,-1),
   below15pc,TEXT(below15/dim,"0%"),
   tbl,VSTACK(abv29half,abv29halpc,below10,below10pc,below12,below12pc,below15,below15pc),
   final,IF(VALUE(tbl)=0,"",tbl),
   mnths,HSTACK("",TEXT(DATE(yr,SEQUENCE(,12,1,1),1),"mmm")),
   rws,TOCOL({">= 29.5",">= 29.5 %","<= 10","<= 10 %","<= 12","<= 12 %","<= 15","<= 15 %"}),
   VSTACK(mnths,HSTACK(rws,final))
)

[Aussiebear]

Thank you to everyone for assisting here.

[p45cal]

An update to include numbers and more:
The pivot now contains actual numbers of days as well as percentages.
There's a new table which allows you to create 'Products' with Max and Min temperatures (at cell AE2). This works by assigning 'Bad' to those days where the temperature is less than or equal to the Min OR the temperature is greater than or equal to the Max. [You can see this step in the Added Custom1 step of the ProductsEffectiveness query where the code is
if [Min] <= [Min.1] or [Max] >= [Max.1] then "Bad" else "OK"
this can be tweaked if necessary to handle missing data]
You can get days where say, the min is less than or equal to 10°C by putting 10 in the Min column, and a temperature that will always pass in the Max column eg. 100 (so all rows will be below 100). If you change any values in this table you'll need to refresh the pivot table (when the new 'Products' will appear in the Product slicer). After that you won't need to refresh again when choosing items in the slicers…

…talking of which, you can choose any combination of Products and Years using the mouse in the slicers with a combination the Ctrl or Shift keys.

There's a copy of the data converted to a table at cell AA1 which is easily pivot-able. This just for your information and isn't used and can be deleted - it's just a copy; the pivot table is fed from separate Power Query query in the background.

My results agree with Paul's largely; just out of interest, you say:

Year Month Your Results Physical check
2002 Nov 9 10

for values >=29.5°C

I get 9 too, made up of dates:
01/11/2002
03/11/2002
04/11/2002
09/11/2002
10/11/2002
13/11/2002
26/11/2002
27/11/2002
30/11/2002
2025-03-26_155333.jpg
being cells W71,W73,W74,W79,W80,W83,W96,W97,W100 (highlighted with red font in the attached).
Which is the extra cell you're counting to get 10?