Pvchabot.
This is no trivial exercise.[ ] Back in the mid 1970s I wrote a Fortran program for the linear elastic analysis of beams on an elastic foundation.[ ] It required a heap of algebra to derive the element stiffness matrix, and a mountain of it to derive the fixed end forces for the various loading types (the worst being that for a partial trapezoidal distributed load).
Years later I put some thought into how I might go about converting this into a spreadsheet, but I eventually blanched at the prospect.[ ] I just could not see how to replicate in a spreadsheet the sort of model flexibility that my Fortran program had, flexibility I was not prepared to abandon.
FWIW, here is my result for the BEF beam's stiffness matrix (K), taken from my working notes of all those years ago.[ ] Degrees of freedom are:[ ] 1 = vertical at LH end,[ ] 2 = rotation at LH end,[ ] 3 = vert at RH end,[ ] 4 = rot at RH end.
Let L be the length of the beam element and k be the elastic foundation constant.
Calculate the traditional "stiffness ratio" = a = [k/(4EI)]^(1/4)
Calculate: S=Sinh(aL), C=Cosh(aL), s=sin(aL), c=cos(aL).
Calculate F = k/[a(S^2-s^2)]
Then the components of the element's stiffness ratio are:
K11 = F * (SC+sc)
K12 = F * (S^2+s^2)/(2a)
K13 = -F * (sC+Sc)
K14 = F * sS/a
K22 = F * (SC-sc)/(2a^2)
K23 = -K14
K24 = F * (sC-Sc)/(2a^2)
K33 = K11
K34 = -K12
K44 = K22
The remaining K components follow from symmetry.
Note that this approach degenerates into "short beam" behaviour as the term k tends to zero.[ ] The other degeneracy occurs as k tends to infinity:[ ] the beam's behaviour at one end becomes uncoupled from its behaviour at the other end, and the results presented in Roark for a "semi[ ]infinite" BEF can be used separately at each end (Table 8 in my fifth edition Roark).
If you do end up using any of this, please note that it comes with no guarantees.[ ] Test, and test again.[ ] A very useful check is to see whether your implementation behaves in a way that is consistent with the two degeneracies mentioned above.
Good luck with it.