I'm a structural engineer and I've created a spreadsheet to calculate foundation depths for a housing development. I've managed to put together a formula that takes into account the different criteria, however said formula takes up the majority of an A4 sheet when I copy it across to Word! Therefore I'm looking for some guidance on how I can refine the formula to something a little more elegant. Unfortunately I can't think of a way to describe the problem in a purely mathematical way aside from that its an iterative calculation. Sorry for the longish post, please stick with me and thanks in advance!
For a given plot within the development, I've been provided with a proposed ground floor level (GFL), an existing external ground level (EEGL), and a proposed external ground level (PEGL). Depending on the area the plot falls within, there will be a range of depths where the soil is sufficient to support a foundation (i.e. between 0.4m-1.2m below EEGL). Typically a foundation will be 1.05m below the ground floor level, so when the external ground levels are not changing, it's nice and straightforward as you can put out a blanket statement saying "All foundations to be 1.05m below GFL". However on this project there are some areas where the external ground level is being raised, and some where it is being lowered.
Where the external ground level is being raised, the foundation level can also be raised - as long as it stays within the allowable soil depth (1). Where the external ground level is being lowered, typically the driving criteria for the foundation depth is (GFL - 1.05m) (2). I've attached an indicative sketch to help visualise the two scenarios.
Please take a look at the attached excel file - I've removed the majority of the plots for clarity. The formula I would like to refine can be found in column 'G', formation level just refers to the underside of foundation level. Let me know if you guys need any more information, and thanks again!