question regarding DIV/0! errors use phpexcel.

[njdubois]

This may belong on the phpexcel discussion board, but that site annoys me, so I am hoping someone here can help me out!

 

I've created an excel spreadsheet with 3 col's.

Each row has an Appointments Set, Appointment Process used count, and then a percentage

 

Below all the rows, I have a row for totals.

 

Something like :

      A    B      C

1    1  |  0  |  0.00% (=B1/A1)

2    1  |  1  |  100.00% (=B2/A2)

3    0  |  0  |  0 (=B3/A3)

4    4  |  2  |  50.00% (=B4/A4)

5    -------------------

6    6  |  3  |  50.00% (=B5/A5)

 

A6 = SUM(A1:A4)

B6 = SUM(B1:B4)

 

Columns A and B are normal numbers, A6 and B6 are sum functions, and C is as shown above.  Each C cell's number format is set to a 2 decimal percentage.

 

Inside MS excel, once I open the generated document, C3 should say #DIV/0! but not until I delete row 1(or delete any row) does C3(which is now C2) changes from 0 to the #DIV/0! error.

 

I have the actual cell formatted as a 2 decimal percentage!

($objPHPexcel->getActiveSheet()->getStyle($key.$cur_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00)

 

What I believe is going on is this:

phpexcel knows you can't do a divide by 0, so it just outputs a 0 and that's how it handles that error.

MS Excel knows you can't do a divide by 0, but needs a refresh before it will say #DIV/0!

 

I do not want to see the #DIV/0 error, and I don't want to see just a 0.

I need all cells to have matching ###.##% format.  Even if its 0.00% from a divide by zero.

 

Because I am doing all the math inside excel with functions, I can't, using php, just set the cell to "0/00%"  excel has to want to set that cell for me.

 

How do I do this?  Its killing me!!!!!

 

Any help is as always appreciated!  phpfreaks hasn't done me wrong yet!

Thanks

Nick

 

 

 

[mikosiko]

cant't you just write a formula in that cell ©  to prevent the error message?... something like:

=IF(A3>0, B3/A3, 0)

[njdubois]

Holy $%^#!

 

You are my hero!!!!!!!!!!

[mikosiko]

example from the documentation:

 

 

The following line of code writes the formula ?=IF(C4>500,"profit","loss")? into the cell B8. Note that the formula must start with ?=? to make PHPExcel recognise this as a formula.

$objPHPExcel->getActiveSheet()->setCellValue('B8','=IF(C4>500,"profit","loss")');