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:

Please Login or Register  to view this content.

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.
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?

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.