{"text_format":"2","workbook":"xlsx","n_sheets":13,"sheets":["CSV-INPUT","Landing","FrontPage","DataQuality","Diagnosis1","Diagnosis2","DiagReport","Inputs","Calculations","Diagnosis","Initial Design","Glossary","Further Reading"],"not_rendered":{"charts":0,"images":13,"macros":1,"external_links":0,"defined_names":36,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"CSV-INPUT","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Landing","index":1,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Mastitis Control Plan Herd Pattern Analysis Tool"]
[]
[]
[]
[]
[null,null,"This tool relies on macros for functionality. If you are seeing this screen then"]
[null,null,"macros are currently disabled. For most versions of Excel, you'll see a button"]
[null,null,"labelled \"Enable content\" in the yellow toolbar above. Click this to continue."]
[null,null,"Alternatively, you may need to close then reopen the spreadsheet and choose"]
[null,null,"\"Enable macros\" during opening."]

{"sheet":"FrontPage","index":2,"n_rows":31,"n_cols":9,"rows_kept":31,"cut":false,"hidden":false,"merged":[[4,1,9,1],[10,1,14,1]]}
[]
[null,"Herd Pattern Analysis Tool"]
[]
[null,null,"All cows - lactations 1+","Qtr  0","Qtr  -1","Qtr  -2","Qtr  -3","Qtr  -4","Qtr  -5"]
[null,"SCC","BMSCC (,000 cells/ml)"]
[null,null,"% Chronic"]
[null,null,"% >200K"]
[null,null,"Lactation New IMI (%)"]
[null,null,"Dry Period New IMI (%)"]
[null,null,"'Apparent' Dry Period Cure (%)"]
[null,"mastitis","Dry Period Origin CM Rate  (in 12)"]
[null,null,"Lactating Period Origin CM Rate  (in 12)"]
[null,null,"Qrt CMIR (/100 cows/year)"]
[null,null,"Cow CMIR (/100 cows/year)"]
[null,null,"Clinical Mastitis Cure Rate (1st Case)"]
[null,null,"Heifers - lactation 1 only"]
[null,null,"Dry period new IMI (%)"]
[null,null,"Dry Period Origin CM Rate  (in 12)"]
{"$rows_empty":11}
[null,null,null,-1,{"$f":"D30-3","v":-4},{"$f":"E30-3","v":-7},-10,-13,-16]
[null,null,null,-3,-6,-9,-12,-15,-18]

{"sheet":"DataQuality","index":3,"n_rows":31,"n_cols":17,"rows_kept":31,"cut":false,"hidden":false,"merged":[[4,1,9,1],[10,1,14,1]]}
[]
[null,"Herd Pattern Analysis Tool"]
[]
[]
[null,null,"Data Quality Assessment","Qtr  0","Qtr  -1","Qtr  -2","Qtr  -3","Qtr  -4","Qtr  -5"]
[null,null,"Number of Recordings",{"$nulls":13},{"$f":"IF(OR(D6=0,E6=0,F6=0,G6=0,H6=0,I6=0),1,IF(OR(D6<2,E6<2,F6<2,G6<2,H6<2,I6<2),1,2))","v":1}]
[null,null,"Number of Calvings (All Cows)",{"$nulls":13},{"$f":"IF(OR(D7<10,E7<10,F7<10,G7<10,H7<10,I7<10),0,IF(OR(D7<20,E7<20,F7<20,G7<20,H7<20,I7<20),1,2))","v":0}]
[null,null,"Number of Calvings (Heifers)",{"$nulls":13},{"$f":"IF(OR(D8<10,E8<10,F8<10,G8<10,H8<10,I8<10),0,IF(OR(D8<20,E8<20,F8<20,G8<20,H8<20,I8<20),1,2))","v":0}]
[null,null,"Number of Clinical Cases",{"$nulls":13},{"$f":"IF(OR(D9<5,E9<5,F9<5,G9<5,H9<5,I9<5),0,IF(OR(D9<20,E9<20,F9<20,G9<20,H9<20,I9<20),1,2))","v":0}]
[{"$nulls":16},{"$f":"MAX(Q6:Q9)","v":1}]
[null,null,"Somatic Cell Count Data",{"$f":"IF(OR(D6=0,E6=0,F6=0,G6=0,H6=0,I6=0),\"Insufficient SCC data for robust interpretation\",IF(OR(D6<2,E6<2,F6<2,G6<2,H6<2,I6<2),\"Interpret SCC data with care\",\"Adequate SCC data available\"))","v":"Insufficient SCC data for robust interpretation"}]
[null,null,"Number of Calvings (All Cows)",{"$f":"IF(OR(D7<10,E7<10,F7<10,G7<10,H7<10,I7<10),\"Low numbers of cows calving - interpret with great care\",IF(OR(D7<20,E7<20,F7<20,G7<20,H7<20,I7<20),\"Low numbers of cows calving - interpret with care\",\"Sufficient cows calving for robust interpretation\"))","v":"Low numbers of cows calving - interpret with great care"}]
[null,null,"Number of Calvings (Heifers)",{"$f":"IF(OR(D8<10,E8<10,F8<10,G8<10,H8<10,I8<10),\"Low numbers of heifers calving - interpret with great care\",IF(OR(D8<20,E8<20,F8<20,G8<20,H8<20,I8<20),\"Low numbers of heifers calving - interpret with care\",\"Sufficient heifers calving for robust interpretation\"))","v":"Low numbers of heifers calving - interpret with great care"}]
[null,null,"Clinical Mastitis Data",{"$f":"IF(OR(D9<5,E9<5,F9<5,G9<5,H9<5,I9<5),\"Insufficient data for robust interpretation\",IF(OR(D9<20,E9<20,F9<20,G9<20,H9<20,I9<20),\"Interpret data with care\",\"Adequate data available\"))","v":"Insufficient data for robust interpretation"}]
[]
[null,null,null,{"$f":"IF(OR(FrontPage!D5=0,FrontPage!E5=0,FrontPage!F5=0,FrontPage!G5=0,FrontPage!H5=0,FrontPage!I5=0),\"BULK MILK SCC VALUE(S) REQUIRED\",\"\")","v":"BULK MILK SCC VALUE(S) REQUIRED"}]
{"$rows_empty":12}
[null,null,null,{"$f":"EOMONTH(TODAY(),D31)","v":"2019-10-31"},{"$f":"EOMONTH(TODAY(),E31)","v":"2019-07-31"},"2019-04-30","2019-01-31","2018-10-31","2018-07-31"]
[null,null,null,-1,{"$f":"D30-3","v":-4},{"$f":"E30-3","v":-7},-10,-13,-16]
[null,null,null,-3,-6,-9,-12,-15,-18]

{"sheet":"Diagnosis1","index":4,"n_rows":64,"n_cols":18,"rows_kept":64,"cut":false,"hidden":false,"merged":[[5,3,5,4],[5,5,5,6],[5,7,5,8],[5,9,5,10]]}
[]
[null,"Herd Pattern Analysis Tool"]
[]
[null,null,"Analysis from"]
[null,null,{"$f":"'CSV-INPUT'!B1","v":"00:00:00"},null,"Last 3 months",null,"Last 12 months",null,"12 - 18 months ago"]
[null,null,null,"Current",null,"Recent",null,"Historic",null,"Seasonality"]
[null,null,"Contagious",{"$f":"ROUND(D60,0)","v":50},{"$f":"D7","v":50},{"$f":"ROUND(E60,0)","v":50},{"$f":"F7","v":50},{"$f":"F60","v":37.5},{"$f":"H7","v":37.5},{"$f":"G60","v":81.944444430675745},{"$f":"J7","v":81.944444430675745}]
[null,null,"Environmental Dry Period",{"$f":"ROUND(D61,0)","v":13},{"$f":"D8","v":13},{"$f":"ROUND(E61,0)","v":38},38,{"$f":"F61","v":25},{"$f":"H8","v":25},{"$f":"G61","v":85.185185142901233},{"$f":"J8","v":85.185185142901233}]
[null,null,"Environmental Lactation",{"$f":"ROUND(D62,0)","v":63},63,{"$f":"ROUND(E62,0)","v":63},63,75,{"$f":"H9","v":75},49.999999990277779,{"$f":"J9","v":49.999999990277779}]
[null,null,"Specific Heifer Management",{"$f":"ROUND(D63,0)","v":0},0,{"$f":"ROUND(E63,0)","v":0},0,0,{"$f":"H10","v":0},0,{"$f":"J10","v":0}]
[null,null,"Clinical Mastitis Recurrence",{"$f":"IF(D64=\"NO DATA\",\"NO DATA\",ROUND(D64,0))","v":50},50,{"$f":"IF(E64=\"NO DATA\",\"NO DATA\",ROUND(E64,0))","v":50},50,{"$f":"IF(F64=\"NO DATA\",\"NO DATA\",ROUND(F64,0))","v":50},{"$f":"H11","v":50}]
[]
[null,null,"Click for more info"]
[null,null,"Key points",{"$f":"\"Predominant current issue\" & IF(O27=2,\"s are \",\" is \") & C27","v":"Predominant current issue is EL"},{"$nulls":6},{"$f":"IF(DataQuality!Q10>0,\"Small numbers/missing info mean that at least\",\"\")","v":"Small numbers/missing info mean that at least"}]
[null,null,null,{"$f":"\"Predominant recent issue\"&IF(O28=2,\"s are \",\" is \")&C28","v":"Predominant recent issue is EL"},{"$nulls":6},{"$f":"IF(DataQuality!Q10=1,\"one measure above should be interpreted with caution.\", \"one measure above may be unreliable.\")","v":"one measure above should be interpreted with caution."}]
[null,null,null,{"$f":"\"Specific heifer management issues \"&IF(E29=1,\"\",\"do not \")&\"currently appear to be important\"","v":"Specific heifer management issues do not currently appear to be important"},{"$nulls":6},{"$f":"IF(DataQuality!Q10>0,\"Click the button below to go back and review this.\",\"\")","v":"Click the button below to go back and review this."}]
[null,null,null,{"$f":"IF(E30=\"NO DATA\",\"TAKE CARE THIS DATASET LACKS ROBUST CLINICAL MASTITIS DATA\",\"Recurrent clinical mastitis is \"&IF(E30=1,\"\",\"not\")&\" currently an important feature\")","v":"Recurrent clinical mastitis is not currently an important feature"}]
[null,null,null,{"$f":"\"There are \"&IF(E31=1,\"\",\"no \")&\"clear important seasonal patterns\"","v":"There are clear important seasonal patterns"}]
[null,null,"Click to see references to research"]
[]
[]
[]
[]
[]
[]
[{"$nulls":4},"category","Cont>75","EDP>75","EL>75","#>75","Max","ContMax","EDPMax","ELMax","MaxCat","SingPlur",null,"Cat","FullText"]
[null,null,{"$f":"VLOOKUP(E27,Q27:R33,2,FALSE)","v":"EL"},"Curr",{"$f":"IF(I27>=1,CONCATENATE(F27,G27,H27),N27)","v":"L"},{"$f":"IF(D7>=75,\"C\",\"\")","v":""},{"$f":"IF(D8>=75,\"D\",\"\")","v":""},{"$f":"IF(D9>=75,\"L\",\"\")","v":""},{"$f":"COUNTIF(D7:D9,\">=75\")","v":0},{"$f":"MAX(D7:D9)","v":63},{"$f":"IF(D7=J27,\"C\",\"\")","v":""},{"$f":"IF(J27=D8,\"D\",\"\")","v":""},{"$f":"IF(J27=D9,\"L\",\"\")","v":"L"},{"$f":"CONCATENATE(K27,L27,M27)","v":"L"},{"$f":"IF(LEN(E27)=1,1,2)","v":1},null,"C","contagious"]
[null,null,{"$f":"VLOOKUP(E28,Q27:R33,2,FALSE)","v":"EL"},"Recent",{"$f":"IF(I28>=1,CONCATENATE(F28,G28,H28),N28)","v":"L"},{"$f":"IF(F7>=75,\"C\",\"\")","v":""},{"$f":"IF(F8>=75,\"D\",\"\")","v":""},{"$f":"IF(F9>=75,\"L\",\"\")","v":""},{"$f":"COUNTIF(F7:F9,\">=75\")","v":0},{"$f":"MAX(F7:F9)","v":63},{"$f":"IF(F7=J28,\"C\",\"\")","v":""},{"$f":"IF(J28=F8,\"D\",\"\")","v":""},{"$f":"IF(J28=F9,\"L\",\"\")","v":"L"},{"$f":"CONCATENATE(K28,L28,M28)","v":"L"},{"$f":"IF(LEN(E28)=1,1,2)","v":1},null,"D","EDP"]
[null,null,null,"Heifer",{"$f":"IF(D10>=75,1,0)","v":0},{"$nulls":11},"L","EL"]
[null,null,null,"Recurr",{"$f":"IF(D11=\"NO DATA\",\"NO DATA\",IF(D11>=75,1,0))","v":0},{"$nulls":11},"CD","contagious and EDP"]
[null,null,null,"Seas",{"$f":"IF(MAX(J7:J10)>=75,1,0)","v":1},{"$nulls":11},"CL","contagious and EL"]
[{"$nulls":16},"CDL","contagious, EL and EDP"]
[{"$nulls":16},"DL","EL and EDP"]
{"$rows_empty":25}
[null,null,null,"Current","Recent","Historic","Seasonality"]
[null,null,"Contagious Issues",50,50,37.5,81.944444430675745]
[null,null,"Dry Period Environmental Issues",12.5,37.5,25,85.185185142901233]
[null,null,"Lactating Period Environmental Issues",62.5,62.5,75,49.999999990277779]
[null,null,"Specific Heifer Management Issues",0,0,0,0]
[null,null,"Clinical Mastitis Recurrence Issues",50,50,50]

{"sheet":"Diagnosis2","index":5,"n_rows":64,"n_cols":10,"rows_kept":64,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Herd Pattern Analysis Tool"]
[]
[]
[]
[null,null,"current","qtr  -1","qtr  -2","qtr  -3","qtr  -4","qtr  -5",null,"historic…………...trend……………..current"]
[null,null,{"$f":"D60","v":50},0,43.75,31.25,37.5,0,"Contagious"]
[null,null,12.5,0,37.5,25,25,0,"Environmental Dry Period"]
[null,null,62.5,50,37.5,62.5,75,75,"Environmental Lactating Period "]
[null,null,0,0,0,0,0,0,"Specific Heifer Management"]
[null,null,50,0,50,0,50,0,"Clinical Mastitis Recurrence"]
{"$rows_empty":47}
[null,null,null,"Current Quarter","Quarter -1","Quarter -2","Quarter -3","Quarter -4","Quarter -5"]
[null,null,"Contagious Issues",50,0,43.75,31.25,37.5,0]
[null,null,"Dry Period Environmental Issues",12.5,0,37.5,25,25,0]
[null,null,"Lactating Period Environmental Issues",62.5,50,37.5,62.5,75,75]
[null,null,"Specific Heifer Management Issues",0,0,0,0,0,0]
[null,null,"Clinical Mastitis Recurrence Issues",50,0,50,0,50,0]

{"sheet":"DiagReport","index":6,"n_rows":26,"n_cols":34,"rows_kept":26,"cut":false,"hidden":false,"merged":[[25,2,25,4],[5,2,5,3],[5,4,5,5],[5,6,5,7],[5,8,5,9],[21,2,21,4],[22,2,22,4],[23,2,23,4],[24,2,24,4]]}
[{"$nulls":20},"category","Cont>75","EDP>75","EL>75","#>75","Max","ContMax","EDPMax","ELMax","MaxCat","SingPlur",null,"Cat","FullText"]
[{"$nulls":18},{"$f":"VLOOKUP(U2,AG2:AH8,2,FALSE)","v":"EL"},"Curr",{"$f":"IF(Y2>=1,CONCATENATE(V2,W2,X2),AD2)","v":"L"},{"$f":"IF(C7>=75,\"C\",\"\")","v":""},{"$f":"IF(C8>=75,\"D\",\"\")","v":""},{"$f":"IF(C9>=75,\"L\",\"\")","v":""},{"$f":"COUNTIF(C7:C9,\">=75\")","v":0},{"$f":"MAX(C7:C9)","v":63},{"$f":"IF(C7=Z2,\"C\",\"\")","v":""},{"$f":"IF(Z2=C8,\"D\",\"\")","v":""},{"$f":"IF(Z2=C9,\"L\",\"\")","v":"L"},{"$f":"CONCATENATE(AA2,AB2,AC2)","v":"L"},{"$f":"IF(LEN(U2)=1,1,2)","v":1},null,"C","contagious"]
[{"$nulls":18},{"$f":"VLOOKUP(U3,AG2:AH8,2,FALSE)","v":"EL"},"Recent",{"$f":"IF(Y3>=1,CONCATENATE(V3,W3,X3),AD3)","v":"L"},{"$f":"IF(E7>=75,\"C\",\"\")","v":""},{"$f":"IF(E8>=75,\"D\",\"\")","v":""},{"$f":"IF(E9>=75,\"L\",\"\")","v":""},{"$f":"COUNTIF(E7:E9,\">=75\")","v":0},{"$f":"MAX(E7:E9)","v":63},{"$f":"IF(E7=Z3,\"C\",\"\")","v":""},{"$f":"IF(Z3=E8,\"D\",\"\")","v":""},{"$f":"IF(Z3=E9,\"L\",\"\")","v":"L"},{"$f":"CONCATENATE(AA3,AB3,AC3)","v":"L"},{"$f":"IF(LEN(U3)=1,1,2)","v":1},null,"D","EDP"]
[null,"  Herd Pattern Analysis Tool",{"$nulls":17},"Heifer",{"$f":"IF(C10>=75,1,0)","v":0},{"$nulls":11},"L","EL"]
[null,{"$f":"'CSV-INPUT'!B1","v":"00:00:00"},null,"Last 3 months",null,"Last 12 months",null,"12 - 18 months ago",{"$nulls":11},"Recurr",{"$f":"IF(C11=\"NO DATA\",\"NO DATA\",IF(C11>=75,1,0))","v":0},{"$nulls":11},"CD","contagious and EDP"]
[null,null,"Current",null,"Recent",null,"Historic",null,"Seasonality",{"$nulls":10},"Seas",{"$f":"IF(MAX(I7:I10)>=75,1,0)","v":1},{"$nulls":11},"CL","contagious and EL"]
[null,"Contagious",{"$f":"Diagnosis1!D7","v":50},{"$f":"C7","v":50},{"$f":"Diagnosis1!F7","v":50},{"$f":"E7","v":50},{"$f":"Diagnosis1!H7","v":37.5},{"$f":"G7","v":37.5},{"$f":"Diagnosis1!J7","v":81.944444430675745},{"$f":"I7","v":81.944444430675745},{"$nulls":22},"CDL","contagious, EL and EDP"]
[null,"Environmental Dry Period ",{"$f":"Diagnosis1!D8","v":13},{"$f":"C8","v":13},{"$f":"Diagnosis1!F8","v":38},38,{"$f":"Diagnosis1!H8","v":25},{"$f":"G8","v":25},{"$f":"Diagnosis1!J8","v":85.185185142901233},{"$f":"I8","v":85.185185142901233},{"$nulls":22},"DL","EL and EDP"]
[null,"Environmental Lactation",{"$f":"Diagnosis1!D9","v":63},63,{"$f":"Diagnosis1!F9","v":63},63,{"$f":"Diagnosis1!H9","v":75},75,{"$f":"Diagnosis1!J9","v":49.999999990277779},49.999999990277779]
[null,"Specific Heifer Management",{"$f":"Diagnosis1!D10","v":0},0,{"$f":"Diagnosis1!F10","v":0},0,{"$f":"Diagnosis1!H10","v":0},0,{"$f":"Diagnosis1!J10","v":0},0]
[null,"Clinical Mastitis Recurrence",{"$f":"Diagnosis1!D11","v":50},50,{"$f":"Diagnosis1!F11","v":50},50,{"$f":"Diagnosis1!H11","v":50},50]
[]
[null,"Key points"]
[null,{"$f":"\"Predominant current issue\" & IF(AE2=2,\"s are \",\" is \") & S2","v":"Predominant current issue is EL"},{"$nulls":5},{"$f":"IF(DataQuality!Q10>0,\"Small numbers/missing info mean that at least\",\"\")","v":"Small numbers/missing info mean that at least"}]
[null,{"$f":"\"Predominant recent issue\"&IF(AE3=2,\"s are \",\" is \")&S3","v":"Predominant recent issue is EL"},{"$nulls":5},{"$f":"IF(DataQuality!Q10=1,\"one measure above should be interpreted with caution.\", \"one measure above may be unreliable.\")","v":"one measure above should be interpreted with caution."}]
[null,{"$f":"\"Specific heifer management issues \"&IF(U4=1,\"\",\"do not \")&\"currently appear to be important\"","v":"Specific heifer management issues do not currently appear to be important"}]
[null,{"$f":"IF(U5=\"NO DATA\",\"TAKE CARE THIS DATASET LACKS ROBUST CLINICAL MASTITIS DATA\",\"Recurrent clinical mastitis is \"&IF(U5=1,\"\",\"not\")&\" currently an important feature\")","v":"Recurrent clinical mastitis is not currently an important feature"}]
[null,{"$f":"\"There are \"&IF(U6=1,\"\",\"no \")&\"clear important seasonal patterns\"","v":"There are clear important seasonal patterns"}]
[]
[]
[null,{"$f":"Diagnosis2!J6","v":"historic…………...trend……………..current"},null,null,null,"current","qtr  -1","qtr  -2","qtr  -3","qtr  -4","qtr  -5"]
[null,null,"Contagious",null,null,{"$f":"Diagnosis2!C7","v":50},{"$f":"Diagnosis2!D7","v":0},{"$f":"Diagnosis2!E7","v":43.75},{"$f":"Diagnosis2!F7","v":31.25},{"$f":"Diagnosis2!G7","v":37.5},{"$f":"Diagnosis2!H7","v":0}]
[null,null,"Environmental Dry Period",null,null,{"$f":"Diagnosis2!C8","v":12.5},{"$f":"Diagnosis2!D8","v":0},{"$f":"Diagnosis2!E8","v":37.5},{"$f":"Diagnosis2!F8","v":25},{"$f":"Diagnosis2!G8","v":25},{"$f":"Diagnosis2!H8","v":0}]
[null,null,"Environmental Lact'n",null,null,{"$f":"Diagnosis2!C9","v":62.5},{"$f":"Diagnosis2!D9","v":50},{"$f":"Diagnosis2!E9","v":37.5},{"$f":"Diagnosis2!F9","v":62.5},{"$f":"Diagnosis2!G9","v":75},{"$f":"Diagnosis2!H9","v":75}]
[null,null,"Heifer Management",null,null,{"$f":"Diagnosis2!C10","v":0},{"$f":"Diagnosis2!D10","v":0},{"$f":"Diagnosis2!E10","v":0},{"$f":"Diagnosis2!F10","v":0},{"$f":"Diagnosis2!G10","v":0},{"$f":"Diagnosis2!H10","v":0}]
[null,null,"Clinical Mast Recurrence",null,null,{"$f":"Diagnosis2!C11","v":50},{"$f":"Diagnosis2!D11","v":0},{"$f":"Diagnosis2!E11","v":50},{"$f":"Diagnosis2!F11","v":0},{"$f":"Diagnosis2!G11","v":50},{"$f":"Diagnosis2!H11","v":0}]

{"sheet":"Inputs","index":7,"n_rows":55,"n_cols":27,"rows_kept":55,"cut":false,"hidden":true,"merged":[]}
[{"$nulls":9},"Contagious vs Environmental",null,"Environmental Dry Period",null,"Environmental Lactating Period",null,"    Heifer Management",null,"   Recurrence Factor",{"$nulls":5},"Collated Scores"]
[null,"Current Qrt (0)","Qrt -1","Qrt -2","Qrt -3","Qrt -4","Qrt -5","Data Source",null,"Low Score Threshold","High Score Threshold","Low Score Threshold","High Score Threshold","Low Score Threshold","High Score Threshold","Low Score Threshold","High Score Threshold","Low Score Threshold","High Score Threshold",null,"Parameter","Qrt 0 Score","Qrt -1 Score","Qrt -2 Score","Qrt -3 Score","Qrt -4 Score","Qrt -5 Score"]
["COW DATA",{"$nulls":19},"Contagious vs Environmental"]
["SCC Parameters",{"$nulls":19},"BMSCC",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B5<$J5,0,IF(B5>$K5,2,1))))","v":0},0,0,0,0,0]
["BMSCC (,000 cells/ml)",118,null,155,154,144,null,"Herd Mastitis Summary",null,200,300,{"$nulls":9},"Chronics",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B6<$J6,0,IF(B6>$K6,2,1))))","v":0},0,0,0,0,0]
["% Chronic",2.9,null,5.8,3.3,1.6,null,"Herd Mastitis Summary",null,10,20,{"$nulls":9},">200K",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B7<$J7,0,IF(B7>$K7,2,1))))","v":0},0,0,0,0,0]
["% >200K",6.4,null,14.5,10.4,7.2,null,"Herd Mastitis Summary",null,20,30,{"$nulls":9},"Chronic:>200K Ratio",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B8<$J8,0,IF(B8>$K8,2,1))))","v":1},0,1,0,0,0]
["Chronic:>200K Ratio",{"$f":"B6/(B7+0.00000001)","v":0.45312499929199218},0,0.39999999972413791,0.31730769200258868,0.22222222191358026,{"$f":"G6/(G7+0.00000001)","v":0},null,null,0.33333333300000001,0.66666666600000002,{"$nulls":9},"DP Apparent Cure",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B11>$J11,0,IF(B11<$K11,2,1))))","v":2},0,2,2,2,0]
["Lactation New IMI (%)",10,null,6.4,0,8.6999999999999993,null,"Herd Mastitis Summary",null,null,null,15,7,7,10,{"$nulls":5},"DP 'Actual' Cure",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B12>$J12,0,IF(B12<$K12,2,1))))","v":2},0,2,2,2,0]
["Dry Period New IMI (%)",1.2,null,2.1,0,5.0999999999999996,null,"Herd Mastitis Summary",null,null,null,10,20,33,10,{"$nulls":5},"CM Cure",{"$f":"IF(B18<5,\"NO DATA\",IF(B55=0,0,IF(B55=\"\",0,IF(B19>$J19,0,IF(B19<$K19,2,1)))))","v":2},0,1,1,1,0]
["'Apparent' Dry Period Cure (%)",0,null,38,0,13.3,null,"Herd Mastitis Summary",null,75,60,{"$nulls":9},"CM Recurrence Factor",{"$f":"IF(B18<5,\"NO DATA\",IF(B20>$R20,0,IF(B20<$S20,2,1)))","v":1},{"$f":"IF(C18<5,\"NO DATA\",IF(C20>$R20,0,IF(C20<$S20,2,1)))","v":0},1,0,1,0]
["'Actual' Dry Period Cure (%)",{"$f":"B10+B11","v":1.2},0,40.1,0,18.399999999999999,0,null,null,85,75,{"$nulls":9},"Total 'Contagious' Score",{"$f":"SUM(V4:V11)","v":8},0,7,5,6,0]
["Clinical Mastitis Parameters"]
["Dry Period Origin CM Rate  (in 12)",0.26,0.31,0.69,0,1.18,0.56000000000000005,"Herd Mastitis Summary",null,null,null,1,2,{"$nulls":7},"Dry Period Environmental"]
["Lactating Period Origin CM Rate  (in 12)",1.26,1.0900000000000001,0.98,2.15,2.84,2.1800000000000002,"Herd Mastitis Summary",{"$nulls":5},2,4,{"$nulls":5},"DP New IMI",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B10<$L10,0,IF(B10>$M10,2,1))))","v":0},0,0,0,0,0]
["DPO Ratio",{"$f":"B14/(B14+B15+0.000000001)","v":0.17105263146641272},{"$f":"C14/(C14+C15+0.000000001)","v":0.22142857127040813},0.41317365244720139,0,0.29353233823543973,0.20437956196920454,{"$nulls":4},0.33333332999999998,0.66666665999999997,0.66666669999999995,0.33333332999999998,{"$nulls":5},"Lact New IMI",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B9>$L9,0,IF(B9<$M9,2,1))))","v":1},0,2,2,1,0]
["Qrt CMIR (/100 cows/year)",22,16,25,28,61,35,"Herd Mastitis Summary",{"$nulls":12},"DPO CM",{"$f":"IF(B18<5,\"NO DATA\",IF(B14<$L14,0,IF(B14>$M14,2,1)))","v":0},{"$f":"IF(C18<5,\"NO DATA\",IF(C14<$L14,0,IF(C14>$M14,2,1)))","v":0},0,0,1,0]
["Cow CMIR (/100 cows/year)",22,16,24,28,59,31,"Herd Mastitis Summary",{"$nulls":12},"DPO Ratio",{"$f":"IF(B18<5,\"NO DATA\",IF(B16<$L16,0,IF(B16>$M16,2,1)))","v":0},{"$f":"IF(C18<5,\"NO DATA\",IF(C16<$L16,0,IF(C16>$M16,2,1)))","v":0},1,0,0,0]
["Clinical Mastitis Cure Rate (1st Case)",20,26,30,35,27,22,"12 month rolling 1st clinical case cure rate",null,40,25,{"$nulls":9},"Total 'Dry Period' Score",{"$f":"SUM(V15:V18)","v":1},0,3,2,2,0]
["Clinical Mastitis Recurrence Factor",{"$f":"((B14+B15)*8.5)/((B17+B18+0.0000000001)/2)","v":0.58727272727139257},{"$f":"((C14+C15)*8.5)/((C17+C18+0.0000000001)/2)","v":0.74374999999767577},0.57938775510085838,0.65267857142740582,0.56949999999952527,0.70575757575650644,{"$nulls":10},0.6,0.4]
["HEIFER DATA",{"$nulls":19},"Lactating  Period Environmental"]
["SCC Parameters",{"$nulls":19},"DP New IMI",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B10>$N10,0,IF(B10<$O10,2,1))))","v":2},0,2,2,2,0]
[{"$nulls":20},"Lact New IMI",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B9<$N9,0,IF(B9>$O9,2,1))))","v":1},0,0,0,1,0]
[{"$nulls":20},"LPO CM",{"$f":"IF(B18<5,\"NO DATA\",IF(B15<$N15,0,IF(B15>$O15,2,1)))","v":0},{"$f":"IF(C18<5,\"NO DATA\",IF(C15<$N15,0,IF(C15>$O15,2,1)))","v":0},0,1,1,1]
[{"$nulls":20},"LPO Ratio",{"$f":"IF(B18<5,\"NO DATA\",IF(B16>$N16,0,IF(B16<$O16,2,1)))","v":2},{"$f":"IF(C18<5,\"NO DATA\",IF(C16>$N16,0,IF(C16<$O16,2,1)))","v":2},1,2,2,2]
[]
[{"$nulls":20},"Total 'Lactating Period' Score",{"$f":"SUM(V22:V25)","v":5},2,3,5,6,3]
[]
[]
["SUM of No Infected",{"$f":"SUM(B27:B29)","v":0},0,0,0,0,0]
["Heifer DPIMI Rate (Qrt)",{"$f":"B30/B26*100","v":{"$error":"#DIV/0!"}},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"},{"$error":"#DIV/0!"}]
["Clinical mastitis Parameters"]
[{"$nulls":20},"Heifer Management"]
[{"$nulls":20},"DP New IMI",{"$f":"IF(B55=0,0,IF(B55=\"\",0,IF(B47<$P47,0,IF(B47>$Q47,2,1))))","v":0},0,0,0,0,0]
[{"$nulls":20},"DPO CM",{"$f":"IF(B18<5,\"NO DATA\",IF(B48<$P48,0,IF(B48>$Q48,2,1)))","v":0},{"$f":"IF(C18<5,\"NO DATA\",IF(C48<$P48,0,IF(C48>$Q48,2,1)))","v":0},0,0,0,0]
[]
[]
[]
[]
[{"$nulls":20},"Total 'Heifer' Score",{"$f":"SUM(V34:V35)","v":0},0,0,0,0,0]
[{"$nulls":20},"Relative 'Heifer' Score",{"$f":"V40/((V19+0.0000000001)/2)","v":0},{"$f":"W40/((W19+0.0000000001)/2)","v":0},0,0,0,0]
[]
["Heifer DP  (Month 1) - Adjusted Rate",{"$f":"B40*B37","v":0},0,0,0,0,0]
["Heifer DP  (Month 2) - Adjusted Rate",0,0,0,0,0,0]
["Heifer DP  (Month 3) - Adjusted Rate",0,0,0,0,0,0]
["Calculated Rate",{"$f":"B43+B44+B45","v":0},0,0,0,0,0]
["Dry Period New IMI (%)",0,null,3.6,0,0,{"$nulls":9},10,20,null,null,null,"Recurrence"]
["Dry Period Origin CM Rate  (in 12)",0,0.31,0.15,0,0.26,0,{"$nulls":8},1,2,null,null,null,"Recurrence Score",{"$f":"IF(B18<5,\"NO DATA\",IF(B20>$R20,0,IF(B20<$S20,2,1)))","v":1},{"$f":"IF(C18<5,\"NO DATA\",IF(C20>$R20,0,IF(C20<$S20,2,1)))","v":0},1,0,1,0]
[]
["Number of Recordings",1,0,2,1,4,0,{"$nulls":19},"Notes"]
["Number of Calvings (All Cows)",141,92,123,57,132,124,{"$nulls":19},"Cows = All females parity >=1 (ie includes heifers)"]
["Number of Calvings (Heifers)",28,23,10,29,19,30,{"$nulls":19},"Heifers = Parity =1 (ie heifers alone)"]
["Number of Clinical Cases",22,16,24,28,59,31,{"$nulls":19},"Select current quarter based on either season (housed animals) or key management change (eg housing or turnout)"]
[]
["BMSCC (Again just in case something different about refs to csv import)",{"$f":"B5","v":118},{"$f":"C5","v":0},155,154,144,0]

{"sheet":"Calculations","index":8,"n_rows":40,"n_cols":18,"rows_kept":40,"cut":false,"hidden":true,"merged":[]}
[null,null,null,"Collated Scores"]
["Parameter","Qrt 0 Score","Qrt -1 Score","Qrt -2 Score","Qrt -3 Score","Qrt -4 Score","Qrt -5 Score","Max Score","Min Score","Mean Score","Seasonality Score"]
["Contagious vs Environmental",{"$nulls":13},"Current","Recent","Historic","Seasonality"]
["BMSCC",{"$f":"Inputs!V4","v":0},{"$f":"Inputs!W4","v":0},{"$f":"Inputs!X4","v":0},{"$f":"Inputs!Y4","v":0},{"$f":"Inputs!Z4","v":0},{"$f":"Inputs!AA4","v":0},{"$nulls":6},"Contagious Issues",{"$f":"B13","v":50},{"$f":"MAX(B13:E13)","v":50},{"$f":"MAX(F13:G13)","v":37.5},{"$f":"K12","v":81.944444430675745}]
["Chronics",{"$f":"Inputs!V5","v":0},{"$f":"Inputs!W5","v":0},{"$f":"Inputs!X5","v":0},{"$f":"Inputs!Y5","v":0},{"$f":"Inputs!Z5","v":0},{"$f":"Inputs!AA5","v":0},{"$nulls":6},"Dry Period Environmental Issues",{"$f":"B21","v":12.5},{"$f":"MAX(B21:E21)","v":37.5},{"$f":"MAX(F21:G21)","v":25},{"$f":"K20","v":85.185185142901233}]
[">200K",{"$f":"Inputs!V6","v":0},{"$f":"Inputs!W6","v":0},{"$f":"Inputs!X6","v":0},{"$f":"Inputs!Y6","v":0},{"$f":"Inputs!Z6","v":0},{"$f":"Inputs!AA6","v":0},{"$nulls":6},"Lactating Period Environmental Issues",{"$f":"B29","v":62.5},{"$f":"MAX(B29:E29)","v":62.5},{"$f":"MAX(F29:G29)","v":75},{"$f":"K28","v":49.999999990277779}]
["Chronic:>200K Ratio",{"$f":"Inputs!V7","v":1},{"$f":"Inputs!W7","v":0},{"$f":"Inputs!X7","v":1},{"$f":"Inputs!Y7","v":0},{"$f":"Inputs!Z7","v":0},{"$f":"Inputs!AA7","v":0},{"$nulls":6},"Specific Heifer Management Issues",{"$f":"B36","v":0},{"$f":"MAX(B36:E36)","v":0},{"$f":"MAX(F36:G36)","v":0},{"$f":"K35","v":0}]
["DP Apparent Cure",{"$f":"Inputs!V8","v":2},{"$f":"Inputs!W8","v":0},{"$f":"Inputs!X8","v":2},{"$f":"Inputs!Y8","v":2},{"$f":"Inputs!Z8","v":2},{"$f":"Inputs!AA8","v":0},{"$nulls":6},"Clinical Mastitis Recurrence Issues",{"$f":"B40","v":50},{"$f":"MAX(B40:E40)","v":50},{"$f":"MAX(F40:G40)","v":50}]
["DP 'Actual' Cure",{"$f":"Inputs!V9","v":2},{"$f":"Inputs!W9","v":0},{"$f":"Inputs!X9","v":2},{"$f":"Inputs!Y9","v":2},{"$f":"Inputs!Z9","v":2},{"$f":"Inputs!AA9","v":0}]
["CM Cure",{"$f":"Inputs!V10","v":2},{"$f":"Inputs!W10","v":0},{"$f":"Inputs!X10","v":1},{"$f":"Inputs!Y10","v":1},{"$f":"Inputs!Z10","v":1},{"$f":"Inputs!AA10","v":0}]
["CM Recurrence Factor",{"$f":"Inputs!V11","v":1},{"$f":"Inputs!W11","v":0},{"$f":"Inputs!X11","v":1},{"$f":"Inputs!Y11","v":0},{"$f":"Inputs!Z11","v":1},{"$f":"Inputs!AA11","v":0},{"$nulls":6},"Notes:"]
["Total 'Contagious' Score",{"$f":"Inputs!V12","v":8},{"$f":"Inputs!W12","v":0},{"$f":"Inputs!X12","v":7},{"$f":"Inputs!Y12","v":5},{"$f":"Inputs!Z12","v":6},{"$f":"Inputs!AA12","v":0},{"$f":"MAX(B12:G12)+0.000000001","v":8.0000000010000001},{"$f":"MIN(B12:G12)+0.000000001","v":1.0000000000000001E-9},{"$f":"AVERAGE(B12:G12)+0.000000001","v":4.3333333343333331},{"$f":"(100-(((I12)/H12)*100)+(100-(((J12)/H12)*100)+(100-(((I12)/J12)*100))))/3","v":81.944444430675745},null,null,"Current = This quarter"]
["% 'Contagious' Score",{"$f":"IF(B10=\"NO DATA\",B12/12*100,IF(Inputs!B55=0,B12/1*100,IF(Inputs!B55=\"\",B12/1*100,B12/16*100)))","v":50},{"$f":"IF(C10=\"NO DATA\",C12/12*100,IF(Inputs!C55=0,C12/1*100,IF(Inputs!C55=\"\",C12/1*100,C12/16*100)))","v":0},{"$f":"IF(D10=\"NO DATA\",D12/12*100,IF(Inputs!D55=0,D12/1*100,IF(Inputs!D55=\"\",D12/1*100,D12/16*100)))","v":43.75},{"$f":"IF(E10=\"NO DATA\",E12/12*100,IF(Inputs!E55=0,E12/1*100,IF(Inputs!E55=\"\",E12/1*100,E12/16*100)))","v":31.25},{"$f":"IF(F10=\"NO DATA\",F12/12*100,IF(Inputs!F55=0,F12/1*100,IF(Inputs!F55=\"\",F12/1*100,F12/16*100)))","v":37.5},{"$f":"IF(G10=\"NO DATA\",G12/12*100,IF(Inputs!G55=0,G12/1*100,IF(Inputs!G55=\"\",G12/1*100,G12/16*100)))","v":0},{"$nulls":6},"Recent = This year"]
[{"$nulls":13},"Historic = > 1 year, but < 18 months"]
["Dry Period Environmental"]
["DP New IMI",{"$f":"Inputs!V15","v":0},{"$f":"Inputs!W15","v":0},{"$f":"Inputs!X15","v":0},{"$f":"Inputs!Y15","v":0},{"$f":"Inputs!Z15","v":0},{"$f":"Inputs!AA15","v":0}]
["Lact New IMI",{"$f":"Inputs!V16","v":1},{"$f":"Inputs!W16","v":0},{"$f":"Inputs!X16","v":2},{"$f":"Inputs!Y16","v":2},{"$f":"Inputs!Z16","v":1},{"$f":"Inputs!AA16","v":0}]
["DPO CM",{"$f":"Inputs!V17","v":0},{"$f":"Inputs!W17","v":0},{"$f":"Inputs!X17","v":0},{"$f":"Inputs!Y17","v":0},{"$f":"Inputs!Z17","v":1},{"$f":"Inputs!AA17","v":0}]
["DPO Ratio",{"$f":"Inputs!V18","v":0},{"$f":"Inputs!W18","v":0},{"$f":"Inputs!X18","v":1},{"$f":"Inputs!Y18","v":0},{"$f":"Inputs!Z18","v":0},{"$f":"Inputs!AA18","v":0}]
["Total 'Dry Period' Score",{"$f":"Inputs!V19","v":1},{"$f":"Inputs!W19","v":0},{"$f":"Inputs!X19","v":3},{"$f":"Inputs!Y19","v":2},{"$f":"Inputs!Z19","v":2},{"$f":"Inputs!AA19","v":0},{"$f":"MAX(B20:G20)+0.000000001","v":3.0000000010000001},{"$f":"MIN(B20:G20)+0.000000001","v":1.0000000000000001E-9},{"$f":"AVERAGE(B20:G20)+0.000000001","v":1.3333333343333333},{"$f":"(100-(((I20)/H20)*100)+(100-(((J20)/H20)*100)+(100-(((I20)/J20)*100))))/3","v":85.185185142901233}]
["% 'Dry Period' Score",{"$f":"IF(B18=\"NO DATA\",B20/4*100,IF(Inputs!B55=0,B20/4*100,B20/8*100))","v":12.5},{"$f":"IF(C18=\"NO DATA\",C20/4*100,IF(Inputs!C55=0,C20/4*100,C20/8*100))","v":0},{"$f":"IF(D18=\"NO DATA\",D20/4*100,IF(Inputs!D55=0,D20/4*100,D20/8*100))","v":37.5},{"$f":"IF(E18=\"NO DATA\",E20/4*100,IF(Inputs!E55=0,E20/4*100,E20/8*100))","v":25},{"$f":"IF(F18=\"NO DATA\",F20/4*100,IF(Inputs!F55=0,F20/4*100,F20/8*100))","v":25},{"$f":"IF(G18=\"NO DATA\",G20/4*100,IF(Inputs!G55=0,G20/4*100,G20/8*100))","v":0}]
[]
["Lactating  Period Environmental"]
["DP New IMI",{"$f":"Inputs!V22","v":2},{"$f":"Inputs!W22","v":0},{"$f":"Inputs!X22","v":2},{"$f":"Inputs!Y22","v":2},{"$f":"Inputs!Z22","v":2},{"$f":"Inputs!AA22","v":0}]
["Lact New IMI",{"$f":"Inputs!V23","v":1},{"$f":"Inputs!W23","v":0},{"$f":"Inputs!X23","v":0},{"$f":"Inputs!Y23","v":0},{"$f":"Inputs!Z23","v":1},{"$f":"Inputs!AA23","v":0}]
["LPO CM",{"$f":"Inputs!V24","v":0},{"$f":"Inputs!W24","v":0},{"$f":"Inputs!X24","v":0},{"$f":"Inputs!Y24","v":1},{"$f":"Inputs!Z24","v":1},{"$f":"Inputs!AA24","v":1}]
["LPO Ratio",{"$f":"Inputs!V25","v":2},{"$f":"Inputs!W25","v":2},{"$f":"Inputs!X25","v":1},{"$f":"Inputs!Y25","v":2},{"$f":"Inputs!Z25","v":2},{"$f":"Inputs!AA25","v":2}]
["Total 'Lactating Period' Score",{"$f":"Inputs!V27","v":5},{"$f":"Inputs!W27","v":2},{"$f":"Inputs!X27","v":3},{"$f":"Inputs!Y27","v":5},{"$f":"Inputs!Z27","v":6},{"$f":"Inputs!AA27","v":3},{"$f":"MAX(B28:G28)+0.000000001","v":6.0000000010000001},{"$f":"MIN(B28:G28)+0.000000001","v":2.0000000010000001},{"$f":"AVERAGE(B28:G28)+0.000000001","v":4.0000000010000001},{"$f":"(100-(((I28)/H28)*100)+(100-(((J28)/H28)*100)+(100-(((I28)/J28)*100))))/3","v":49.999999990277779}]
["% 'Lactating Period' Score",{"$f":"IF(B26=\"NO DATA\",B28/4*100,IF(Inputs!B55=0,B28/4*100,B28/8*100))","v":62.5},{"$f":"IF(C26=\"NO DATA\",C28/4*100,IF(Inputs!C55=0,C28/4*100,C28/8*100))","v":50},{"$f":"IF(D26=\"NO DATA\",D28/4*100,IF(Inputs!D55=0,D28/4*100,D28/8*100))","v":37.5},{"$f":"IF(E26=\"NO DATA\",E28/4*100,IF(Inputs!E55=0,E28/4*100,E28/8*100))","v":62.5},{"$f":"IF(F26=\"NO DATA\",F28/4*100,IF(Inputs!F55=0,F28/4*100,F28/8*100))","v":75},{"$f":"IF(G26=\"NO DATA\",G28/4*100,IF(Inputs!G55=0,G28/4*100,G28/8*100))","v":75}]
[]
["Heifer Management"]
["DP New IMI",{"$f":"Inputs!V34","v":0},{"$f":"Inputs!W34","v":0},{"$f":"Inputs!X34","v":0},{"$f":"Inputs!Y34","v":0},{"$f":"Inputs!Z34","v":0},{"$f":"Inputs!AA34","v":0}]
["DPO CM",{"$f":"Inputs!V35","v":0},{"$f":"Inputs!W35","v":0},{"$f":"Inputs!X35","v":0},{"$f":"Inputs!Y35","v":0},{"$f":"Inputs!Z35","v":0},{"$f":"Inputs!AA35","v":0}]
["Total 'Heifer' Score",{"$f":"Inputs!V40","v":0},{"$f":"Inputs!W40","v":0},{"$f":"Inputs!X40","v":0},{"$f":"Inputs!Y40","v":0},{"$f":"Inputs!Z40","v":0},{"$f":"Inputs!AA40","v":0}]
["Relative 'Heifer' Score",{"$f":"Inputs!V41","v":0},{"$f":"Inputs!W41","v":0},{"$f":"Inputs!X41","v":0},{"$f":"Inputs!Y41","v":0},{"$f":"Inputs!Z41","v":0},{"$f":"Inputs!AA41","v":0},{"$f":"MAX(B35:G35)+0.000000001","v":1.0000000000000001E-9},{"$f":"MIN(B35:G35)+0.000000001","v":1.0000000000000001E-9},{"$f":"AVERAGE(B35:G35)+0.000000001","v":1.0000000000000001E-9},{"$f":"(100-(((I35)/H35)*100)+(100-(((J35)/H35)*100)+(100-(((I35)/J35)*100))))/3","v":0}]
["% 'Heifer' Score",{"$f":"IF(B35/(B34+0.0000000001)*100<101,B35/(B34+0.0000000001)*100,100)","v":0},{"$f":"IF(C35/(C34+0.0000000001)*100<101,C35/(C34+0.0000000001)*100,100)","v":0},0,0,0,0]
[]
["Recurrence"]
["Recurrence Score",{"$f":"Inputs!V48","v":1},{"$f":"Inputs!W48","v":0},{"$f":"Inputs!X48","v":1},{"$f":"Inputs!Y48","v":0},{"$f":"Inputs!Z48","v":1},{"$f":"Inputs!AA48","v":0}]
["% Recurrence Score",{"$f":"IF(B39=\"NO DATA\",\"NO DATA\",B39/2*100)","v":50},{"$f":"IF(C39=\"NO DATA\",\"NO DATA\",C39/2*100)","v":0},50,0,50,0]

{"sheet":"Diagnosis","index":9,"n_rows":18,"n_cols":13,"rows_kept":18,"cut":false,"hidden":true,"merged":[]}
[]
[null,null,"Current Quarter","Quarter -1","Quarter -2","Quarter -3","Quarter -4","Quarter -5",null,"Current","Recent","Historic","Seasonality"]
[null,"Contagious Issues",{"$f":"Calculations!B13","v":50},{"$f":"Calculations!C13","v":0},{"$f":"Calculations!D13","v":43.75},{"$f":"Calculations!E13","v":31.25},{"$f":"Calculations!F13","v":37.5},{"$f":"Calculations!G13","v":0},null,{"$f":"C3","v":50},{"$f":"MAX(C3:F3)","v":50},{"$f":"MAX(G3:H3)","v":37.5},{"$f":"Calculations!K12","v":81.944444430675745}]
[null,"Dry Period Environmental Issues",{"$f":"Calculations!B21","v":12.5},{"$f":"Calculations!C21","v":0},{"$f":"Calculations!D21","v":37.5},{"$f":"Calculations!E21","v":25},{"$f":"Calculations!F21","v":25},{"$f":"Calculations!G21","v":0},null,{"$f":"C4","v":12.5},{"$f":"MAX(C4:F4)","v":37.5},{"$f":"MAX(G4:H4)","v":25},{"$f":"Calculations!K20","v":85.185185142901233}]
[null,"Lactating Period Environmental Issues",{"$f":"Calculations!B29","v":62.5},{"$f":"Calculations!C29","v":50},{"$f":"Calculations!D29","v":37.5},{"$f":"Calculations!E29","v":62.5},{"$f":"Calculations!F29","v":75},{"$f":"Calculations!G29","v":75},null,{"$f":"C5","v":62.5},{"$f":"MAX(C5:F5)","v":62.5},{"$f":"MAX(G5:H5)","v":75},{"$f":"Calculations!K28","v":49.999999990277779}]
[null,"Specific Heifer Management Issues",{"$f":"Calculations!B36","v":0},{"$f":"Calculations!C36","v":0},{"$f":"Calculations!D36","v":0},{"$f":"Calculations!E36","v":0},{"$f":"Calculations!F36","v":0},{"$f":"Calculations!G36","v":0},null,{"$f":"C6","v":0},{"$f":"MAX(C6:F6)","v":0},{"$f":"MAX(G6:H6)","v":0},{"$f":"Calculations!K35","v":0}]
[null,"Clinical Mastitis Recurrence Issues",{"$f":"Calculations!B40","v":50},{"$f":"Calculations!C40","v":0},{"$f":"Calculations!D40","v":50},{"$f":"Calculations!E40","v":0},{"$f":"Calculations!F40","v":50},{"$f":"Calculations!G40","v":0},null,{"$f":"C7","v":50},{"$f":"IF(AND(C7=\"NO DATA\",D7=\"NO DATA\",E7=\"NO DATA\",F7=\"NO DATA\"),\"NO DATA\",MAX(C7:F7))","v":50},{"$f":"IF(AND(G7=\"NO DATA\",H7=\"NO DATA\"),\"NO DATA\",MAX(G7:H7))","v":50}]
[]
[]
[null,"Notes:"]
[null,"Current = This quarter"]
[null,"Recent = This year"]
[null,"Historic = > 1 year, but < 18 months"]
[]
[]
[null,"error check for e.g. no data or calc error"]
[null,"with warning for Dx"]
[null,"small n warning?","although won't know what n is specifically?"]

{"sheet":"Initial Design","index":10,"n_rows":30,"n_cols":5,"rows_kept":30,"cut":false,"hidden":true,"merged":[]}
["All in the last 1/4","Cows","Heifers","Source (Cows)"]
["BMSCC (,000 cells/ml)",null,null,"Herd Mastitis Summary"]
["% Chronic",null,null,"Herd Mastitis Summary"]
["% >200K",null,null,"Herd Mastitis Summary"]
["Lactation New IMI (%)",null,null,"Herd Mastitis Summary"]
["Dry Period New IMI (%)",null,{"$f":"B28","v":20},"Herd Mastitis Summary"]
["'Apparent' Dry Period Cure (%)",null,null,"Herd Mastitis Summary"]
[]
["Dry Period Origin CM Rate  (in 12)",null,{"$f":"B22","v":1.3333333333333333},"Herd Mastitis Summary"]
["Lactating Period Origin CM Rate  (in 12)",null,null,"Herd Mastitis Summary"]
["Qrt CMIR (/100 cows/year)",null,null,"Herd Mastitis Summary"]
["Cow CMIR (/100 cows/year)",null,null,"Herd Mastitis Summary"]
[]
[]
["Clinical Mastitis Cure Rate",null,null,"12 month rolling 1st clinical case cure rate"]
[]
[null,"Risk","Rate","Risk Proportion","Adjusted Rate"]
["Heifer DP  (Month 1)",12,1,{"$f":"B18/B$21","v":0.33333333333333331},{"$f":"C18*D18","v":0.33333333333333331}]
["Heifer DP  (Month 2)",12,1,{"$f":"B19/B$21","v":0.33333333333333331},{"$f":"C19*D19","v":0.33333333333333331}]
["Heifer DP  (Month 3)",12,2,{"$f":"B20/B$21","v":0.33333333333333331},{"$f":"C20*D20","v":0.66666666666666663}]
[null,{"$f":"SUM(B18:B20)","v":36}]
["Calculated Rate",{"$f":"E18+E19+E20","v":1.3333333333333333}]
[null,"No 1st Reorded","No Infected"]
["Heifer DPIMI (Month 1)",10,1]
["Heifer DPIMI (Month 2)",10,2]
["Heifer DPIMI (Month 3)",10,3]
[null,{"$f":"SUM(B24:B26)","v":30},{"$f":"SUM(C24:C26)","v":6}]
["Heifer DPIMI Rate (Qrt)",{"$f":"C27/B27*100","v":20}]
[]
["X 6 qrts"]

{"sheet":"Glossary","index":11,"n_rows":98,"n_cols":1,"rows_kept":98,"cut":false,"hidden":false,"merged":[]}
["INPUT PARAMETERS"]
["Input parameters - General"]
["There are 17 separate input parameters required for Somatic Cell Count (SCC) and Clinical Mastitis (CM) parameters"]
["Of these, 13 are required to be input by the advisor and four are calculated by the tool itself"]
["These 17 parameters are then input for six 3-month periods, meaning the auto diagnosis tool requires over 100 separate inputs to run effectively."]
["Input parameters - Somatic Cell Count (SCC) data"]
["Note 1: ALL parameters are averaged over all recordings in a three month period and are designed to be used for herds that are engaged in regular (i.e. MONTHLY) milk recording to gain maximum benefit from the output."]
["Note 2: ALL parameters are reported for the previous SIX quarters – i.e. the bulk milk SCC is averaged for the last three recordings (‘Current Quarter’), the three recordings before this (‘Quarter -1’), the three recordings before that (‘Quarter -2’) and so on. In this way, the SCC parameters input cover the previous 18 monthly recordings, allowing for seasonality (if present) to be evaluated. If not monthly recording, the average of the previous three recordings can still be used, but this will inevitably lead to a less ‘robust’ diagnosis."]
[]
["Bulk milk SCC (‘000/ml): The calculated herd average SCC at each milk recording"]
["% of the herd ‘chronic’: The % of cows in the herd that remain above a threshold of 200,000 cells/ml on more than one occasion in the last three recordings"]
["% of the herd >200,000 cells/ml: The % of cows in the herd that are above a threshold of 200,000 cells/ml at the milk recording"]
["Chronic: >200 ratio: The proportion of the cows >200,000 cells/ml that are chronic – calculated by the tool automatically"]
["Lactation New Intra Mammary Infection (IMI) Rate (%): The rate of new infection as measured by an increase in somatic cell count for lactating cows between successive milk recordings"]
["Dry Period New Intra Mammary Infection (IMI) Rate (%): The rate of new infection as measured by an increase in somatic cell count across the dry period (i.e. ‘low’ cell count at drying-off but ‘high’ cell count after calving)"]
["‘Apparent’ Dry Period Cure Rate (%): The rate of cure as measured by a decrease in somatic cell count across the dry period (i.e. ‘high’ cell count at drying-off but ‘low’ cell count after calving)"]
["‘Actual’ Dry Period Cure Rate (%): The sum of the dry period cure rate as measured by somatic cell count across the dry period and the dry period new infection rate"]
["Heifer Dry Period New Intra Mammary Infection (IMI) Rate (%): The rate of new infection as measured by somatic cell count for parity one cows (i.e. ‘high’ cell count at first milk recording)"]
[]
["Input parameters - Clinical Mastitis (CM) data"]
["Note 1: ALL parameters are averaged over THREE months and are designed to be used for herds that report ALL cases of clinical mastitis detected in the parlour (whether treated or not) to gain maximum benefit from the output."]
["Note 2: ALL parameters are reported for the previous SIX quarters – i.e. the cow clinical mastitis incidence rate is averaged for the last three months (‘Current Quarter’), the three months before this (‘Quarter -1’), the three months before that (‘Quarter -2’) and so on. In this way, the CM parameters input cover the previous 18 months, allowing for seasonality (if present) to be evaluated. If herds are not reporting clinical mastitis cases regularly or not reporting cases fully this will inevitably lead to a less ‘robust’ diagnosis."]
[]
["Dry Period Origin CM Rate (in 12): The proportion of cows affected with a new (first or ‘index’) case of clinical mastitis within 30 days of calving, out of every 12 cows that are ‘at-risk’ (i.e. cows that are eligible)"]
["Lactating Period Origin CM Rate (in 12): The proportion of cows affected with a new (first or ‘index’) case of clinical mastitis after 30 days of lactation, out of every 12 cows that are ‘at-risk’ (i.e. cows that are eligible)"]
["Dry Period Origin Ratio: The ratio of new (first or ‘index’) cases that are attributable to the dry period, expressed as a proportion of all new cases – calculated by the tool automatically"]
["Quarter CM Incidence Rate (/100 cows/year): The incidence rate of all quarter cases of clinical mastitis expressed as a incidence rate per 100 cows/year"]
["Cow CM Incidence Rate (/100 cows/year): The incidence rate of all cow cases of clinical mastitis expressed as a incidence rate per 100 cows/year"]
["Clinical Mastitis Cure Rate (1st Case): The proportion of new (first or ‘index’) cases that are cured as measured by non-recurrence of clinical mastitis and a reduction in somatic cell count"]
["Clinical Mastitis Recurrence Factor: The total number of new cases expressed as a proportion of the all clinical mastitis case rate to evaluate the impact of clinical mastitis recurrence – calculated by the tool automatically"]
["Heifer Dry Period Origin CM Rate (in 12): The proportion of parity one cows affected with a new (first or ‘index’) case of clinical mastitis within 30 days of calving, out of every 12 parity one cows that are ‘at-risk’ (i.e. parity one cows that are eligible)"]
[]
["OUTPUT PARAMETERS"]
["Output parameters - General"]
["The auto diagnosis tool presents a series of five outputs, calculated for each of the six 3-month time ‘blocks’ submitted"]
["A traffic-light system is used to present percentage output scores relative to likely importance, using <50% (‘green’, less important), 51-74% (‘amber’, important) and >74% (‘red’, very important)."]
["The five outputs are summarised for the ‘Current’ period, the ‘Recent’ period and the ‘Historic’ period, alongside a fourth output period to describe the relative importance of seasonality."]
[]
["Output parameters - Specific"]
["Contagious Issues: The percentage ‘score’ summarising the relative importance of contagious mastitis on farm, calculated from the numerical scores attributed to appropriate ‘contagious’ parameters (e.g. a poor actual dry period cure rate from the SCC inputs would contribute points to the overall ‘contagious’ score)."]
["A very high score (75% or greater) should alert the advisor to a possible ‘Contagious Lactation/Contagious Dry Period’ herd diagnosis and prioritisation of control measures directed at reducing transmission of infection between cows during milking and optimising cure rate across the dry period should be considered."]
["Appropriate priorities depending on current herd management aimed at reducing transmission of infection between cows in lactation should include:"]
["Segregation of ‘infected’ cows as measured by SCC parameters during the milking process, either physically (e.g. different group) or ‘virtually’ (e.g. identification and disinfection of milking equipment after use on these cows)"]
["Optimisation of the pre-milking routine to include rapid identification of clinical mastitis and the application of pre-milking teat disinfection"]
["Optimisation of post-milking teat disinfection (PMTD) routine to include rapid application of PMTD product and excellent coverage"]
["Consider vaccination to reduce the rate of transmission"]
["Appropriate biosecurity measures, for example screening cows bought onto the unit using SCC, California Mastitis Test, previous whole lactation SCC records etc"]
[]
["Appropriate priorities depending on current herd management aimed at optimising cure of infected cows should include:"]
["The importance of antibiotic dry cow therapy (ADCT) for infected cows using an appropriate SCC threshold – for example, in a high cell count herd, using a low threshold and three consecutive monthly milk recordings to identify cows for treatment"]
["Reviewing treatment of clinical mastitis cases during lactation, particularly rapid identification and treatment of the first clinical case in lactation and the importance of extended intra-mammary therapy to improve the chance of cure"]
["Management of persistent infection (e.g. recurrent clinical mastitis, chronic high SCC cows) by drying-off infected quarters"]
["Culling decisions for persistently infected cows based on SCC and bacteriology data "]
[]
["Dry Period Environment Issues: The percentage ‘score’ summarising the relative importance of the dry period origin environmental mastitis on farm, calculated from the numerical scores attributed to appropriate ‘dry period’ parameters (e.g. an increased dry period new infection rate from the SCC inputs would contribute points to the overall ‘dry period’ score)."]
["A very high score (75% or greater) should alert the advisor to a possible ‘Environmental Dry Period’ herd diagnosis and prioritisation of control measures directed at reducing new infections from the environment to cows during the dry period and at calving."]
["Appropriate priorities depending on current herd management aimed at reducing transmission of infection from the environment during the dry period may include:"]
["The importance of optimising aseptic infusion of both antibiotic and non-antibiotic dry cow therapy to minimise the risk of new intra-mammary infection at the time of drying-off"]
["Consideration of environmental management for dry cow groups at pasture, e.g. spending no more than 2 weeks in any one area to prevent build-up of infection"]
["Consideration of environmental management for dry cow groups in cubicles, e.g. application of new, clean organic bedding daily"]
["Consideration of environmental management for dry cow groups in loose yards, e.g. completely cleaning out loose yards every 4 weeks"]
["The importance of loafing space and ventilation for dry cow groups managed indoors"]
["The importance of slurry management for housed dry cow groups, e.g. scraping passageways and feeding areas daily"]
["The importance of internal teat sealant alone in uninfected cows as measured by SCC"]
[]
["Lactating Period Environment Issues: The percentage ‘score’ summarising the relative importance of the lactating period origin environmental mastitis on farm, calculated from the numerical scores attributed to appropriate ‘lactating period’ parameters (e.g. an increased lactation new infection rate from the SCC inputs would contribute points to the overall ‘lactating period’ score)."]
["A very high score (75% or greater) should alert the advisor to a possible ‘Environmental Lactating Period’ herd diagnosis and prioritisation of control measures directed at reducing new infections from the environment to cows during the lactating period."]
["Appropriate priorities depending on current herd management aimed at reducing transmission of infection from the environment during lactation may include:"]
["Consideration of environmental management for lactating cow groups in cubicles, e.g. application of new, clean inorganic bedding every other day"]
["Consideration of environmental management for lactating cow groups in loose yards, e.g. application of new, clean straw bedding every day or twice daily in the face of an increased challenge"]
["Consideration of environmental management for lactating cow groups at pasture, e.g. spending no more than 2 weeks in any one area to prevent build-up of infection"]
["The importance of loafing space and ventilation for lactating cow groups managed indoors"]
["The importance of pre-milking teat disinfection"]
[]
["Specific Heifer Management Issues: The percentage ‘score’ summarising the relative importance of heifer mastitis on farm, calculated from the numerical scores attributed to appropriate ‘heifer’ parameters (e.g. an increased dry period infection rate from the SCC inputs would contribute points to the overall ‘heifer’ score)."]
["A very high score (75% or greater) should alert the advisor to a possible ‘Environmental Dry Period’ herd diagnosis and prioritisation of control measures directed at reducing new infections from the environment to heifers during the pre- calving period."]
["Appropriate priorities depending on current herd management aimed at reducing transmission of infection from the environment during the pre-calving period may include:"]
["Consideration of environmental management for heifer groups at pasture, e.g. spending no more than 2 weeks in any one area to prevent build-up of infection"]
["Consideration of environmental management for heifer groups in cubicles, e.g. application of new, clean organic bedding daily"]
["Consideration of environmental management for heifer groups in loose yards, e.g. completely cleaning out loose yards every 4 weeks"]
["The importance of loafing space and ventilation for pre-calving heifer groups managed indoors"]
["The importance of fly control for pre-calving heifers"]
[]
["Clinical Mastitis Recurrence Issues: The percentage ‘score’ summarising the relative importance of recurrent mastitis on farm, calculated from the numerical score attributed to the ‘recurrence’ parameter (i.e. an increased clinical mastitis recurrence factor from the CM inputs would contribute points to the overall ‘recurrence’ score)"]
["A very high score (75% or greater) would alert the advisor to both ‘Contagious’ and ‘Environmental’ herd diagnosis as both Gram-positive and Gram-negative pathogens are able to cause persistent intra-mammary infection during lactation. Prioritisation of control measures directed at optimising cure rate during the lactating period for new (first or ‘index’) cases of clinical mastitis are likely to involve management of the first clinical mastitis case in lactation."]
["Appropriate priorities depending on current herd management aimed at optimising cure of new (first or ‘index’) cases of clinical mastitis may include:"]
["Rapid identification of clinical mastitis during milking, e.g. fore-stripping or in-line mastitis detectors"]
["Aseptic collection of clinical mastitis samples, labelling and storage "]
["Submission of 10 clinical mastitis samples two or three times a year to a specialist laboratory to review aetiology"]
["Rapid treatment of new clinical mastitis cases with intra-mammary antibiotic"]
["Review of intra-mammary antibiotic treatment protocol with your veterinary surgeon"]
["Monitoring cure rates for new clinical mastitis cases using clinical and SCC outcomes"]
[]
["‘Current’ Score: The percentage score for the appropriate output parameter for the last three months only. A very high score alerts the advisor to a recent trend and an emergent issue in the output parameter of interest"]
["‘Recent’ Score: The percentage score for the appropriate output parameter for the last 12 months only. A very high score alerts the advisor to a relatively recent trend and a likely issue in the output parameter of interest"]
["‘Historic’ Score: The percentage score for the appropriate output parameter for the period 12-18 months ago only. A very high score alerts the advisor to an historic trend and whilst the issue with the output parameter of interest may have been important, this may not be the case currently."]
["‘Seasonality’ Score: The percentage score across the appropriate output parameters that describe seasonality for each of the four main outputs (contagious, dry period, lactating period and heifer management. For example, a high percentage ‘Seasonality’ score for lactation would indicate large variation in appropriate lactation parameters between each of the six 3-month time periods entered."]
["A very high score (75% or greater) should alert the advisor to an ‘Environmental’ herd diagnosis and prioritisation of control measures directed at reducing new infections from the environment at particular times of the year (e.g. winter housing, dry cow management in the summer etc.)"]

{"sheet":"Further Reading","index":12,"n_rows":7,"n_cols":1,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["FURTHER READING"]
["AHDB Dairy (2012) DairyCo Mastitis Control Plan 3 year report (2008-2012) https://dairy.ahdb.org.uk/resources-library/technical-information/health-welfare/dairyco-mastitis-control-plan-3-year-report-(2008-2012)/"]
["Bradley AJ, Green MJ. (2000) A study of the incidence and significance of intramammary enterobacterial infections acquired during the dry period. J Dairy Sci. Sep 83(9):1957-65."]
["Bradley A, Green M (2005) Use and interpretation of somatic cell count data in dairy cows. In Practice 27:310-315."]
["Bradley AJ, Breen JE and Green MJ (2007) Mastitis pattern analysis – a fresh look at the analysis of bovine mastitis: Part I – somatic cell count data. UK Vet Livestock 2007 12:7, 29-35"]
["Bradley AJ, Breen JE and Green MJ (2008) Mastitis pattern analysis – a fresh look at the analysis of bovine mastitis: Part 2 – clinical mastitis data. UK Vet Livestock 13:1, 30-35"]
["Green MJ, Green LE, Schukken YH, Bradley AJ, Peeler EJ, Barkema HW, de Haas Y, Collis VJ, Medley GF. (2004) Somatic cell count distributions during lactation predict clinical mastitis. J Dairy Sci. May 87(5):1256-64."]