Search code examples
c#exceldecimalexcel-interopdecimal-point

How can I restrict the decimal count in an Excel cell with an "Average" formula?

I'm using C# Excel Interop to create spreadsheets. I'm using formulas for a "totals" section at the bottom of a sheet. Here's the code I'm using for that:

var totalTotalOrdersCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, TOTAL_ORDERS_COLUMN];
totalTotalOrdersCell.Formula = string.Format("=SUM(J10:J{0})", curDelPerfRow);

var avgAvgWeeklyDeliveriesCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, AVG_WEEKLY_DELIVERIES_COLUMN];
avgAvgWeeklyDeliveriesCell.Formula = string.Format("=AVERAGE(K10:K{0})", curDelPerfRow);

var avgAvgOrderAmountCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, AVG_ORDER_AMOUNT_COLUMN];
avgAvgOrderAmountCell.Formula = string.Format("=AVERAGE(L10:L{0})", curDelPerfRow);

var avgAvgPackageCountCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, AVG_PACKAGE_AMOUNT_COLUMN];
avgAvgPackageCountCell.Formula = string.Format("=AVERAGE(M10:M{0})", curDelPerfRow);

var totalTotalSalesCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, TOTAL_SALES_COLUMN];
totalTotalSalesCell.Formula = string.Format("=SUM(N10:N{0})", curDelPerfRow);

var totalTotalPackageCountCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, TOTAL_PACKAGE_COUNT_COLUMN];
totalTotalPackageCountCell.Formula = string.Format("=SUM(O10:O{0})", curDelPerfRow);

This is what is being produced:

enter image description here

The monetary/currency vals are formatting just right, without any intervention from me - Excel is apparently smart about money.

As to the integers, though, not so much - it shows "20192" instead of "20,192" - but I already asked about that particular issue here.

My question now is - and it's a "bigger deal" - how can I restrict the decimal count on the averaged values? I want "15.23" not "15.23076923" and "34.17" not the much longer and more precise version of the value.

How can I tell the Formula former that two decimal points is enough?

Solution

  • Have you tried the =TEXT formula? - syntax is like this: =TEXT("412.24134","#,###.00") and the answer will be displayed as 412.24:

    so wrt your code, something like this (has not been tested):

    var avgAvgWeeklyDeliveriesCell = (Range)_xlSheetDelPerf.Cells[curDelPerfRow + 2, AVG_WEEKLY_DELIVERIES_COLUMN];
avgAvgWeeklyDeliveriesCell.Formula = string.Format("=TEXT(AVERAGE(K10:K{0})", curDelPerfRow),"#,###.00");