+ Reply to Thread
Results 1 to 4 of 4

Formula to calculate value until one of three conditions are met

  1. #1
    Registered User
    Join Date
    07-15-2014
    Location
    Ireland
    MS-Off Ver
    2013
    Posts
    28

    Formula to calculate value until one of three conditions are met

    Hi,

    I have been tasked with calculating the thermal mass of different constructions. There is an equation to calculate the thermal mass but currently I've to manually enter the values individually. What I would like to do is to create a formula that can be inputted in the relevant cell and it would calculate it for me.


    Thermal Mass is calculated by multiplying the density by the thickness of the material by the specific heat capacity of the material. The material that is located closest to the internal surface is calculated first. The values are added together for each material until one of the following is met:
    * the sum of the thicknesses of the materials has reached 0.1 m
    * you have reached the mid-point of the construction
    * you have reached a layer with conductivity of 0.08 or less

    Below are threes examples of wall make-ups, one with a thickness of 0.478 m, one with a thickness of 0.075 m and one with thickness of 0.075 m.

    Wall Type 1 (External Wall)
    Material Thickness Conductivity Density Specific Heat Capacity
    External Block 0.10 1.33 2000 1
    Cavity 0.50 0.277.... 1.225 0.718
    Insulation 0.100 0.025 30 1.4
    External Block 0.215 1.33 2000 1
    Internal Render 0.013 0.18 600 1


    Wall Type 2 (Fully Internal Wall)
    Material Thickness Conductivity Density Specific Heat Capacity
    Plywood 0.0125 0.13 1600 1
    Cavity 0.050 0.277.... 1.225 0.718
    Plywood 0.0125 0.13 1600 1

    Wall Type 3 (Fully Internal Wall with insulation)
    Material Thickness Conductivity Density Specific Heat Capacity
    Plywood 0.0125 0.13 1600 1
    Insulation 0.050 0.025 30 1.4
    Plywood 0.0125 0.13 1600 1

    Those three constructions would meet the three conditions.

    I have created a crud formula with if statements for each condition, which doesn't always work for each condition but I am having difficultly creating a master equation that could be inputted into any construction to calculate it for me.

    I can post an example excel work book with the attempted equations if required.

    Any help with what could work best here would be helpful.

    Kind regards,
    Stephen

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,746

    Re: Formula to calculate value until one of three conditions are met

    By all means, post a workbook as you suggested with some data and expected results. It's easier to work on

  3. #3
    Registered User
    Join Date
    07-15-2014
    Location
    Ireland
    MS-Off Ver
    2013
    Posts
    28

    Re: Formula to calculate value until one of three conditions are met

    Hi,

    I have attached an excel with what I have done so far.

    Within each construction I have the actual thermal mass (calculated the long way) and the calculated thermal mass (from an equation).
    The equation is getting large and it isn't returning the correct figures when there is a layer of insulation or in some constructions when
    0.1 m has been reached.

    I know if I cleared some time and concrete, I could change the equation with the addition of more if statements to work properly.
    I will work down that road unless anyone can make a suggestion on an easier/better method.

    PS) Normally there wouldn't be any more than 5 or 6 Materials but to be on the safe said, I just added in 10.

    Regards,
    Stephen
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-15-2014
    Location
    Ireland
    MS-Off Ver
    2013
    Posts
    28

    Re: Formula to calculate value until one of three conditions are met

    Hi all,

    I have managed to create a formula that works now and returns the correct values.

    Currently the equation is over 2300 characters long.

    Is there any other method/equation which might achieve the same answer but shorter?

    Thanks
    Attached Files Attached Files

+ 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. [SOLVED] Formula to Calculate Payment Date based on Multiple Conditions
    By NonStopLeo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2018, 01:13 AM
  2. Replies: 1
    Last Post: 11-14-2013, 08:45 AM
  3. Formula required to calculate prices whilst considering other conditions
    By NIVLAG in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-24-2013, 10:45 AM
  4. [SOLVED] Calculate sum with conditions
    By bigband1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-30-2012, 12:53 PM
  5. Calculate Avg Cost per conversion formula with 3 conditions
    By alfykunable in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2012, 05:01 AM
  6. Replies: 5
    Last Post: 08-26-2009, 10:21 AM

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