Power By 专否社区

PHPExcel - How to round up value?

Go To StackoverFlow.com

2

May I know what is the function to be use in order to round up the column value into 2 decimals point with percentage symbol? E.g: 1.88% instead of 1.88230293

$worksheet_details->setCellValue("D14", "=SUM((F33 / F34))");

How do I round up the value in cell D14?

SOLUTION:

By the way, after I keep continue look for the solution from the Internet and I got this...

$percentageFormat = '#.## \%;[Red]-#.## \%';
$worksheet_details->setCellValue("C14", "=SUM((C33 / C34) * 100)");
$worksheet_details->getStyle('C14')->getNumberFormat()->setFormatCode($percentageFormat);

just change the first # to 0 if you want it display in 0.xx format... or else it will display .xx only

2012-04-04 01:37
by Whatever Kitchen
1.88 is 188%, 0.0188 is 1.88%. Did you make a mistake in your post - Incognito 2012-04-04 01:39

3

If you just want it to be outputted as a string:

echo '%'.number_format($your_number,2);

If you want to retain it as a numerical float value (but compromise on the percentage):

echo round($your_number,2);

If you're looking for an Excel function, use:

$worksheet_details->setCellValue('D14', '=TEXT(F33/F34,"0.00%")');
2012-04-04 01:39
by hohner
Well, instead of echoing it out, store it as a variable - hohner 2012-04-04 01:49
erm sorry Jamie, I think I miss out some of the thing in my question.. try to read and try assist me again - Whatever Kitchen 2012-04-04 01:50
Oh, I see - you want the Excel formula not the PHP way of doing it. I've added the solution to my answer - hohner 2012-04-04 02:01

2

I guess you are looking for ROUND() function:

=ROUND(10/3; 2)

or, in your case:

$worksheet_details->setCellValue("D14", "=ROUND(SUM((F33 / F34)); 2)");
2012-04-04 01:55
by ariefbayu

1

If you mean to round in PHP, with round() you can specify the precision you want a float number:

echo round(1.95583, 2) . "%";  // 1.96%

If you mean to round in Excel, you can use the ROUND function:

$worksheet_details->setCellValue("D14", "=TEXT(SUM((F33 / F34)), '###.##%')");

or

$worksheet_details->setCellValue("D14", "=TEXT(SUM((F33 / F34)/100), '###.##%')");

if the number is a percentage already.

Hope it helps.

2012-04-04 01:42
by elias
erm sorry eljunior, I think I miss out some of the thing in my question.. try to read and try assist me again - Whatever Kitchen 2012-04-04 01:50
I'm sorry, I looked quickly at the php tag and didn't read the question appropriately. I edited my answer but I think Jamie's is better - elias 2012-04-04 02:07

0

If you want to display in percent number format you can try this. 

$percentVal = '95.6';

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );


$percentCell->setValue($percentVal . '%');
$sheet->getStyleByColumnAndRow($percentCellIndex, $dataRow)->getNumberFormat()->setFormatCode('0.0%');

// Output 95.6% and formatted as number in Excel

2013-07-10 08:55
by robert.dejesica
Ads