Macros For Data Analysis in Excel

The macro below can be pasted directly into the Visual Basic Editor of Excel - any problems contact me - jbradley@gladstone.ucsf.edu

Trapezoid Method for Integrated Area

This macro was written to determine the approximate area and peak response of  the curve for agonist-induced Ca2+ responses.

Sub aaa_trapezoid()

'written 07-30-02

setpoint = 10 'this point is included in calculating the baseline not the integration

bline = 8  ' number of points in the baseline calculation

undercurve = 8  '  number of points to be used in trapezoid area calculation

output = 30  '  output row on sheet

numbcells = 5  '  number of columns across sheet to be calculated

For across = 1 To numbcells

sumx = 0: sumy = 0: sumprodxy = 0: sumxsquare = 0

a = 0: b = 0: diff = 0: trap_area = 0: base_av = 0

'regression fit for baseline

For up = (setpoint - bline + 1) To setpoint

sumy = sumy + ActiveCell.Cells(up, 1 + across)

sumx = sumx + ActiveCell.Cells(up, 1)

sumprodxy = sumprodxy + (ActiveCell.Cells(up, 1 + across) * ActiveCell.Cells(up, 1))

sumxsquare = sumxsquare + (ActiveCell.Cells(up, 1) * ActiveCell.Cells(up, 1))

Next up

atop = (sumy * sumxsquare) - (sumx * sumprodxy)

abot = (bline * sumxsquare) - (sumx * sumx)

a = atop / abot

btop = (bline * sumprodxy) - (sumx * sumy)

bbot = (bline * sumxsquare) - (sumx * sumx)

b = btop / bbot

base_av = sumy / bline

'    ActiveCell.Cells(39, 1) = sumx

'   ActiveCell.Cells(40, 1) = sumy

'       ActiveCell.Cells(41, 1) = sumprodxy

'       ActiveCell.Cells(41, 3) = sumxsquare

'  ActiveCell.Cells(42, 3) = a

'  ActiveCell.Cells(43, 3) = b

'calculate area under curve by subtracting expected baseline from actual value

max_peak_ratio = 0

For down = setpoint To setpoint + undercurve

'      diff = ActiveCell.Cells(down, across + 1) - base

peakratio = (ActiveCell.Cells(down, across + 1) / base_av)

base = (b * ActiveCell.Cells(down, 1)) + a

base2 = (b * ActiveCell.Cells(down + 1, 1)) + a

leftside = ActiveCell.Cells(down, across + 1) - base

rightside = ActiveCell.Cells(down + 1, across + 1) - base2

trap_area = ((leftside + rightside) / 2) * (ActiveCell.Cells(down + 1, 1) - ActiveCell.Cells(down, 1))

'    diff = ActiveCell.Cells(down, across + 1) - base

If peakratio > max_peak_ratio Then

max_peak_ratio = peakratio

End If

Next down

ActiveCell.Cells(output, across + 1) = base_av

ActiveCell.Cells(output + 1, across + 1) = adder

ActiveCell.Cells(output + 2, across + 1) = max_peak_ratio

Next across

ActiveCell.Cells(output, 1) = "average bseline"

ActiveCell.Cells(output + 1, 1) = "trapezoid area"

ActiveCell.Cells(output + 2, 1) = "peak response"

ActiveCell.Cells(output + 4, 1) = "baseline number ="

ActiveCell.Cells(output + 4, 3) = bline

ActiveCell.Cells(output + 5, 1) = "undercurve number ="

ActiveCell.Cells(output + 5, 3) = undercurve

End Sub

Dissociation Curve Analysis

This macro is used to sort out dissocitation curve data from the real-time pcr machine - following the dissociation curve analysis to check for primer dimers.

Sub dissociation_curve_analysis()

cwt = 0: wells = 0

y = 1

go = True

Dim lots(96) As Integer

Dim wellname(100) As Variant

Dim differential(100, 500) As String

Do Until cwt > 6

a = ActiveCell.Cells(y, 1).Value

sybr2 = ActiveCell.Cells(y + 2, 7).Value

sybr = ActiveCell.Cells(y + 1, 7).Value

timed2 = ActiveCell.Cells(y + 2, 2).Value

timed = ActiveCell.Cells(y + 1, 2).Value

If go = True Then

differential(wells, adder) = ((sybr2 - sybr) / (timed2 - timed)) * -1

End If

If a = "" And go = True Then

End If

If a = "" Then

cwt = cwt + 1

go = False

End If

If a = "Well" Then

cwt = 0

wells = wells + 1

go = True

wellname(wells) = ActiveCell.Cells(y + 1, 1).Value

End If

y = y + 1

Loop

For toot = 1 To 96

ActiveCell.Cells(1, toot).Value = "Well " & wellname(toot)

For hh = 1 To lots(toot)

ActiveCell.Cells(hh + 3, toot).Value = differential(toot, hh)

Next hh

Next toot

End Sub