Compare multiple parameters of samples to reference values (Excel 2007)

Asbestos_Jen

Asbestos_Jen

Board Regular
Joined
May 12, 2017
Messages
220
Office Version
  1. 2007
Platform
  1. Windows
I’m building a tool for users to check their own performance on QA/QC samples without revealing the true values to them (Tbl.Grp1.entry and Tbl.Grp2.entry). Right now I do this manually for each instance.

I’m getting stuck on how best to compare values when the TYPEs are mismatched, and when TRACE quantities are reported or missed contrary to the reference values.

I am interested in the ways this would be so much easier (generally, or with 365), but it must (unfortunately) work in Excel 2007 in order to be useful to all staff.

I'm open to helper columns as needed, and suggestions for a better layout.

The full reference catalogue is ~350 Samples and grows quarterly. I’ve included all of the relevant types of samples (I think) in the Tbl.Ref.Results.

The reporting order of types is irrelevant, except other TYPEs must be reported before NONE.

Samples may contain 0, 1, 2, or 3 TYPEs. All TYPEs and QTYs must match for 0 points (there are exceptions for TRACE amounts). The passing score for a group of 4 Samples is <100 total points.

In the Code(s) column J, I would like to concatenate the codes from column P where the applicable Smp.Result column is TRUE. If this is out of reach, I’ll just use the scoring table to return important results.

Catalogue - add PF_test.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1User input tablesScoring ExplanationScoring CodePointsSmp.1ResultSmp.2ResultSmp.3ResultSmp.4Result
2Tbl.Grp1.entryCalc'dReported all NONE when NONE present (NDPass)0n/an/aTRUEn/a
3Smp.#Sample IDTYPE 1Qty1TYPE 2Qty2TYPE 3Qty3PointsCode(s)Pass/Fail#TYPEs#TraTYPE1 QTY1 match (T1Q1pass)0TRUEFALSETRUEFALSE
4Smp.15408Banana12Radish17NONE045Pass20TYPE2 QTY2 match (T2Q2pass)0#N/A#N/ATRUETRUE
5Smp.24226Blackberry70Blueberry0NONE020Pass21TYPE3 QTY3 match (T3Q3pass)0TRUETRUETRUE#N/A
6Smp.37618NONE0NONE0NONE00Pass00TYPE 1 present (T1pass)0TRUETRUETRUETRUE
7Smp.48266Blackberry20Cauliflower0NONE070Pass21TYPE 2 present (T2pass)0FALSEFALSETRUETRUE
8Total135 n/aTYPE 3 present (T3pass)0TRUETRUETRUEFALSE
9Incorrect ID of TRACE quantityIDt0
10Tbl.Grp2.entryEXPECTED/ManualReported TRACE TYPE when NONE presentFPt1FALSEFALSEFALSEFALSE
11Smp.#Sample IDTYPE 1Qty1TYPE 2Qty2TYPE 3Qty3PointsCode(s)Pass/Fail#TYPEs#TraPer sample, first TYPE quantitation outside acceptance rangeQTY120FALSETRUEFALSETRUE
12Smp.55408Banana12Radish17NONE055ID2,QTY2Pass20Per sample, second TYPE quantitation outside acceptance rangeQTY210#N/A#N/AFALSEFALSE
13Smp.64226Blackberry70Blueberry0NONE021QTY1,FPtPass21Per sample, third TYPE quantitation outside acceptance rangeQTY35FALSEFALSEFALSE#N/A
14Smp.77618NONE0NONE0NONE00Pass00Reported at least 1 TYPE when NONE present (false positive)FP100FALSEFALSEFALSEFALSE
15Smp.88266Blackberry20Cauliflower0NONE025FNtPass21Reported NONE when at least 1 TYPE present (false negative)FN100FALSEFALSEFALSEFALSE
16Total101FailReported 1 TYPE incorrectly: first instance (ID error)ID50FALSEFALSE--FALSE
17Failed to report 2nd TYPE in a 2-TYPE SampleFN250FALSEFALSEFALSEFALSE
18Tbl.Ref.ResultsFailed to report 3rd TYPE in a 3-TYPE SampleFN325FALSEFALSEFALSETRUE
19Sample IDTYPE 1Lower1Upper1TYPE 2Lower2Upper2TYPE 3Lower3Upper3#TYPEs#TRAReported 2nd TYPE incorrectly in a 2-TYPE SampleID245TRUEFALSEFALSEFALSE
205408Blackberry015Banana325NONE0020Reported 3rd TYPE incorrectly in a 3-TYPE SampleID325FALSEFALSEFALSEFALSE
214226Blackberry7595NONE00NONE0010Failed to report TRACE quantityFNt25FALSEFALSEFALSETRUE
227618NONE00NONE00NONE0000Total points----4520070
238266Blackberry215Cauliflower00Broccoli0032NOTE: report TRACE quantity as 0
248266Blackberry215Cauliflower00Broccoli0032
255403Banana020Blueberry010Blackberry0230TYPE
263935Blackberry010Cauliflower00Broccoli0032Cauliflower
271677Blackberry010NONE00NONE0010Radish
289633Banana030NONE00NONE0010Banana
299721Banana020NONE00NONE0010Blackberry
303385Blackberry1560NONE00NONE0010Blueberry
313877Blackberry020NONE00NONE0010Broccoli
323132Radish655NONE00NONE0010NONE
PassFailCheck
Cell Formulas
RangeFormula
S2S2=IF(COUNTIF($C$20:$K$20,"NONE")<>3,"n/a",COUNTIF($C$4:$H$4,"NONE")=COUNTIF($C$20:$K$20,"NONE"))
T2T2=IF(COUNTIF($C$21:$K$21,"NONE")<>3,"n/a",COUNTIF($C$5:$H$5,"NONE")=COUNTIF($C$21:$K$21,"NONE"))
U2U2=IF(COUNTIF($C$22:$K$22,"NONE")<>3,"n/a",COUNTIF($C$6:$H$6,"NONE")=COUNTIF($C$22:$K$22,"NONE"))
V2V2=IF(COUNTIF($C$23:$K$23,"NONE")<>3,"n/a",COUNTIF($C$7:$H$7,"NONE")=COUNTIF($C$23:$K$23,"NONE"))
S3:V5S3=AND(S6=TRUE,S11=FALSE)
S6S6=COUNTIF($C$20:$K$20,$C$4)>=1
T6T6=COUNTIF($C$21:$K$21,$C$5)>=1
U6U6=COUNTIF($C$22:$K$22,$C$6)>=1
V6V6=COUNTIF($C$23:$K$23,$C$7)>=1
S7S7=COUNTIF($C$20:$K$20,$E$4)>=1
T7T7=COUNTIF($C$21:$K$21,$E$5)>=1
U7U7=COUNTIF($C$22:$K$22,$E$6)>=1
V7V7=COUNTIF($C$23:$K$23,$E$7)>=1
S8S8=COUNTIF($C$20:$K$20,$G$4)>=1
T8T8=COUNTIF($C$21:$K$21,$G$5)>=1
U8U8=COUNTIF($C$22:$K$22,$G$6)>=1
V8V8=COUNTIF($C$23:$K$23,$G$7)>=1
K12:K15,K4:K8K4=IF(AND(I4<>"",I4<100),"Pass","n/a")
L12:L15,L4:L7L4=COUNTIFS(C4:H4,"<>NONE")-COUNT(C4:H4)
M12:M15,M4:M7M4=COUNTIFS(C4,"<>"&"NONE",D4,0)+COUNTIFS(E4,"<>"&"NONE",F4,0)+COUNTIFS(G4,"<>"&"NONE",H4,0)
I4I4=PassFailCheck!$S$22
I5I5=PassFailCheck!$T$22
I6I6=PassFailCheck!$U$22
I7I7=PassFailCheck!$V$22
I8,I16I8=SUM([Points])
J8J8=CONCATENATE(J4,J5,J6,J7)
S10S10=AND(COUNTIF(C20:K20,"none")>COUNTIF(C4:H4,"none"),COUNTIF(C4:H4,0)<=N20)
T10T10=AND(COUNTIF(C21:K21,"none")>COUNTIF(C5:H5,"none"),COUNTIF(C5:H5,0)<=N21)
U10U10=AND(COUNTIF(C22:K22,"none")>COUNTIF(C6:H6,"none"),COUNTIF(C6:H6,0)<=N22)
V10V10=AND(COUNTIF(C23:K23,"none")>COUNTIF(C7:H7,"none"),COUNTIF(C7:H7,0)<=N23)
S11S11=OR(D$4<INDEX($C$20:$K$20,1,MATCH(C$4,$C$20:$K$20,0)+1),D$4>INDEX($C$20:$K$20,1,MATCH(C$4,$C$20:$K$20,0)+2))
T11T11=OR(D$5<INDEX($C$21:$K$21,1,MATCH(C$5,$C$21:$K$21,0)+1),D$5>INDEX($C$21:$K$21,1,MATCH(C$5,$C$21:$K$21,0)+2))
U11U11=OR(D$6<INDEX($C$22:$K$22,1,MATCH(C$6,$C$22:$K$22,0)+1),D$6>INDEX($C$22:$K$22,1,MATCH(C$6,$C$22:$K$22,0)+2))
V11V11=OR(D$7<INDEX($C$23:$K$23,1,MATCH(C$7,$C$23:$K$23,0)+1),D$7>INDEX($C$23:$K$23,1,MATCH(C$7,$C$23:$K$23,0)+2))
S12S12=OR(F$4<INDEX($C$20:$K$20,1,MATCH(E$4,$C$20:$K$20,0)+1),F$4>INDEX($C$20:$K$20,1,MATCH(E$4,$C$20:$K$20,0)+2))
T12T12=OR(F$5<INDEX($C$21:$K$21,1,MATCH(E$5,$C$21:$K$21,0)+1),F$5>INDEX($C$21:$K$21,1,MATCH(E$5,$C$21:$K$21,0)+2))
U12U12=OR(F$6<INDEX($C$22:$K$22,1,MATCH(E$6,$C$22:$K$22,0)+1),F$6>INDEX($C$22:$K$22,1,MATCH(E$6,$C$22:$K$22,0)+2))
V12V12=OR(F$7<INDEX($C$23:$K$23,1,MATCH(E$7,$C$23:$K$23,0)+1),F$7>INDEX($C$23:$K$23,1,MATCH(E$7,$C$23:$K$23,0)+2))
S13S13=OR(H$4<INDEX($C$20:$K$20,1,MATCH(G$4,$C$20:$K$20,0)+1),H$4>INDEX($C$20:$K$20,1,MATCH(G$4,$C$20:$K$20,0)+2))
T13T13=OR(H$5<INDEX($C$21:$K$21,1,MATCH(G$5,$C$21:$K$21,0)+1),H$5>INDEX($C$21:$K$21,1,MATCH(G$5,$C$21:$K$21,0)+2))
U13U13=OR(H$6<INDEX($C$22:$K$22,1,MATCH(G$6,$C$22:$K$22,0)+1),H$6>INDEX($C$22:$K$22,1,MATCH(G$6,$C$22:$K$22,0)+2))
V13V13=OR(H$7<INDEX($C$23:$K$23,1,MATCH(G$7,$C$23:$K$23,0)+1),H$7>INDEX($C$23:$K$23,1,MATCH(G$7,$C$23:$K$23,0)+2))
S14S14=AND(COUNTIF($C$4:$H$4,"NONE")<3,COUNTIF($C$20:$K$20,"NONE")=3)
T14T14=AND(COUNTIF($C$5:$H$5,"NONE")<3,COUNTIF($C$21:$K$21,"NONE")=3)
U14U14=AND(COUNTIF($C$6:$H$6,"NONE")<3,COUNTIF($C$22:$K$22,"NONE")=3)
V14V14=AND(COUNTIF($C$7:$H$7,"NONE")<3,COUNTIF($C$23:$K$23,"NONE")=3)
S15S15=AND(COUNTIF($C$4:$H$4,"NONE")=3,COUNTIF($C$20:$K$20,"NONE")>=1)
T15T15=AND(COUNTIF($C$5:$H$5,"NONE")=3,COUNTIF($C$21:$K$21,"NONE")>=1)
U15U15=AND(COUNTIF($C$6:$H$6,"NONE")=3,COUNTIF($C$22:$K$22,"NONE")<3)
V15V15=AND(COUNTIF($C$7:$H$7,"NONE")=3,COUNTIF($C$23:$K$23,"NONE")>=1)
S16S16=IF(S2=TRUE,"--",COUNTIF($C$20:$I$20,$C$4)<>1)
T16T16=IF(T2=TRUE,"--",COUNTIF($C$21:$I$21,$C$5)<>1)
U16U16=IF(U2=TRUE,"--",COUNTIF($C$22:$I$22,$C$6)<>1)
V16V16=IF(V2=TRUE,"--",COUNTIF($C$23:$I$23,$C$7)<>1)
S17S17=AND(COUNTIF($C$20:$K$20,"NONE")=1,COUNTIF($C$4:$H$4,"NONE")>1)
T17T17=AND(COUNTIF($C$21:$K$21,"NONE")=1,COUNTIF($C$5:$H$5,"NONE")>1)
U17U17=AND(COUNTIF($C$22:$K$22,"NONE")=1,COUNTIF($C$6:$H$6,"NONE")>1)
V17V17=AND(COUNTIF($C$23:$K$23,"NONE")=1,COUNTIF($C$7:$H$7,"NONE")>1)
S18S18=AND(COUNTIF($C$20:$K$20,"NONE")=0,COUNTIF($C$4:$H$4,"NONE")>0)
T18T18=AND(COUNTIF($C$21:$K$21,"NONE")=0,COUNTIF($C$5:$H$5,"NONE")>0)
U18U18=AND(COUNTIF($C$22:$K$22,"NONE")=0,COUNTIF($C$6:$H$6,"NONE")>0)
V18V18=AND(COUNTIF($C$23:$K$23,"NONE")=0,COUNTIF($C$7:$H$7,"NONE")>0)
S19S19=AND(COUNTIF($C$20:$K$20,"NONE")=1,COUNTIF($C$20:$K$20,$C$4)+COUNTIF($C$20:$K$20,$E$4)+COUNTIF($C$20:$K$20,$G$4)=2)
T19T19=AND(COUNTIF($C$21:$K$21,"NONE")=1,COUNTIF($C$21:$K$21,$C$5)+COUNTIF($C$21:$K$21,$E$5)+COUNTIF($C$21:$K$21,$G$5)=2)
U19U19=AND(COUNTIF($C$22:$K$22,"NONE")=1,COUNTIF($C$22:$K$22,$C$6)+COUNTIF($C$22:$K$22,$E$6)+COUNTIF($C$22:$K$22,$G$6)=2)
V19V19=AND(COUNTIF($C$23:$K$23,"NONE")=1,COUNTIF($C$23:$K$23,$C$7)+COUNTIF($C$23:$K$23,$E$7)+COUNTIF($C$23:$K$23,$G$7)=2)
S20S20=AND(COUNTIF($C$20:$K$20,"NONE")=0,COUNTIF($C$20:$K$20,$C$4)+COUNTIF($C$20:$K$20,$E$4)+COUNTIF($C$20:$K$20,$G$4)=3)
T20T20=AND(COUNTIF($C$21:$K$21,"NONE")=0,COUNTIF($C$21:$K$21,$C$5)+COUNTIF($C$21:$K$21,$E$5)+COUNTIF($C$21:$K$21,$G$5)=3)
U20U20=AND(COUNTIF($C$22:$K$22,"NONE")=0,COUNTIF($C$22:$K$22,$C$6)+COUNTIF($C$22:$K$22,$E$6)+COUNTIF($C$22:$K$22,$G$6)=3)
V20V20=AND(COUNTIF($C$23:$K$23,"NONE")<>3,(COUNTIF($C$23:$K$23,$C$7)+COUNTIF($C$23:$K$23,$E$7)+COUNTIF($C$23:$K$23,$G$7)=3))
S21S21=AND(L4<L20,M4<M20)
T21T21=AND(L5<L21,M5<M21)
U21U21=AND(L6<L22,M6<M22)
V21V21=AND(L7<L23,M7<M23)
S22:V22S22=SUMIF(S10:S21,"True",$R$10:$R$21)
I12I12=45+10
I13I13=20+1
K16K16=IF(I16<100,"Pass","Fail")
C20:K32C20=INDEX(TblPAT,MATCH($B20,BlindIDs,0),MATCH(C$19,TblPAT[#Headers],0))
L20:L32L20=COUNTIFS(C20:K20,"<>NONE")-COUNT(C20:K20)
M20:M32M20=COUNTIFS(C20,"<>"&"NONE",E20,0)+COUNTIFS(F20,"<>"&"NONE",H20,0)+COUNTIFS(I20,"<>"&"NONE",K20,0)
B20:B23B20=B4
Named Ranges
NameRefers ToCells
BlindIDs=Catalogue!$B$3:$B$70C20:K32
Cells with Data Validation
CellAllowCriteria
C20:C32List=TYPE
B4:B7List=BlindIDs
C4:C7List=TYPE
D4:D7Whole numberbetween 0 and 100
E4:E7List=TYPE
F4:F7Whole numberbetween 0 and 100
G4:G7List=TYPE
H4:H7Whole numberbetween 0 and 100
B12:B15List=BlindIDs
C12:C15List=TYPE
D12:D15Whole numberbetween 0 and 100
E12:E15List=TYPE
F12:F15Whole numberbetween 0 and 100
G12:G15List=TYPE
H12:H15Whole numberbetween 0 and 100
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
I am open to a VBA solution, if a formula doesn't get the job done.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Sort problem
2
Replies
10
Views
310
Alex Blakenburg
A
youbitto
  • Solved
Index match VBA taking too long
2 3
Replies
27
Views
354
Alex Blakenburg
A
K
  • Question
conditional formatting question
Replies
2
Views
492
kerm007
K
OvernightCellebrity
  • Solved
SUMIF slowing calculation times
2
Replies
12
Views
290
kvsrinivasamurthy
K
E
  • Solved
Formula not working
Replies
2
Views
268
esmarques
E

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top