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));

Are you talking about explicit rounding (using a Round() function for example), or implicit rounding (which is just happening without you calling for it)? This question is difficult to address since it's pretty general - we don't know enough about the specifics of your exact situation. — Tim Williams, Oct 15 '19 at 20:37
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. — Keeko, Oct 15 '19 at 20:45
Hi Olivier, I was not aware that doubles are converted to integers if they contain an integer value. As it is variables that are being used within the formulaes it is possible that there could be integer values within. I have posted a formula in the original post — Keeko, Oct 15 '19 at 20:56
doubles are not converted to `int`. But ints are not automatically converted to double when a division of 2 ints is made. — Olivier Jacot-Descombes, Oct 15 '19 at 20:58
Please post the datatypes of all the above variables, a sample input, and what the expected output is. — Robert McKee, Oct 15 '19 at 22:19
I'm afraid you probably can't match it 1-for-1, who knows where and how the calculations Excel performs applies rounding. If your goal is to get close enough calculations, I wouldn't bother trying to replicate it, you're better off running an excel instance via interop to do the actual calculation. — Jeff Mercado, Oct 15 '19 at 22:22

Olivier Jacot-Descombes · Accepted Answer · 2019-10-15T22:13:21.657

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?

Hi Olivier, there are no integer values within my program. I have posted the formula to hopefully give some insight into where the values start to drift :) — Keeko, Oct 15 '19 at 20:59
Thanks for the constructive feedback, I have implemented all the suggested improvements, unfortunately I get exactly the same answer back as before. — Keeko, Oct 15 '19 at 23:01
I'm sorry Oliver, I have just realised that I have made a schoolboy error. Most of the cells don't have formatting applied but one in particular did, it was only a small rounding to the nearest third decimal place but this was enough to cause the rest of my applications numbers to be off. I have selected your answer as correct for the helpful insights. — Keeko, Oct 15 '19 at 23:17
@Olivier I'm not entirely opposed, but modifying the formulas to be more sensible is, I believe, something to be handled automatically, if possible, or postponed as long as possible, otherwise. Invariably it is necessary to redo the whole thing because of some reason or another, and the presence of changes made by hand tends to motivate against staying up to date. — Robert Dodier, Oct 16 '19 at 00:19
@RobertDodier: My suggestions are not so much about modifying the formulas but about about making them more readable and maintainable from the beginning. Also, this time, some changes were worth trying to see what the impact would be on the precision. — Olivier Jacot-Descombes, Oct 16 '19 at 12:18

Matching excels rounding in a C# application

1 Answers1