Quality 101: Tracking Gage Calibration with a Spreadsheet

September 28, 2007
/ Print / Reprints /
ShareMore
/ Text Size+
Shown here is the conditional formatting of cell backgrounds. The operator clicks on Format to select the cell color. Source: Levinson Productivity Systems P.C.


Problems can arise in factories that use 3 by 5 cards to keep track of gages. In fact, calibration problems are one of the top sources of nonconformances during ISO 9000 audits. Commercial software is available for managing a calibration program, but for small shops an Excel spreadsheet may do just as well. Learn how to program a spreadsheet to remind personnel when calibrations are due.

The Spreadsheet

Table 1 shows the layout of a simple spreadsheet for tracking gages and their calibration status. Colored cell backgrounds serve as visual controls that make each gage’s status easily visible to the person who manages calibration control.

The current date in cell B3 is given by the today function in Microsoft Excel. The lead time is entered by the operator, and it is the desired advance warning prior to the due date. In this case, gages may be calibrated up to 30 days before their deadlines. DUE with a yellow background appears when the gage is within 30 days of its calibration deadline.

The gage status is calculated by the following formula in cell D6, which also was copied into the subsequent cells.
    =IF(2*(C6<$B$3)+1*(C6-$D$3<=$B$3)*(C6>=$B$3)=0,“OK”,
    IF(2*(C6<$B$3)+1*(C6-$D$3<=$B$3)*(C6>=$B$3)=1,“DUE”,“OVERDUE”))

  • 2*(C6<$B$3) equals 2 if the due date is less than (i.e., earlier than) today’s date. If this is true, the gage calibration is overdue.

     

  • C6-$D$3<=$B$3 if the due date is within $D$3 days (in this case 30 days) of today’s date. C6 must also be greater than or equal to (later than or equal to) today’s date or else the gage is overdue. If both conditions are met, then 1*(C6-$D$3<=$B$3)*(C6>=$B$3) is equal to 1.


Quality System Considerations

The Excel spreadsheet is easy to program and is usable on any computer with Microsoft Office. Corel QuattroPro can be programmed similarly if the factory uses this software.

It is important to remember, however, that the spreadsheet is a quality record: a fourth-tier document under the control of a third-tier work instruction or second-tier procedure. For example, additional fields (columns) might be added to the spreadsheet to show the gage’s location, or the name of the person performing the calibration. It is vital to remember that a column heading is an implied instruction to enter information. Therefore, the spreadsheet and its controlling document must ask for exactly the same data to comply with ISO 9000’s provisions; conflicting instructions from controlling and subordinate documents constitute a nonconformance.

The same principle applies to how early the calibration may be performed; the controlling work instruction or policy must specify how many days ahead of deadline the gages may be calibrated. If this differs from gage to gage, a separate column must be added to give each gage a unique lead time. The cells in what is currently column D must then be reprogrammed to reference each gage’s lead time instead of a universal lead time. As a quality record, the calibration tracking spreadsheet also is subject to ISO 9000’s record retention provisions. The importance of backing up this file cannot be overemphasized, and the backup file must be kept in a place where it cannot be destroyed by an accident that erases the primary file, such as an offsite computer or in a computer media fire safe.

 

Did you enjoy this article? Click here to subscribe to Quality Magazine. 

Recent Articles by BNP Media Staff

You must login or register in order to post a comment.

Formula not working??

Scott
December 8, 2008
The cause of the problem is due to incompatible font. You can copy and paste the formula but you will need to manually replace the double-quote marks of the 3 desired result statements.

Multimedia

Videos

Podcasts

 In honor of World Quality Month, we spoke to James Rooney, ASQ Past Chairman of the Board of Directors 2013, for his take on quality around the world.
For more information, read the ASQ Speaking of Quality column.
More Podcasts

THE MAGAZINE

Quality Magazine

cover_image

2014 April

Check out the April 2014 edition of Quality Magazine for features!
Table Of Contents Subscribe

Manufacturing Process

Has/does news about a manufacturers’ recall (like the GM recall in the news now) cause you or your company to reexamine its manufacturing process?
View Results Poll Archive

Clear Seas Research

qcast_ClearSeas_logo.gifWith access to over one million professionals and more than 60 industry-specific publications,Clear Seas Research offers relevant insights from those who know your industry best. Let us customize a market research solution that exceeds your marketing goals.

eNewsletters

STAY CONNECTED

facebook_40.png twitter_40px.png  youtube_40px.pnglinkedin_40px.png