Issue 11064

Summary:	SLOPE and INTERCEPT Problems When Indirect is Used
Product:	Calc	Reporter:	proalias <ben>
Component:	code	Assignee:	niklas.nebel
Status:	CLOSED DUPLICATE	QA Contact:	issues@sc <issues>
Severity:	Trivial
Priority:	P3	CC:	issues
Version:	OOo 1.0.2
Target Milestone:	---
Hardware:	All
OS:	All
Issue Type:	FEATURE	Latest Confirmation in:	---
Developer Difficulty:	---

Description proalias 2003-01-29 21:27:39 UTC

The following cells from an Excel SpreadSheet will not work (even when cell
references are modified with  quote marks):						

=SLOPE(INDIRECT(G35):INDIRECT(H35),INDIRECT(E35):INDIRECT(F35))	
=INTERCEPT(INDIRECT(G35):INDIRECT(H35),INDIRECT(E35):INDIRECT(F35))		3	

where the referenced cells (G35, etc) are of the form: 
=ADDRESS(B35,2)     =ADDRESS(B36,2)   etc.
This gives 508 and sometimes 502 errors.

The INDIRECT and ADDRESS functions always work properly by themselves.
The INTERCEPT and SLOPE functions work properly with normal cell references
except  that the SLOPE function returns 0 if the second operand set contains all
values between 0.001 and .9999...


It also reads the line incorrectly from the Excel Spreadsheet as:
=SLOPE(INDIRECT(G35);INDIRECT(H35),INDIRECT(E35);INDIRECT(F35))		
and 
=INTERCEPT(INDIRECT(G35);INDIRECT(H35),INDIRECT(E35);INDIRECT(F35))		3	
This gives #NAME! errors in the cells after load.



File Exit (Ctrl-Q) also has no effect in Win 2000.

Comment 1 frank 2003-01-30 09:39:33 UTC

Hi,

please change the commas (the delimiter for parameters) into
semicolons and try it again.

Frank

Comment 2 daniel.rentz 2003-01-30 09:56:04 UTC

Calc cannot handle cell ranges created from function results. It is
only possible to use fixed ranges, i.e. A1:C3, but not function:function.

Comment 3 proalias 2003-01-30 16:19:12 UTC

This problem in conversion of legacy appplications was resolved by using:

=INTERCEPT(INDIRECT("C" & B35 &":C"&C35); INDIRECT("B" & B35
&":B"&C35))       

where B35 and C35 contained offsets rather than addresses. As Daniel
Rentz pointed out, no combination of code snippets work when cell
ranges generated by functions are deployed.

While this workaround functions, it is problematic since it requires
the conversion of a lot of legacy spreadsheet code from the product
made in Renton, WA. 

Chart Titles also require explicit naming (cell reference does not
work on titles). So far I can think of no workaround for that in the
conversion of legacy applicatons.

Comment 4 frank 2003-08-14 13:59:48 UTC

According to the comments from Daniel and the latest one from the
submitter, it should be usefull to react as Excel reacts, therefore I
declare this a Feature request and assign it to Bettina for further
handling.

Frank

Comment 5 bettina.haberer 2003-12-04 19:22:37 UTC

Summary: Calc cannot handle cell ranges created from function results.
It is only possible to use fixed ranges, i.e. A1:C3, but not
function:function.
Hello Niklas, please give approval for this evaluated OO.o 2.0 flagged
issue. 
If you confirm with the target OO.o 2.0, then please keep it on your
owner (or the owner of the concerning developer) for implementation.
In case you want this issue for 'OOo Later', then please reset the
target milestone. If you decline the issue finally, please set the
resolution to 'Wontfix' (but do not close). In case of 'OOo Later' or
'Wontfix' please reset it on Bettina's owner. Thank you.

Comment 6 niklas.nebel 2004-01-26 14:04:12 UTC

This is part of the changes mentioned in issue 20494.

*** This issue has been marked as a duplicate of 20494 ***

Comment 7 niklas.nebel 2004-01-26 14:05:04 UTC

closing duplicate.