=(AGGREGATE(3, 5, OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,1))>0)+0
=(AGGREGATE(3, 5, B29)>0)+0
=(SUBTOTAL(3, B29)>0)+0
Bug Report Information:
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
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)
}