Validation of Excel Calculation Sheets
- Prepare the excel calculation sheet in “Microsoft Excel”
- Prepare separate calculation sheet for individual tests, which can be common for all products.
- The typical calculation sheet should include name of the organization, name of the test, name of product, batch No, AR No, and wherever applicable, standard preparation (Weight of the standard, dilutions, potency of standard) Average standard area / absorbance, labelled amount, Average weight, conversion factor (if applicable) Sample preparation (Weight of sample, dilutions) and sample area / absorbance. [In case of Related Substances, include retention time, relative retention time, name of the impurity etc]
[ads id="ads1"]
- For dilutions of the standard solution, use prefix “S” and serial numbers.
- For dilutions of the sample solution, use prefix “T” and serial numbers.
- Consider the maximum possible dilutions of the solutions and give provision for it.
- Type the formula correctly in the cell where the result will appear.
- Fill the grey color in the cells that need to be filled while calculating the results.
- Protect the excel sheet except for the cells that need to be filled while calculating the results.
- Protection of excel calculation sheet shall be done by Assistant Manager QA.
[ads id="ads1"]
Excel Calculation Sheet
- Procedure for Protection of excel calculation sheet
- Select the cells that need to be filled while calculating the results. On the Format menu, click Cells, and then click the Protection tab. Clear the Locked checkbox.
- Note: After protection of the calculation sheet, the cells that are unlocked in this procedure are the only cells that can be changed.
- On the Tools menu, point to Protection, and then click Protect Sheet. Type a password, click OK, and then retype the password in the Confirm Password dialog box. Passwords are case sensitive. Type the password exactly as you want to enter it, including uppercase and lowercase letters. The password should be known to Assistant Manager QA and Head QA/QC only.
- Fill the grey color in the cells that need to be filled while calculating the results.
Calculations using excel calculation sheet
[ads id="ads1"]
- Open the excel calculation sheet for respective test.
- Entry shall be done in non-protected (grey colored) cells only.
- No cells shall be kept empty.
- If no value to be entered in the non-protected cells, enter “1”
Validation of excel calculation sheet
- Prepare validation protocol for each excel calculation sheet.
- Perform three sets of calculations using excel calculation sheet and compare the results with same sets of calculations performed using scientific calculator up to predetermined decimal places.
- Prove that entries in protected cells cannot be made.
- Prove that to unprotect the sheet, the password is required.