Iterative formula to calculate minimum foundation depths

1. Iterative formula to calculate minimum foundation depths

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!

2. Re: Iterative formula to calculate minimum foundation depths

I think that you are over complicating this.

The bottom of the foundation must be 1.05m below the finished ground floor level.

( I do not agree with your second scenario. If the EGFL is reduced by 2 metres does that mean you do not need a foundation? )

The existing level is important data but largely irrelevant.

The depth of the bottom of the foundation compared with the existing ground level is

1.05m - ( Existing Level - Proposed Level)

Or if you remove the brackets

1.05m - Existing Level + Proposed Level

Formula:
`Please Login or Register  to view this content.`

3. Re: Iterative formula to calculate minimum foundation depths

Formula in cell G2: = IF((C2-1.05)>=D2,[TRUE Statement],[FALSE Statement]).
Every statement in the [TRUE Statement] is TRUE because D2 - a positive number will always be < D2.

4. Re: Iterative formula to calculate minimum foundation depths

Originally Posted by mehmetcik
I think that you are over complicating this.
I think I'm definitely over complicating things, but I don't think your method takes a few factors into account - though that's probably my fault for not explaining it properly! The foundation needs to be a minimum 1.05m below the finished floor level, but also be a minimum of 0.7m below ground level to avoid soil heave during seasonal changes. I would also consider the existing ground level to be relevant as that's the 'datum' from which the allowable range of depths is calculated (the table on sheet 'SOIL ZONES'). Basically if the external ground level is being raised (usually to 0.15m below finished floor level), you can still achieve the 'min. 1.05m below finished floor level' as long as the foundation is still within the allowable depth range from existing external level.

Originally Posted by josephteh
Formula in cell G2: = IF((C2-1.05)>=D2,[TRUE Statement],[FALSE Statement]).
Every statement in the [TRUE Statement] is TRUE because D2 - a positive number will always be < D2.
That's because I wanted the minimum allowable depth to be used - so if you look at the table on the SOIL ZONES sheet, for the log No. 102, the smallest allowable depth is 0.4m. So the [TRUE Statement] formula checks the first column of the SOIL ZONE table for log No. 102, where the entry is 0. Therefore that statement is not satisfied, and the next column is checked etc. Is there a better way of doing that?

5. Re: Iterative formula to calculate minimum foundation depths

Oh... in that case, use the formula MINIFS('SOIL ZONES'!B9:Q9,'SOIL ZONES'!B9:Q9,">0") to replace the TRUE statement, or the normal function MIN('SOIL ZONES'!B9:Q9) if you don't use zeros for empty cells.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1