#VALUE in excel calculation

Aspose.Cells Product Family
1

We’re creating excel, its working for all the users who are in India, but having some concerns with Europe people, I’ve attetced the screenshot and the file as well. On cells, it shows this error.

Aspose.Cell_Issue.png (123.4 KB)

After downloading, while editing this file will work with all the formulas, this error comes while downloading from the backend.

Report.zip (9.3 KB)

I tried to fix this issue with the help of changing the style to number specific.

Please help!

2

@ashish.dadhich,

Thanks for the screenshot and sample file.

I checked your file and found some values (in the cells) for those formulas are stored as string/text and not as numeric values. This might cause to “#VALUE!” error as the values are of wrong type. When you double click in those formula cells, MS Excel will evaluate to correct data type and re-update the formulas/results. Please make sure you are inserting value into those cells with correct type. See the following lines of code with comments for your reference:
e.g.
Sample code:

cell.PutValue("0.126");//this will insert value as string/text which is wrong.
cell.PutValue(0.126);//this will insert value as numeric data type which is ok.

Hope, this helps a bit.

3

Thanks for responding @Amjad_Sahi

Here is my below code, if you can see this I tried this way.

    private static void populateUserPrefereceData(Worksheet worksheet, Result result)
{
	cellConfig(worksheet, "A3", "Year", CellOperation.VALUE, CellConstant.BOLD_CELL);
	cellConfig(worksheet, "B3", result.Year, CellOperation.VALUE);
	Cell yearCell = worksheet.Cells["B3"];
	Style yearStyle = yearCell.GetStyle(); //Changing the style as number 
	yearStyle.Number = 1;
	yearCell.SetStyle(yearStyle);

	cellConfig(worksheet, "C3", "years", CellOperation.VALUE);

	cellConfig(worksheet, "A4", "Cost / kwh", CellOperation.VALUE, CellConstant.BOLD_CELL);
   
	cellConfig(worksheet, "B4", result.CostKwh, CellOperation.VALUE);

	Cell costKwhCell = worksheet.Cells["B4"];
	Style costKwhStyle = costKwhCell.GetStyle();
	costKwhStyle.Number = 2; //Changing the style as number floating point
	costKwhCell.SetStyle(costKwhStyle);

	cellConfig(worksheet, "C4", Constant.DEFAULT_CURRENCY, CellOperation.VALUE);
}

public static void cellConfig(Worksheet worksheet, string cellReference, string cellValue, CellOperation operation, params string[] additionalProps)
{
	Cell cell = worksheet.Cells[cellReference];
	if (operation == CellOperation.VALUE)
	{
		cell.PutValue(cellValue);
	}
	else if(operation == CellOperation.FORMULA)
	{
		cell.Formula = cellValue;
	}

	applyBorder(cell);

	foreach (string props in additionalProps) 
	{
		if (props.Equals(CellConstant.BLANK_BLACK_CELL))
		{
			blackCell(cell);
		}

		if (props.Equals(CellConstant.BOLD_CELL))
		{
			boldContent(cell);
		}
	}
}

I took the reference of this List of Supported Number Formats|Documentation documentation.

4

@ashish.dadhich,

Your provided sample code segment might not help much to evaluate/trace your issue. Please create a separate standalone VS.NET console demo application (source code with compilation errors), zip the project and post us to reproduce the issue on our end, we will check it soon. Also, zip and provide your resource files in any (input Excel file and output Excel file, etc.).