Recent Comments
Evaluate Function
It would often be convenient to evaluate a function entered as text; for instance if we have the function for the deflection of a cantilever under point loading at the end:
F*L^3/(3*E*I)
then it would be convenient to be able to allocate different values to F, L, E and I, and calculate the value of the function, without having to re-enter it.
Unfortunately Excel provides no such function. Fortunately it is quite easy to write one in VBA.
Function Eval(form As String, RepA As Variant, ParamA As Variant) Dim Eform As String, i As Long GetArray RepA GetArray ParamA Eform = form For i = 1 To UBound(RepA, 1) - LBound(RepA, 1) + 1 Eform = Replace(Eform, RepA(i, 1), ParamA(i, 1)) Next i Eval = Evaluate(Eform) End Function
Usage of this function is illustrated in this screen shot:
The UDF parameters are:
The cell containing the function to be evaluated
A range containing the function parameters
A range containing the values to be substituted into each corresponding parameter
Note that the GetArray function can be found here: GetArray Function
A working version of this function can also be found in the section properties spreadsheet here: Section Properties Spreadsheet
The idea for this function was borrowed from: Lamda Function
The history of the theory of beam bending – Part 2
Before moving on from Galileo, Mariotte and Parent, let’s examine a peculiarity arising from the testing of their formulas.
Jacques Heyman in “The Science of Structural Engineering” states:
“… then the new calculation of bending strength [by Parent] gave a coefficient or 1/6 instead of the 1/2 of Galileo or 1/3 of Mariotte. For the first time, a logical and correct mathematical description had been given of the way a beam might fracture, but in fact none of the three values of coefficient accorded with tests – 1/2 might be better for stone and 1/3 for wood, while the mathematically correct value of 1/6 seemed to be useless as a predictor of fracture.”
This statement seems to be based on the work of Coulomb, who in 1773 (60 years after Parent’s work) reinvented the theory that had already been discovered by others. Coulomb found that stone and wood behaved in different ways, and presented two theories, based on a common approach, that satisfied the requirements of mechanics. He concluded that Galileo’s coefficient of 1/2 seemed best for stone, but he could not verify his (and Parent’s) coefficient of 1/6 for timber. By the turn of the century the standard text of the Ecole Polytechnique still stuck to Galileo’s formula for stone, and Mariotte’s coefficient of 1/3 for wood (Heyman).
This is curious, because although timber might approximate a plastic material, and thus Mariotte’s coefficient of 1/3 would give a good approximation of the bending strength, stone is a brittle material that will fail in tension while the stress distribution is close to triangular, and thus the coefficient of 1/6 should be appropriate, rather than 1/2.
Unfortunately Heyman gives no details of Coulomb’s experimental work, but the only way I can explain this anomaly is to assume that either Coulomb calculated the tensile strength of stone by back-calculation from bending tests, using Galileo’s formula, or that he carried out tensile tests that gave results much lower than the correct value, perhaps due to stress concentrations in the testing apparatus.
Either way, the end result was that by the early 19th century, over 300 years after Leonardo Da Vinci published the basis of the correct theory of bending, engineering texts still recommended a formula for the bending strength of stone that was incorrect by a factor of 3.
Reference:
The Science of Structural Engineering – Jacques Heyman
Posted in Beam Bending, Newton
Tagged Antoine Parent, Beam Bending, Codex Madrid, Coulomb, Edme Mariotte, Galileo Galilei, history, Leonardo da Vinci
3 Comments
Bach and Newton
Bach’s Music and Newtonian Science:
A Composer in Search of the Foundations of
His Art
CHRISTOPH WOLFFhttp://www.bachnetwork.co.uk/ub2/wolff.pdf
Posted in Bach, Newton
Leave a comment
Difference of Opinion
Found whilst browsing:
about Difference of Opinion
The following articles, reprinted by agreement with IOP Publishing Ltd, formed a regular diary column in Scientific Computing World magazine betweeen September 1997 and February 2000.Difference of Opinion was co-written by Ray Girvan and Felix Grant under the pen names Babbage and Lovelace. The two personae do not represent the two authors; every diary entry was jointly written by us both, as a team. Rather, they represent different aspects of the computing world: loosely, Babbage is interested in hardware, and Lovelace in software and philosophical aspects.
Posted in Computing - general, Newton
Leave a comment
Newton Excel Bach, not (just) an Excel Blog 