Matching excels rounding in a C# application

Question

I am currently in the process of turning a rather lofty Excel sheet that is used for calculating scientific values into a C# application. However, I am hitting some problems in regards to the rounding.

All of my values are stored as doubles, and when you perform a small number of operations on them they match the excel sheet within acceptable accuracy (5 or 6 decimal places). When they are put through rather large operations with division, multiplication, square roots. They start to drift off by quite a large margin. I switched the entire code base to decimals at another point to test if it resolved this issue, it lessened the gap but the issue still remained.

I am aware this is due to the nature of decimal numbers in software development, but it's imperative I match excels rounding as much as possible. Research on this topic points me towards the standards that excel uses to round and it seems C# by default uses a slightly different one. Despite learning of this I am still unsure of how to proceed on replicating excels rounding. I'm wondering if anyone has any advice or previous experience on this topic?

Any help would be greatly appreciated.

EDIT : I would just like to clarify that I am not rounding my numbers whatsoever. The rounding on both the sheet and my code is implicitly being applied. I have tested the same formulas inside of a totally different software package (A form builder called K2). The resulting numbers match my c# application so it seems excels implicit rounding differs in some way.

One of the offending formulas:

(8.04 * Math.Pow(10, -5)) *
(Math.Pow(preTestTestingDetails.PitotCp, 2)) * (DeltaH) *
(tempDGMAverage + 273.0) / 
(StackTemp + 273) * 
((preTestTestingDetails.BarometricPressure / 0.133322 + 
((preTestTestingDetails.StackStaticPressure / 9.80665) / 13.6)) /
(preTestTestingDetails.BarometricPressure / 0.133322)) * 
(preTestTestingDetails.EstimatedMolWeight / 
((preTestTestingDetails.EstimatedMolWeight * (1 - (EstimatedMoisture / 100))) +
(18 * (EstimatedMoisture / 100)))) *
Math.Pow((1 - (EstimatedMoisture / 100)), 2) * 
(Math.Pow(preTestTestingDetails.NozzleMean, 4));

Answer 1

In C# the result of

int x = 5;
var result = x / 2; // result is 2 and of type int

... because an integer division is performed. So if integers are involved (not a double with no decimals, but a value of type int or long), make sure to convert to double before dividing.

int x = 5;
double result = x / 2; // result is 2.0 because conversion to double is made after division

This works:

int x = 5;
var result = (double)x / 2; // result is 2.5 and of type double

int x = 5;
var result = x / 2.0; // result is 2.5 and of type double

int x = 5;
var result = 0.5 * x; // result is 2.5 and of type double

The only place in your formula where this could happen is EstimatedMoisture / 100, in case EstimatedMoisture is of type int. If this is the case, fix it with EstimatedMoisture / 100.0.

Instead of 8.04 * Math.Pow(10, -5), you can write 8.04e-5. This avoids rounding effects of Math.Pow!

I don't know how Math.Pow(a, b) works, but the general formula is a^b=exp(b*ln(a)). So instead of writing Math.Pow(something, 2), write something * something. This is both, faster and more accurate.

Using constants for magic numbers adds clarity. Using temps for common sub-expressions makes the formula more readable.

const double mmHg_to_kPa = 0.133322;
const double g0 = 9.80665;

var p = preTestTestingDetails;
double moisture = EstimatedMoisture / 100.0;
double dryness = 1.0 - moisture;
double pressure_mmHg = p.BarometricPressure / mmHg_to_kPa;
double nozzleMean2 = p.NozzleMean * p.NozzleMean;
double nozzleMean4 = nozzleMean2 * nozzleMean2;

double result = 8.04E-05 *
    p.PitotCp * p.PitotCp * DeltaH * (tempDGMAverage + 273.0) / (StackTemp + 273.0) *
    ((pressure_mmHg + p.StackStaticPressure / g0 / 13.6) / pressure_mmHg) *
    (p.EstimatedMolWeight / (p.EstimatedMolWeight * dryness + 18.0 * moisture)) *
    dryness * dryness * nozzleMean4;

Why not use 273.15 instead of 273.0 if precision is a concern?