1

I tried to enter the following code in my excel formula bar directly =INDEX($E$4:$E$132,AGGREGATE(15,6,ROW($1:$30) / ($J$4:$J$132=M4), COUNTIF($M$4:M4, M4))) and works perfectly fine (the left column on the pic below).

pic

But if I'm using my web application to generate an excel report file (PHP, using CodeIgniter and Laravel). It displays an error "'Wrong number of arguments for INDEX() function: 5 given, between 1 and 4 expected'"

Here's my sample code snippet:

$code = "=INDEX(\$E\$4:\$E\$$occurance, AGGREGATE(15,6,ROW(\$1:\$$occurance) / (\$J\$4:\$J\$$occurance=M$top_cell), COUNTIF(\$M\$4:M$top_cell, M$top_cell)))";
$ews2->setCellValue("L$top_cell", $code);

I also have tried to use the setValueExplicit method but causes the excel file to NOT precalculate the code, it reads the code as a string

$ews2->setCellValueExplicit("L$top_cell", $code, DataType::TYPE_STRING);

NOTE TYPE_STRING is provided because if TYPE_FORMULA is also used, the same output mentioned at the top occurs

Here's what it looks like using the setCellValueExplicit

Precoded from PHP May I know the right solution or quick fix for this? Thank you very much in advance!

Suomynona
  • 639
  • 1
  • 5
  • 20
  • Does the version of PhP, codeigniter or Laravel support aggregate() ? if they don't or one of them does not then it would think that index() has too many arguments. – Solar Mike May 07 '19 at 09:09
  • My CodeIgniter app has PHP version 5.6, while my Laravel app has PHP version 7.2 sir, do you think it's with the PHP version? – Suomynona May 07 '19 at 09:12
  • I don't have any of those to check so you need to check, which is why I suggested it. – Solar Mike May 07 '19 at 09:16
  • What do you get when you echo $code? – TimBrownlaw May 07 '19 at 11:12
  • hello @TimBrownlaw, I have other variables there such as the `$occurance` and `$top_cell` that computes on what cell should be the reference of the computation, they just display numbers, the same as the formula at the top – Suomynona May 08 '19 at 00:38
  • Well when you get an error like you have reported, you go back and view the generated string that is causing it and see why. Which is why I asked. – TimBrownlaw May 08 '19 at 08:55
  • I already found the error sir, the PhpSpreadsheet library that I am using to generate the excel file prohibits the usage of complex codes and aggregated functions, so I used another code that does not use any Aggregate function :) – Suomynona May 08 '19 at 09:38

1 Answers1

0

I have found out that the PHPSpreadsheet library for PHP is yet to allow the usage of the AGGREGATE() and complicated formulas/functions, so I had found another way around

By using this excel code

=INDEX(E$2:E$38,IF(M4=M3,MATCH(L3,E$2:E$38,0),0)+MATCH(M4,OFFSET(J$2,IF(M4=M3,MATCH(L3,E$2:E$38,0),0),0,COUNT(J$2:J$38)-IF(M4=M3,MATCH(L3,E$2:E$38,0),0),1),0))

I was able to traverse through my entire range and make sure that no duplicate publication names would appear

This is in relation with the my other question -> Excel - Getting the 2nd or nth matched string from your corresponding data

Suomynona
  • 639
  • 1
  • 5
  • 20