Issue 37351 - count function in XLS imported as #name?
Summary: count function in XLS imported as #name?
Status: CLOSED DUPLICATE of issue 4904
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: 680m59
Hardware: PC All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2004-11-17 16:44 UTC by micrond
Modified: 2004-11-18 10:30 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
formula in error included in a real-life example (103.00 KB, application/vnd.ms-excel)
2004-11-17 16:45 UTC, micrond 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description micrond 2004-11-17 16:44:49 UTC 
when importing an Excel spreadsheet containing formula
"=IF(COUNT(C9:E9:G9:I9:K9:M9:O9)=0;"";((C9+E9+G9+I9+K9+M9+O9)/COUNT(C9:E9:G9:I9:K9:M9:O9)))"
in OpenOffice version 680m59 and 1.1, the formula is not imported properly. The
problem seems to be in the enumeration C9:E9:I9:... that is shown as
"#name?(#name?(#name?(..."
Comment 1 micrond 2004-11-17 16:45:49 UTC 
Created attachment 19437 [details]
formula in error included in a real-life example
Comment 2 micrond 2004-11-17 16:47:14 UTC 
Afteword, the issue might be more with the interpretation of the range than with
the count function itself.
Comment 3 daniel.rentz 2004-11-18 10:06:44 UTC 
New behaviour in OOo 2.0 (starting from 680m62): Range operators are imported 
correctly, and re-export to Excel works too, no more #NAME? errors. Nevertheless 
Calc cannot handle them in calculations.

Btw: What is the purpose of this weird construct? A simple  COUNT(C9:O9)  does 
exactly the same, and works in Calc and Excel. Or do you want to count only the 
mentioned cells? This is done correctly by  COUNT(C9;E9;G9;I9;K9;M9;O9)  --  
semicolons instead of colons.

*** This issue has been marked as a duplicate of 4904 ***
Comment 4 daniel.rentz 2004-11-18 10:23:31 UTC 
Just another hint  --  if you are interested in a neat way to simplify your formulas, 
you may do the following steps (in Excel):
- go to cell Q9
- Insert->Names->Define
- give a name, i.e. "ranges", enter the formula
    =C9,E9,G9,I9,K9,M9,O9     (*)(**)
- in cell Q9, enter the formula
    =IF(COUNT(ranges)=0,"",SUM(ranges)/COUNT(ranges))     (*)
- copy this cell to all other cells

(*) if your Excel uses commas as separators (some locales use semicolons); Calc 
always uses semicolons
(**) ensure that no dollar signs are in the formulas, to make the copy&paste work

The name "ranges" contains a reference to 7 cells to the left of the current position 
(relative references), therefore copy&paste of the formula works as expected.

This issue: double->closed
Comment 5 daniel.rentz 2004-11-18 10:30:40 UTC 
Sorry to disturb again, I see another interoperability issue with your document:

In cell C14, change  =IF(C10>=1;"1";)  to  =IF(C10>=1;1;)  --  the 1 must be a 
number, otherwise the summation in Q14 will not work in Calc. Copy C14 to all 
other cells using this formula. After that, you can change Q14 to  =SUM(ranges)

Note that Excel needs a separate defined name for every sheet in your document, 
because it adds the sheet name to the cell addresses.