Formula to calculate value until one of three conditions are met

[deweyirl]

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

[Pepe Le Mokko]

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

[deweyirl]

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

[deweyirl]

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