getCalculatedValue returns #VALUE!

Topics: Developer Forum, User Forum
Aug 20, 2009 at 7:55 PM

Hope someone can help me with this.  Cell C7 contains 56 1/2

Cell G14 includes a formula like =C7-3.125

 

$sourceSheet->getCell('G14')->getCalculatedValue();

Returns #VALUE!.  rather than calculating the formula since C7 doesn't appear to be a number I suppose. Is there a way to resolve this sort of problem?

 

Thanks,

Developer
Aug 20, 2009 at 8:04 PM

I cannot recreate the problem. Now, are you reading from an existing Excel file, or are you creating one from scratch using PHPExcel. What is your full code?

Aug 20, 2009 at 8:22 PM

Hi,

The spreadsheet was created with PHPExcel but you can create one with Excel too as long as the cell format is not numeric. (In other words it looks like C7 has a non numeric format (text))

<?php

include 'PHPExcel.php';
include 'PHPExcel/IOFactory.php';

#create reader
$objReader=PHPExcel_IOFactory::createReader('Excel5');

#load spreadsheet
$sourceBook = $objReader->load("/home/products/Estimating/Book1.xls");

#define source workbook worksheet
$sourceSheet = $sourceBook->setActiveSheetIndex(0);

echo $sourceSheet->getCell('G14')->getCalculatedValue();
?>

Last line returns #VALUE! even though G14 evaluates to a number in the spreadsheet.  I was hoping for a way around this since it appears all these spreadsheets contain a text format for the cells containing the fractions.

 

Developer
Aug 20, 2009 at 8:56 PM

I can recreate the problem now. Number-like text is a big problem in Excel when there are calculations involved. I have reported your case:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10490

Let's wait for MarkBaker to comment on the issue and tells us whether there is a solution to this.

Aug 20, 2009 at 8:59 PM

Ok, thanks for your help.

 

Aug 21, 2009 at 12:57 PM

koyama,

Is there a cell datatype that will work with a fraction that could be set explicit when writing the spreadsheet?

 

Developer
Aug 21, 2009 at 3:53 PM

>> Is there a cell datatype that will work with a fraction that could be set explicit when writing the spreadsheet?

Correctly entered fractions have numeric data type with a number format for fractions: # ?/?

The problem is with fractions in an Excel workbook that are incorrectly entered as text.

Example how to do it correctly in Excel:

There are some guidelines here if you look at section "Typing a Fraction":

http://support.microsoft.com/kb/214233

The problem is with users changing the cell format to "Text" before typing the fraction in Excel. That way Excel will store the fraction as text which is not good.

Correctly entered fraction looks like this:

http://img149.imageshack.us/img149/7885/ss20090821173309.png

Incorrectly fraction looks like this:

http://img149.imageshack.us/img149/5139/ss20090821173432.png

Coordinator
Aug 21, 2009 at 5:08 PM

I've created a helper method in PHPExcel_Shared_String that returns a boolean identifying whether a string contains a valid fractional value or not, and that actively converts the passed-in value to a numeric if it is

I've also modified the basic tests perfomed against non-matrix operands to call this helper method when used for basic arithmetic functions, so simple mathematical formulae such as ="1 1/2"+2 will now work correctly (valid Excel result of 3.5 rather than OO Calc result of 0 or previous PHPExcel result of #VALUE!). It will take more work to replicate this when applied to matrices or named ranges, or when passing fractional string values to functions.

Aug 21, 2009 at 6:37 PM

Koyama & Mark,

thanks for the help.  Will wait for it to be available!