Eighth day at SSMC.
Today, Miss Pascale showed me some database of SSMC's in the excel files and I was supposed to improve on the existing files using pivot tables and macros. However, we find that the two functions were not very useful and we were able to use certain formula to replace them for the same purposes.
However, using which formula is the problem I met today. I've tried using 'COUNTIF', 'SUMIF', 'IF', 'AND', 'HLOOKUP', and 'MATCH'. In the end, I solve the "mystery" using the 'COUNTIFS' formula.
Some FAILED formula examples:
=IF(Defect!$D:$D=AOQ!$A9,IF(Defect!$N:$N=AOQ!$B$3,IF(Defect!$O:$O=AOQ!$C$4,COUNTIF(Defect!$D:$D,$A(),0),0),0)
=AND(IF(Defect!D:D=AOQ!A17,1,0),IF(Defect!N:N=AOQ!B3,1,0))
=COUNTIF(Defect!D:D,(AND(IF(Defect!D3:D34=AOQ!A17,Defect!N:N=AOQ!D3,Defect!O:O=AOQ!B4),1,0)))
The Finally successful formula I had used to solve my problem today:
=COUNTIFS($A:$A,$E18,$B:$B,F$12,$C:$C,F$13)
Today, I have also found out the Max and Min values for some points and we were able to combine the areas into one chart. Thus, the total number of charts required has lowered to 111 charts.
Time flies?? Tomorrow is last day of work for this week... Hope that everything goes smoothly... (^^)
No comments:
Post a Comment