Issue writing formulas with AGGREGATE Function

[Dave Emde]

I am using Excel::Writer::XLSX; to generate a spreadsheet where I need to filter rows. I was using array functions to only count the visible rows but due to slow response I added a helper column in the table. The problem was I would always get #NAME? errors for the formula. 

=(AGGREGATE(3, 5, OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,1))>0)+0

or (as I tried to make it work.)

=(AGGREGATE(3, 5, B29)>0)+0

I could F2 and hit return and the function would be fine.  Fortunately I tried to use a similar function and it worked fine

=(SUBTOTAL(3, B29)>0)+0

I think this might be a bug so I stopped in to see if anyone had encountered similar problems with the AGGREGATE function.

Any help or insight would be appreciated

Thanks,

-Dave

[Dave Emde]

Bug Report Information:

 Perl version   : 5.022001

 OS name        : MSWin32

 Module versions: (not all are required)

                  Spreadsheet::WriteExcel    2.40

                  Parse::RecDescent          1.967013

                  File::Temp                 0.2304

                  OLE::Storage_Lite          0.19

                  IO::Stringy                2.111 

[jmcnamara]

Hi,

Excel 2010 and later added functions which weren’t defined in the original file specification. These functions are referred to by Microsoft as future functions. Examples of these functions are ACOT, CHISQ.DIST.RT , CONFIDENCE.NORM, STDEV.P, STDEV.S and WORKDAY.INTL.

When written using write_formula() these functions need to be fully qualified with a _xlfn. (or other) prefix as they are shown the link below. For example:

    worksheet.write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')

They will appear without the prefix in Excel.

The following is a list of these "Future Functions":

    https://msdn.microsoft.com/en-us/library/dd907480%28v=office.12%29.aspx

AGGREGATE() is one of those so you should add it to your formula as _xlfn.AGGREGATE.

P.S., I thought that this as referenced in the documentation but it isn't. I'll add it.

John

[Dave Emde]

John,

just verified the following works:

 for my $ii ( 28 .. $#RANGE+28 ) {
         $worksheet->write_formula($ii,0,'=(_xlfn.AGGREGATE(3, 5, OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,1))>0)+0',$fD)
 }

Thanks for the quick reply. I"m  very impressed with how much you can do with XLSX writer and the work that went into making the complexities reasonable to work with.

Thanks for the help,

-Dave