Issue 11064 - SLOPE and INTERCEPT Problems When Indirect is Used
Summary: SLOPE and INTERCEPT Problems When Indirect is Used
Status: CLOSED DUPLICATE of issue 20494
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.2
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: niklas.nebel
QA Contact: issues@sc
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-01-29 21:27 UTC by proalias
Modified: 2013-08-07 15:15 UTC (History)
1 user (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this issue.
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.