Problem with Excel formula

hi

Do you use version version 2.0 ?

Is it formula for 2nd row ? how will it work for 3,4,5th row?

Thanks for your reply! I tried with version 1.5.5 and 2.0.0. I get the same error in both versions.

If I change the formula to

=ROUND(IF(N4<=76,7%*42;N4-(M4/90*23);N4-(1/3*N4)),2)

I get the same error. Or what do you mean by

how will it work for 3,4,5th row?

For testing purposes the formula now just is for the second row. In the next step I wanted to make it dynamic so that it works in the other rows. I do not know yet how to do that. But I wanted to test the export of the formula for the 2nd row and received the mentioned error.

hi

1. Please, export empty static field.

2. Add following code to section “Misc Settings”.
You should change AAA to necessary column.
thanks, Alex

add_action("woe_xls_print_footer", function($objPHPExcel,$formatter) {
   $sheet = $objPHPExcel->getActiveSheet();
   for($i=2;$i<=$formatter->last_row;$i++)
	 $sheet->setCellValue( "AA$i", "=ROUND(IF(N$i<=0.767*M$i;N$i-(M$i/90*23);N$i-(1/3*N$i));2)");
},10,2);

• This reply was modified 6 years, 4 months ago by algol.plus.

Thanks a lot for developing this code! Unfortunately it gives me again a formula error :/

But it pointed me into the right direction. I now make all calculations in PHP, store them in a variable and insert this variable into the cell. That is fine for me because the Excel table will not change after export. So a static value is OK.

This is the PHP code I am using right now:

add_action("woe_xls_print_footer", function($objPHPExcel,$formatter) {
  $sheet = $objPHPExcel->getActiveSheet();
  for($i=2;$i<=$formatter->last_row;$i++) {
	  	$dauer = $sheet->getCell(&quot;M$i&quot;)->getValue();
	  	$preis = $sheet->getCell(&quot;N$i&quot;)->getValue();
	  	$honorar = 0;
	  	if($preis <= ((76.7 / 100) * $dauer)) {
	  		$honorar = $preis - ($dauer / 90 * 23);
	  	} else {
	  		$honorar = $preis - (1 / 3 * $preis);
	  	}
	  	$honorar = number_format(round($honorar, 2), 2, ',', '');
		$sheet->setCellValue( &quot;Q$i&quot;, &quot;$honorar&quot;);
	}
},10,2);

Thank you very much for your instant help!

you’re welcome.
but WordPress has damaged code a bit. I see ” as & quot;

you can modify your code and add it as calculated field

• This reply was modified 6 years, 4 months ago by algol.plus.
• This reply was modified 6 years, 4 months ago by algol.plus.
• This reply was modified 6 years, 4 months ago by algol.plus.

OK… where would you use ” instead of ” ?

” instead of "

Ah.. I understand now…

I tried both variants. Your code with ” and with & quot;. Both did not work. In my code I really use & quot; which is working fine.

I meant WordPress damaged your code , not mine ??

No, WordPress shows my code exactly like I use it. It works with both the entity code or the actual character.