如何使用“平均”公式限制Excel单元格中的小数计数?

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:

我正在使用c# Excel互操作来创建电子表格。我正在使用表格底部的“总数”部分的公式。这是我使用的代码:

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:

这就是正在生产的:

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

货币/货币的变化是正确的格式，没有来自我的任何干预- Excel显然是关于钱的聪明。

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

至于整数，虽然不是很多——它显示的是“20192”而不是“20192”——但是我已经问过这个问题了。

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.

我现在的问题是——这是一个“更大的问题”——我如何限制小数计数的平均值?我想要“15.23”而不是“15.23076923”和“34.17”，而不是更长的更精确的值版本。

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

我怎么能告诉前一个公式两个小数点就够了?

1 个解决方案

#1

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

你试过文本公式吗?-语法是这样的:=TEXT(“412.24134”，“#，### #.00”)，答案将显示为412.24:

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

因此，wrt您的代码，类似这样(没有经过测试):

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

#1