UV analysis on a spreadsheet


An increase in Covid numbers in Scotland (and especially Glasgow) has meant we’ve had a weeks notice to swap our UV labs from face-to-face to online. I’ve rehashed one of my personal-project, HPLC-analysis-on-a-spreadsheet designs (to be posted soon) for the UV analysis of potassium dichromate. I’ve worked with my colleague Drs David Breen and Lina Akil on this one, but I would also like to thank the Advanced Pharmaceutical Manufacturing, Pharmaceutical Analysis and Advanced Drug Delivery 2020-21 classes for their feedback during and after the Zoom sessions which have improved the design of the sheets (especially the inclusion of the option to measure several replicates from one sample).

The spreadsheet is UV_sheet_hidden_protected_psswrd_SIPBSMSc, the instructions are in the next section and the design of the spreadsheet is in the section after that.

User Instructions

  1. The users have to specify the mass of potassium dichromate they should weigh (0.60 +/- 0.01 grams), the volumetric flask size and give the (stock) solution a name.
  2. The users have to specify dilutions from the stock solutions (for up to another 17 solutions) and give those solutions names. They can do serial dilutions for any solution they name.
  3. Every time a pipette and standard flask is selected the actual volume used for the calculations is taken from a table in the worksheet ‘Error(h)’. [(h) means hidden.] There is a different volume (generated at random from the volume and tolerance of the glassware) for every type of glassware and every row in the worksheet.)
  4. Steps 1 to 3 above happen without the spreadsheet showing the user the actual concentrations of the solutions (they need to work those out).
  5. On the UV trace worksheet users can then select solution names, and a UV trace is calculated from the concentration of the analyte in that solution. The UV trace is modelled to be experimentally correct, so has random noise and is non-linear at around 2.3 AU, flattening out at 3AU).
  6. The user can select a replicate number: this mimics the same solution being analysed in the UV spectrometer more than once.
  7. The user can then choose the read the absorbance off the UV trace using a scroll bar at the bottom of the plot.
  8. This spreadsheet could be used to the validation of a UV assay for precision, linearity, range, LOD and LOQ. Accuracy could also be studied of students were given a UV absorbance and concentration value for an ‘accurate sample’.

Can I use this for my classes?

Yes. We have designed this sheet for our SIBPS MSc classes, but since it was largely based in an HPLC/Excel spreadsheet I’d developed in my free time, Dave, Lina and I decided to put this out for free, under Creative Commons BY-CA (see note 1). If you really want to pay something for it the please consider giving a sum of your choice to a charity close to your heart.

The password to unlock the workbook and the worksheets is SIPBSMSc.

Spreadsheet design

There is a video describing the calculations behind the spreadsheet here.

Sheet ‘Solution Prep’:

Note that columns F to R are hidden from the user.

Row 1 contains column headings

A2 to F2 contains information for blank solution

A3 to F3 contains column headings

A4 contains masses (drop down list), volumes (drop down list), solution names (free entry) and concentrations for stock solution prep.

A7 to E7 contains column headings

B8 to E24 contains stock solution names (drop down list), pipette volumes (drop down list), volumetric volumes (drop down list), names and concentration of analytical (non-stock) solutions.

Column F and G contain the actual pipette and standard flask volumes. These are generated by HLOOKUPs linked to the ‘Error Sheet (h)’, where each piece of glassware has an error associated with it and the row in which the glassware appears in. (The row the HLOOKUP formula is in is specified as the row index number in the Error table range.) These columns are hidden.

Column H contains the glassware concentration values. This column is hidden.

Column I contains error values as checks during building the sheet. The column is hidden.

Columns M and N contain dropdown lists for the glassware. Column P contains the dichromate mass (values around 0.6 g).

Column R contains a list of the solution names which acts as a drop down list for the UV trace sheet.

Sheet ‘Error sheet (h)’ – this sheet is hidden from the user.

Contains lists of randomly generated numbers for each type of glassware (pipettes from row 3 to row 25 and standard flasks from row 29 to 50). These are used for the HLOOKUP in the Solution Prep worksheet. Sources and equations are given on the sheet. Note the table is fixed and was made by copy’n’value_paste from a RANDBETWEEN table. The fixed values mean that the table does not change after every Excel calculation (RAND and RANDBETWEEN are volatile Excel functions)

Sheet ‘UV trace’

Contains a plot of the UV trace. The generation of the trace defined by the solution name (and the concentration associated with it, which is hidden from the user). The replicate number provides another variable to the randomiser (in column AC) which means the trace ‘changes’ with different replicates of the same solution. At the bottom of the trace is a cell with values defined by arrow selectors: this gives a reading of the absorbance at the selected wavelength.

The trace is generated by the following columns which are all hidden.

Column AB: A list of wavelengths from 230 to 400 at 1 nm increments

Column AC: Generate baseline noise. The parameters for the baseline noise are in AH1 to AI9. Note that the noise factor needs to be set quite low otherwise it dominates the UV trace. The details of the randomiser are in the HPLC spreadsheet blog (to be posted).

Column AD: this generate a basic trace for dichromate using two ‘NORM.DIST’ function with means, sigmas and scalers set so that the trace looks like a dichromate trace I found on the internet from Hellma here.

Column AE: this takes the basic trace (column AD) multiplied by the concentration of the sample selected, multiplied by 5417.6 (the converts the conc into absorbance allowing for the basic trace intensity). It then adds the scaled down noise trace.

Column AF: produces the non-linear curve. The absorbance value is converted into %T and 0.1% added to the %T value, before being converted back to absorbance. In cell AF2 the %T value added can be increased or decreased as required (see comments in the cell).

AG16 to AH18 relate to the VLOOKUP values for the ‘scroller’ at the bottom of the trace and return the absorbance value from the trace at the wavelength the user selects.

AK1:AK11 is a replicate dropdown list (for the selector cell B3) . The replicate dropdown list also provides a variable to the randomiser.


Note 1: The spreadsheet is shared under Creative Commons Attribution-ShareAlike (CC BY-SA), you may  remix, adapt, and build upon this work for both commercial and non-commercial purposes, but you should credit us (SIPBS_MScs) and license new versions under the identical terms

Categories: Tags: , , , , ,

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s