We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

TotalsCalculation with N/A values

Hi Team,

I'm calculating average of a column using TotalsCalculation. My column can contain either a number or a text "N/A" in its cells. If i do a TotalCalculation.Average i should either get the average of values or "N/A" if no value is present in the column. How do i do that. Please help me in this regard.

Hi,

Thanks for your posting and considering Aspose.Cells.

Please provide us a sample Excel file containing your values (i.e numbers and text) and the average of the values for our reference. You can create it manually and attach with this post.

We will look into it and provide you a sample code letting you know how to achieve your desired Excel file using Aspose.Cells APIs.

Hi shakeel,


Please find the sample attached

Hi Shivam,

Thanks for your posting and considering Aspose.Cells.

Please see the following sample code for your needs. I have attached the source Excel file used in this code and the output Excel file generated by it for your reference.

The average is shown in red color in yellow filled cells as shown in this screenshot.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


for (int c = 0; c <= worksheet.Cells.MaxDataColumn; c++)

{

Cell endCell = worksheet.Cells.EndCellInColumn((short)c);


bool found = false;

double Sum = 0;

int Count = 0;


for (int r = 1; r < endCell.Row; r++)

{

Cell data = worksheet.Cells[r, c];


if (data.StringValue != “N/A”)

{

found = true;

Sum = Sum + (int)data.Value;

Count = Count + 1;

}

}


Cell averageCell = worksheet.Cells[endCell.Row + 1, endCell.Column];


if (found == true)

{

averageCell.PutValue(Sum / Count);

}

else

{

averageCell.PutValue(“N/A”);

}


//Make it bold and red color and fill it with yellow color

Style style = averageCell.GetStyle();

style.Font.Color = Color.Red;

style.Font.IsBold = true;

style.Pattern = BackgroundType.Solid;

style.ForegroundColor = Color.Yellow;

averageCell.SetStyle(style);

}


workbook.Save(“output.xlsx”);