• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working out a formula and creating a calculator from known variable data

H

HHBerty

New Member
I am trying to create a simple calculator in Excel from a set of variable data figures I have, but I am stuck on the equation even before I've got to the Excel coding.

Below are the two data entry "calculator" displays I would like to use.

I would enter the Home U-Value and the Floor Area from the 'Carpet Areas' and 'Vinyl Areas' tables below. The required radiator output would be automatically calculated.

Data Entry Calculators

Calculator Displays.PNG

The known data I have is displayed below:

Carpet Areas.PNG

Vinyl Areas.PNG

I have plotted graphs from the data which show there must be a formula linking the U-value (variable), to the room area (variable). I'm thinking it should be possible to work backwards from the completed graph to obtain the formula?

The Vinyl Area values will be slightly higher than those of the carpeted areas, hence the need for a separate calculation.

The radiator outputs are my known results. I would like to be able to enter any U-value ranging between 1.0 to 3.o and any room size, ranging between 1 to 30 metres square. The 'calculator' would then give the radiator output.

Graphs.PNG

The work above is probably reaching the extent of my Excel knowledge.
Can anyone help with the formula please?
Ideally, I would like the two equations (one for carpet, one for vinyl) and to understand the working breakdown of those equations.

Any guidance on how to work the formula from known variable data/graph is also gratefully appreciated.

Many Thanks in advance.
 
Your charts are badly distorted by the fact that they are line charts (area as categories) rather than scatter charts (area as values). Plotted correctly, the data is reasonably linear but a power trendline might be the best option (if one may assume that a zero area room requires a zero rated radiator).

When you insert the trendline check the 'Display Equation on chart' box.
 
Peter Bartholomew said:
View attachment 57879
Click to expand...

Many Thanks Peter.

I have amended my graph as a scatter and added the trendline with the POWER option. I have also inverted the axis, as the radiator output is my unknown values (apart from the examples I have provided in the table).
So my chart now looks like this:

1.PNG

I have simplified the table to show the data source for the above chart.

2.PNG

I tried to test the trendline formula using the data in the table above.

Trendline formula: Y(Radiator Output)= 71.593x to the power of 1.0943

X value: 5.45
Y should be: 430

I have inserted the formula:

3.PNG

The answer I get is 684.89, but should be 430.

I'm not understanding where I'm going wrong. Can anyone wizards advise please?
 
That one is easy. The formula on the chart should be implemented as
= 71.593 * POWER( 5.45, 1.094 )
which evaluates to
457.8

Then you need to bring the U-value in to play. If the exponent in the interpolating formula is similar for each U-value it should all fit together well.
 
Hi Peter. Just a quick note to say thank you, I'm not ignoring you. I've been off for a couple of days and need to catch up on a few things.
There's things not quite right with the data (my end). I will get back as soon as possible.
 
You must log in or register to reply here.
Back
Top