Re: Search & Replace Formula in Cell with a Named Array

[Vince B.]

Thank you. 

Regards, 
Vince


Sent from my iPad

> On Jun 5, 2016, at 14:17, Dennis E. Hamilton  wrote:
> 
> 
> 
>> 
> 
> In case this remains a question. >100 does not include 100 and <109 does not 
> include 109.
> 
> So long as the values are integers, >99 and <110 cover 100 to 109 inclusive.
> 
> Just remember that ">" is more-than and "<" is less-than.
> 
> 
> 
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: users-h...@openoffice.apache.org
> 

RE: Search & Replace Formula in Cell with a Named Array

[Dennis E. Hamilton]

> -Original Message-
> From: Vince [mailto:wa.two...@verizon.net]
> Sent: Sunday, June 5, 2016 10:18
> To: users@openoffice.apache.org
> Subject: Re: Search & Replace Formula in Cell with a Named Array
> 
> (I reported in an earlier posting, /Detecting/Counting Cell's Values if
> data is within a Specific Range of Values,/ that the Play File I created
> was working.  I now find that there are erroneous calculations in the
> file that I am now using (a larger array of data) .
> 
> Also, please note that the number of rows of data within the array will
> change each week, as I add additional bowling scores for the week.)
> 
> 
> _
> 
> Here's my concern: I would appreciate confirmation that the formula:
> 
> =SUMPRODUCT($D$343:$L395>100;$D$343:$L395<109)
> will operate on values detected within the decade equal to 100 through
> 109, inclusively,
> 
> and
> 
>   =SUMPRODUCT($D$343:$L395>110;$D$343:$L395<119)
> will operate on values detected within the decade equal to 110 through
> 119, inclusively.
> 
> Regards,
> VinceB.
[orcmid] 

In case this remains a question. >100 does not include 100 and <109 does not 
include 109.

So long as the values are integers, >99 and <110 cover 100 to 109 inclusive.

Just remember that ">" is more-than and "<" is less-than.



-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org

Re: Search & Replace Formula in Cell with a Named Array

[Brian Barker]

At 13:38 04/06/2016 -0400, Vince Bonly wrote:
Using the Find feature in AOO 4.1.2 Calc, is it possible to 
replace a formula, such as:

=SUMPRODUCT($D$343:$L391>100;$D$343:$L391<109)
with:
=SUMPRODUCT(_2016_Scores_Target_Vince>100;_2016_Scores_Target_Vince<109)
where:
_2016_Scores_Target_Vince
is a named array ($D343:$L391) ?


Yes. But you won't need to replace the formula, of course, just the 
ranges it includes.


In the Find & Replace dialogue, click More Options and select 
Formulae in the "Search in" drop-down.


You may wish to tick Tools | Options... | OpenOffice Calc | View | 
Display | Formulae, perhaps temporarily, in order that you can see 
what is going on. In particular, depending on exactly how you perform 
the replace, you may have to take care that multiple references to 
the same range (as you have in your example) are all replaced. But it 
is *not* necessary for formulae to be displayed for the process to work.


Do I need to enclose the formula within quotation marks, " " in the 
Search dialog window?


No.

PS: When you looked up "replacing" in the built-in help text and saw 
the sub-entry "cell content" and the explanation there of "Search in" 
and "Formulae", what went wrong? Oh, or "Finding and replacing 
formulas or values" in Chapter 2 of the Calc Guide?


I trust this helps.

Brian Barker


-
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org