Spreadsheet::WriteExcel Formula #DIV/0 Bad message

[mlibeson]

I am using the PERL module Spreadsheet::WriteExcel to create an excel workbook from scratch. I create a worksheet put some data in it. Format everything and I also add a few formulas near the bottom of the sheet after all the data is written to the worksheet.

When I open the workbook, everything looks fine except the formulas. They all display as #DIV/0. Which is supposed to mean I am trying to divide by zero in my formula and that is not the case. In fact, if I select the cell with the formula and click in the edit field, but do not make any changes and just press the enter key, the cell calculates a correct value and the #DIV/0 message disappears.

I looked at the documentation and made sure all the parts of the formula are compatible with the PERL module and they are. I use the following functions:

ROUND
SUMIF
COUNTIF
/

The formula looks like:

=ROUND((SUMIF(A6:A38,L40,M6:M38)/COUNTIF(A6:A38,L40)),1)


Michael Libeson

 

[travs69]

Might post your code as I don't see anything wrong with the formula.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[mlibeson]

Here is a snippet:

foreach my $group (@main::Groups) {
   my $excelrow = $main::row + 1;

   $main::worksheet->write_formula($main::row, 12, "=ROUND((SUMIF(A${main::FirstDataRow}:A${main::LastDataRow},L$excelrow,M${main::FirstDataRow}:M${main::LastDataRow})/COUNTIF(A${main::FirstDataRow}:A${main::LastDataRow},L$excelrow)),1)", $main::currencyBRed);

   $main::row++;
}

Michael Libeson

 

[mlibeson]

easier to read version:

foreach my $group (@main::Groups) {
   my $excelrow = $main::row + 1;

$main::worksheet->write_formula($main::row, 12, "=ROUND(
(
SUMIF(
A${main::FirstDataRow}:A${main::LastDataRow},
L$excelrow,
M${main::FirstDataRow}:M${main::LastDataRow}
)
/
COUNTIF(
A${main::FirstDataRow}:A${main::LastDataRow},L$excelrow)
)
,1)", $main::currencyBRed);

   $main::row++;
}

Michael Libeson

 

[MorganGreylock]

Every time I've ever gotten a division by zero error its because of an unwanted/unescaped slash (/) in my code somewhere. Are you dividing the first part (SUMIF) by the second part (COUNTIF)? If so, then you need to make sure that the denominator is not zero, and if not, what is the purpose of that slash? I'm far from an expert in Excel calculations, so its very possible I'm missing something, but division by zero errors are very concise, you're trying to divide by zero, which requires a division operator. ;-)