Excel formula converted to c# not giving the same result

Question

I have a formula in excel

SUM((((25+273))/((40+273))*((688.00*1.001)+101.325))-101.325)-8.46

This formula gives me an answer of 642.36

I translated this formula to the following c# function

public decimal CalculateBaselinePressure(decimal baselineTemperature, 
                                         decimal knownTemperature, 
                                         decimal knownPressure)
{
    return (baselineTemperature + 273) / (knownTemperature + 273) * 
           (((knownPressure * 1.001m) + 101.325m) - 101.325m) -8.46m;
}

This gives me an answer of 647.22378274760383386581469649M

Any idea why the formulas is not giving the same answer.

The `SUM` in the Excel formula does not seem to do anything. — FrankPl, Sep 04 '13 at 20:16
It seems like your parens in the excel one are wacky. If not anything else at least a few are pointless. — ChiefTwoPencils, Sep 04 '13 at 20:19
Don't worry to much about the problems with the excel formula, this is a formula I got from a client and have to translate to C#, it's not my own. — Andre Lombaard, Sep 04 '13 at 20:24

score 3 · Accepted Answer · answered Sep 04 '13 at 20:22

3

You have a difference in your parentheses. This is a simplified but equivalent version of the Excel formula:

(a+273) / (b+273) * (c*1.001 + 101.325) - 101.325 - 8.46

This is what your C# expression breaks down to

(a+273) / (b+273) * ((c*1.001 + 101.325) - 101.325) - 8.46;

You need to remove one set of parentheses after the multiplication. Also you should probably be using double instead of decimal:

public static double CalculateBaselinePressure(
    double baselineTemperature,
    double knownTemperature,
    double knownPressure)
{
    return (baselineTemperature + 273) / (knownTemperature + 273) *
        ((knownPressure * 1.001d) + 101.325d) - 101.325d - 8.46d;
}

answered Sep 04 '13 at 20:22

Magnus Grindal Bakken

2,083
1
16
22

Perfect! Worked 100%. Thank you for the clarification! +1 – Andre Lombaard Sep 04 '13 at 20:27
Decimal will actually be _more_ precise in this case since it will calculate to 28 significant digits. Double can only hold 15 significant digits of precision (but has a larger range). – D Stanley Sep 04 '13 at 20:29
1

The reason I suggested double over decimal is not because of the significant digits it can hold, but because it's faster. If he's passing numbers like 25 and 40 into the formula there's no way he needs anywhere near 15 significant digits. See e.g. http://stackoverflow.com/questions/329613/decimal-vs-double-speed. Chances are he'll never feel the difference anyway, but he might as well use the more appropriate data type. – Magnus Grindal Bakken Sep 04 '13 at 20:31

score 1 · Answer 2 · answered Sep 04 '13 at 20:14

1

Excel uses double precision floating point numbers internally, just use double instead of decimal in your C# code for all variables and constants.

answered Sep 04 '13 at 20:14

FrankPl

13,205
2
14
40

When using double, I get a 'Operator * cannot be applied to operand of type double and decimal' error – Andre Lombaard Sep 04 '13 at 20:23
1

Double vs Decimal would not produce a difference of that magnitude. – D Stanley Sep 04 '13 at 20:24
@user65439 You would have to use the double data type for bot your variables and the constants. For constants, you would remove the suffix `m`. – FrankPl Sep 06 '13 at 09:07

Excel formula converted to c# not giving the same result

2 Answers2