# Problems with Excel

23 thoughts
last posted Aug. 26, 2016, 2:18 p.m.

21 earlier thoughts

0

A somewhat opinionated example of what the code for the spreadsheet would look like (modified from Bentley’s original post above):

InputHeights = A2:A4
MeanHeights = mean(InputHeights)
Test_MeanHeights = assert(MeanHeights>1 && MeanHeights<3)

InputWeights = B2:B4
MeanWeights = mean(InputWeights)
Test_MeanWeights = assert(MeanWeights>20 && MeanWeights<150)

fit = lm(log(InputWeights)~log(InputHeights))   ove
aCalc = exp(fit$coef) bCalc = exp(fit$coef)

MyChart = plot(InputHeights,InputWeights,xlab=label_Height,ylab=label_Weight,pch=1,cex=2)

A1 = "Height (cm)", label_Height
A2 = 1.2
A3 = 1.6
A4 = 1.9
A6 = MeanHeights
A7 = Test_MeanHeights
A9 = ""To estimate the parameters |a| and |b| of the {br} relation between weight (|w|) and height (|h|): {br} |w=ah^b| {br} a log-log linear model was fitted to the data: {br} |log(w)=i+slog(h)| {br} so that |a=e^i| and |b=e^s|""

A25 = "a"
A26 = "b"
B25 = aCalc
B26 = bCalc

B1 = "Weight (kg)", label_Weight
B2 = 34
B3 = 56
B4 = 89
B6 = MeanWeights
B7 = Test_MeanWeights

E2 = MyChart


It is more verbose than the example originally given, but we aren’t writing Perl. The software would generate this code behind the scenes and the GUI would eliminate most of the friction involved if done properly.

1 later thought