Excel Formula

JohnSwift10

How do I enter the following formula into excel where Po = 1,013.25, P = 900 and T = 15.



h should= 1,010.83

WaywardDriver

=((P0/P)^(1/5.257)-1)*(T+273.15)/0.0065
where P0, P, T are named ranges e.g. enter text P0 in A1, 1013.25 in A2, right-click A2, select Define Name. 

JohnSwift10

Hi, Thanks.
.                                    .

Heedtheadvice

This might be a bit pedantic...or give the result that you have indicated (which is not the exact result of the calculation).....

the above formula gives the correct answer but you quote to 2 decimal places which is slightly different (1010.82676324453 vs 1010.83).

Might matter for you or might not?

If it does matter to be to 2 decimal places:

round to 2 decimal places by enclosing the above formula in the round function :

=ROUND(((PO/P)^(1/5.257)-1)*(T+273.15)/0.0065,2)

WaywardDriver

To continue with being pedantic. If more calculations are to follow, formatting to 2 decimal places but leaving to full (Excel) accuracy makes sense.

Heedtheadvice

Quite! No point in losing accuracy.

Sandtree

Heedtheadvice said:

Quite! No point in losing accuracy.

There can be a point to losing accuracy when dealing with real world and things that are finitely divisible. 

If someone has a £2500.60 monthly income and gets a 1% uplift its fairly difficult to pay them £2525.606 a month. Unfortunately have seen excel spreadsheets where its been formatted to show to 2dp but it ends up in problems when the next few rises occur and its using full precision whereas it should be at 2dp

WaywardDriver

Sandtree said:

Heedtheadvice said:

Quite! No point in losing accuracy.

There can be a point to losing accuracy when dealing with real world and things that are finitely divisible. 

If someone has a £2500.60 monthly income and gets a 1% uplift its fairly difficult to pay them £2525.606 a month. Unfortunately have seen excel spreadsheets where its been formatted to show to 2dp but it ends up in problems when the next few rises occur and its using full precision whereas it should be at 2dp

Quite but don't think formula has anything to do with money.
Suspect T = temperature in ° Celsius and T+273.15 converts from Celsius to Kelvin - I think.

Heedtheadvice

You are right, Sandtree, that there are some cases where a limited number of decimal places are appropriate and those where it is not - and still bring real world things as pointed out by Wayward.

The important point though is that John needs to be aware of the differences between displayed value and the real values held within Excel (and the limitations of both) and then go do the calculation and the presentation of the value appropriate to it's use. John may already be aware and have made appropriate decision for his calculation or have continued the thread and taken the decision.

Assuming Wayward has it correct (and P/Po represent pressure changes?) It would be utter folly to ignore the decimal parts if differences between real world small changes reading to reading were of consideration!

Accuracy should still not be lost but treated properly?

Sandtree

Agree in this scenario its likely maximum accuracy is useful, just highlighting the blanket statement that full precision is always better isn't always true.

WaywardDriver said:
Quite but don't think formula has anything to do with money. 

Absolutely, but money isnt the only thing that has limited divisions... I know 2.3 children is average but in the real world it gets messy if you try to be the average family.

naedanger

I had a physics teacher who would deduct marks if you gave an answer to more significant figures than justified by the input values.