[Spreadsheet-WriteExcel] SUMPRODUCT does not readily produce correct value

[bc]

It is required to perform a dummy update to the cell before Excel
will
compute/display correct result. The problem was oberved with MS Excel
2002 and
2007, although each version had unique reactions. Open Office, on the
other
hand, has no problem displaying the correct result right from the
start,
without intervention.

*****************************Demo***************************************
#! /usr/misc/bin/perl5
#
# Demo program exercising SUMPRODUCT * Functionality
# Observation is that the formula only works
# after dummy update is applied in Excel
# i.e. the formula is selected in Excel
# as if to make an update, but, the Enter key
# is pressed to accept the formula as is.
#
#use strict;
use Spreadsheet::WriteExcel;
my $newfil = "out.xls";
my $sheetname = "DATA";
#
# Data:
#
my @months= ('JAN',
'FEB',
'MAR');
my @types = ('TYPEA',
'TYPEB',
'TYPEC');
my @values= ( 200,
150,
275);
#
# Open workbook and worksheet
#
my $workbook = Spreadsheet::WriteExcel->new($newfil);
my $worksheet = $workbook->add_worksheet($sheetname);
#
# Insert data
#
$worksheet->write_col('A1', \@months);
$worksheet->write_col('B1', \@types);
$worksheet->write_col('C1', \@values);
#
# format for merged cell
#
my $form_text_merged = $workbook->add_format(num_format => '@');
$form_text_merged->set_center_across();
#
# Use SUMPRODUCT formula
#
my $formula =
q{=SUMPRODUCT((LEFT(A1:A5,3)="JAN")*(LEFT(B1:B5,5)="TYPEA"))};
$worksheet->write('B8', "JAN & TYPEA = 1",$form_text_merged);
$worksheet->write_formula('A9',$formula);
#
# Cleanup and exit
#
$workbook->close();
exit;

[jmcnamara]

Hi,

Thanks for your very detailed question.

Unfortunately, it doesn't look like Spreadsheet::WriteExcel parses
this correctly. This is sometimes possible to workaround but I don't
think so in this case.

If possible you may be able to switch to using
Spreadsheet::WriteExcelXML which does support this type of formula and
array formulas as well.

John.
--

[bc]

OK, thanks for looking!

i can stick with WriteExcel for now by using perl to duplicate each
row on a monthly sheet (JAN...DEC), besides the master full-year
sheet. This then simplifies the Excel formula to select cells, since
each month is already filtered on the monthly sheets, so that
SUMPRODUCT isn't necessary.

One more thing...

i have added a few charts now, and i think each generates an error
when opening the workbook via Excel. Am guessing i do not have the
right fonts added for the charts. Is there any way to tell directly
which are missing?

thanks!

[jmcnamara]

On Jun 20, 7:30 pm, bc <clark.b...@comcast.net> wrote:

>
> One more thing...
>
> i have added a few charts now, and i think each generates an error
> when opening the workbook via Excel. Am guessing i do not have the
> right fonts added for the charts. Is there any way to tell directly
> which are missing?

Hi,

There will be a new version of the chartex utility very shortly that
will tell you how many fonts/formats are required.

A later version will generate the format properties as well so you can
paste them directly into your program.

John.
--

[bc]

OK, thanks again!
Will TRY to be patient until then
:)

> > > --- Hide quoted text -
>
> - Show quoted text -