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

31909



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...... :work: 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,SEQ UENCE(,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


31912

[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.

31913

[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,SEQ UENCE(,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 10for 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
31916
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?