HPLC analysis on a spreadsheet

Help! Covid means we can’t huddle four or five students round an HPLC system. Any lab-based HPLC experiments we do will take ages! And that’s before we decontaminate each HPLC and computer keyboard between students. Maybe ‘scale up’ I hear you cry! Well at a cool at a £40K per system (and you’d only get that price of you bargained hard with your vendor) it’d be a very expensive lab to kit out for 140 students! And because an HPLC is a real piece of equipment, not a digital entity, so it’s impossible to get one squeezed down the internet and there not scalable.

This is a job for Excel-Man! Move the lab-based HPLC experiment online, using widely available tools (like Excel) and try to add value to the learners experience. The Excel HPLC digital mimic spreadsheet (which can be downloaded from here) allows the users to complete five main activities (listed below), but they can do this in the comfort of their own homes, and make as many mistakes as they want (or need to) before they can nail down there understanding of the sample-to-result pathway.

The Excel spreadsheet allows user to take the following steps:

  1. The users have to specify analyte masses and volumetric flasks for up to 2 stock solutions. They then give the solutions names.
  2. The users have to specify dilutions from those solutions and give those solutions names. They can do serial dilutions (so they can redilute the non-stock solutions) and make combination solutions.
  3. It does steps 1 and 2 above without showing the user the actual concentrations of the solutions (so students need to work the concentration out themselves)
  4. The user can then select solution names, and an HPLC trace is calculated from the concentration of the analyte in that solution. The user can also make replicate injections of the same sample. (The HPLC trace is as realistic as possible and has random noise, random retention time variation, void peaks and and peak tailing.)
  5. The user can then integrate the peak and obtain peak height, areas, retention time, width (for plate number and resolution calculations), front width and rear width at 10% height (for assymmetry calculations).

A 12 minute demonstration video is here. 

Spreadsheet design

This spreadsheet started life sometime in September 2020 as a personal side -project and I’d got as far as modelling one EMG-based peak. However, in October rising Covid cases in Glasgow forced the city onto ‘lockdown-lite’ and the University canceled two of our MSc labs. I quickly re-wrote the HPLC spreadsheet for UV analysis, and together with Drs Lina Akil and David Breen, we generated an activity based upon the analysis of potassium dichromate. That spreadsheet was revised after student comments and can be found here.

I re-designed the HPLC spreadsheet focussing on an HPLC analysis of a sample containing two peaks. I did this (again in my spare time) to integrate into a lab we do on the MSc program, however this version is ‘open access’ and you can make up your own names for compounds X and Y. In the MSc program we run a lidocaine and methyl paraben lab experiment, and we are using this worksheet to allow students to do that experiment in this ‘digital mimic’. We also extend the experiment to include validating the assay of one of the peaks. I have not shared the protocols for those experiments as they really belong to the University of Strathclyde and my colleagues in SIPBS.

I’d developed this spreadsheet in my free time, so it’s here for free, under Creative Commons BY-CA (This means you may remix, adapt, and build upon this work for both commercial and non-commercial purposes, but you should credit ‘SFord’ and license new versions under the identical terms). If you do use it, if you let me know how you got on with it that would be cool. 🙂

Spreadsheet design

Note that the user can only change the values in the white/no-fill cells. All the grey cells are ‘locked’ and so can’t be changed when the worksheets is protected.

In some column I have added colour, and the cell at the top right hand side will contain an explanation of the contents of the coloured cell.

Sheet ‘Solution Prep’:

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

Row 1 contains column headings

A2 to I2 contains information for the blank solution that is required.

Column F and G contains the volume of the pipette and standard flask when considering random error on the glassware. 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. .

Columns H and I contains the glassware concentration values. The two separate columns hold the concentration values for the two separate analytes.

Columns M and N contain  the drop down lists for the pipette and volumetric flask volumes.

Columns P, Q and R contain a list of random masses (centred around 50mg, 75 mg, 100 mg, 150 mg and 200 mg) for the three possible stock solution weights. The masses are all different so students need to recalculate for every stock solution.

Column T contains a list of the solution names which acts as a drop down list for the HPLC sheet.

Sheet ‘Trace creation’ – this sheet is hidden from the user.

Columns A and B creates a noise trace which is unique for every sample but does not change when Excel recalculates a value (unlike the RAND() function which would always be changing). I got the idea of using the MOD function from this website. This function also provides the random values for the retention time variation in cells Q23 and R23.

Cell B2 takes the name the user has given to the solution, B3 takes the name length, B4 the ASCII value of the last letter int he name (which is most likely to change), B5 the row number of the name, B6 a simple equation combining B3, B4 and B5, and B7 a large-ish prime number. B8 is a mutliplication factor that reduces the noise trace to a suitable level.

Columns C and D (coloured blue) contain the equations that generated the first peak. Cells C1 to D7 contains the values needed for the EMG (exponentially modified Gaussian) model. I built the EMG model (in cells D11 to D511) from equations (1) and (3) cited in this reference. The Excel ERF function generates the values for the integral (I) given by equation 3. The tau value (D7) gives the peak tailing. The sigma value (D6) the peak width. The mean value (D5) is the peak ‘moment’ (I think?) – note that this not the peak maximum and so not the traditional retention time. Cell D2 VLOOKs up the concentration from the Solution Prep sheet. Cell D3 is a multiplication factor that allows the intensity of the peak to be altered. Cell D4 multiples the concentration and multiplication factor to give the Area used by the EMG equation. C10:C511 contain time value from 0 to 10 minutes in 0.02 increments), D10:D511 contain the peak absorbance values as defined by the EMG equation, the values in D4 to D7 and the time values in C10:C511.

Columns E and F (coloured brown) contain the equations that generated the second peak and see the details on colmns C and D above for more information.

Columns G and H generated the void peak by using the second derivative of a Gaussian. In the spreadsheet I have set height and width to ‘look real’, with the mean is set by 1.5 ml /flow rate (1.5 ml being the void volume of a 4.6 mm id 15 cm HPLC column), but it can be changed to whatever is needed. It should be noted that the void peak time is calculated in cell P22.

Column J is a total of the noise and two analyte peak columns.

Column K produces the non-linear absorbance responce curve from the data in column J. The absorbance value is converted into %T and 0.1% added to the %T value, before being converted back to absorbance. In cell K2 the %T value added can be increased or decreased as required (see comments in the cell).

Cells O17 to V25 contain the information needed to alter the peak retention times according to the mobile phase composition. These cells are coloured blue and desciptions of the date within more of the cells has been added in the comments section

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 HPLC

This sheet shows the HPLC trace and allows the student to see the trace, integrate the peaks and then extract relevant data.

In cell B2 the user selects the solution to be tested from a dropdown list. The dropdown list value come from column T in the ‘Solution Prep’ worksheet. In cell B3 the user selects a replicate. This is the same solution as specified in cell B2, but a the randomiser changes the background noise. This allows students to obtain replicate data from the same sample solution.

Cells F2 and F3 allows users to alter the mobile phase composition and flow rate to obtain a suitable HPLC trace.

The spin buttons below the trace control the postion of the integration start and stop, and the peak values consequently generated are show to the right. 

Columns N to V are hidden from the user. Descriptions of the functions of the cells are given in comments boxes.

Columns N and O contain the HPLC trace data from the ‘Trace Creation’ worksheet. 

Columns P and O filters the HPLC trace data so that absorbance values are only generated when the HPLC trace is between the start and stop integration values. This allows the peak area to be calculated. Note that the right hand time time column is there to allow the peak max (ret time) to be reported

Cells T1:T10 contain the replicate dropdown list values.

Column V gives a list of the absorbance values between the integral start and the peak max (these are ascending values and are used to work out half width and width at 10% height).

Column W gives a list of the absorbance values between the peak max and the integral end e(these are decending values and are used to work out half width and width at 10% height).

R17:T33 contain calculations needed to calculate the peak height, peak area, and width values. (The latter is used for resolution, asymmetry and plate number/height calculations.)

Categories: Tags: ,

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