+ Reply to Thread
Results 1 to 5 of 5

Iterative formula to calculate minimum foundation depths

  1. #1
    Registered User
    Join Date
    07-27-2017
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    2

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Iterative formula to calculate minimum foundation depths

    I think that you are over complicating this.

    As I read it

    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by mehmetcik; 07-27-2017 at 06:59 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,105

    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.
    Please check your formula.

  4. #4
    Registered User
    Join Date
    07-27-2017
    Location
    Manchester, England
    MS-Off Ver
    2016
    Posts
    2

    Re: Iterative formula to calculate minimum foundation depths

    Quote Originally Posted by mehmetcik View Post
    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.

    Quote Originally Posted by josephteh View Post
    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.
    Please check your formula.
    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?
    Last edited by AliGW; 07-28-2017 at 03:50 AM.

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,105

    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.
    Last edited by josephteh; 07-28-2017 at 04:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Checking the minimum of iterative calculations:
    By rajeshrajagopal in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-07-2016, 07:17 AM
  2. Replies: 2
    Last Post: 11-26-2014, 12:48 PM
  3. Replies: 3
    Last Post: 06-21-2013, 10:17 PM
  4. Iterative Formula
    By Phil_V in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-14-2009, 08:54 AM
  5. Calculate formula using a minimum value
    By Gtrhero in forum Excel General
    Replies: 1
    Last Post: 07-30-2008, 11:05 PM

Bookmarks

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