Re: Detecting/Counting Cell's Values if data is within a Specific Range of Values

[Vince B.]

I thought attachments are not permitted for this mail list? 

Would an embedded link to my Drop Box work here?  (I am new to Drop Box.) I'll 
place the dummy array in my DB, to start.

Regards, 
Vince


Sent from my iPad

> On Jun 1, 2016, at 00:22, James Plante  wrote:
> 
> One way would be to export it to PDF, and send the PDF as an attachment. 
> 
> Jim
> 
>> On May 31, 2016, at 8:15 PM, Vince  wrote:
>> 
>> Ooops!  Copy from AOO-Calc and Paste to Thunderbird 45.1.0 for the mail list 
>> did not go very well (my first attempt) with that array.
>> 
>> 
>> If anyone is interested, is there a suggestion for how I could project the 
>> array properly for email list viewing?
>> 
>> VinceB.
>> 
>> 
>>> On 5/31/2016 8:04 PM, Vince wrote:
>>> Hi Brian:
>>> 
>>> I created a play-file that used the array shown below:
>>> 
>>> 
>>>  B CD   
>>>   E
>>> * */Week #/* */Game #1/* */Game #2 /* */Game #3/*
>>> 8
>>> 9 1 141 137 
>>> 135
>>> 10   2  97 135  
>>> 134
>>> 113 193   172   
>>>   138
>>> 124 151 185 
>>>  125
>>> 135 197 127 
>>>  186
>>> 146 153 138 137
>>> 157 158 188  134
>>> 168 132 150   132
>>> 
>>> Your suggested formula (=SUMPRODUCT($D$9:$F$16>100;$D$9:$F$16<129), with a 
>>> modified target range, results returned are:
>>> 
>>> *<100*   *1*
>> 
>>> *100 thru 129* *2*
>>> *130 thru 139* *10*
>>> *140 thru 149* *1*
>>> *150 thru 159* *3*
>>> *160 thru 169* *0*
>>> *170 thru 179* *1*
>>> *180 thru 189* *3*
>>> *190 thru 199* *3*
>> 
>> 
>> -
>> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
>> For additional commands, e-mail: users-h...@openoffice.apache.org
> 
> 
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: users-h...@openoffice.apache.org
> 

Re: Detecting/Counting Cell's Values if data is within a Specific Range of Values

[James Plante]

One way would be to export it to PDF, and send the PDF as an attachment. 

Jim
 
> On May 31, 2016, at 8:15 PM, Vince  wrote:
> 
> Ooops!  Copy from AOO-Calc and Paste to Thunderbird 45.1.0 for the mail list 
> did not go very well (my first attempt) with that array.
> 
> 
> If anyone is interested, is there a suggestion for how I could project the 
> array properly for email list viewing?
> 
> VinceB.
> 
> 
> On 5/31/2016 8:04 PM, Vince wrote:
>> Hi Brian:
>> 
>> I created a play-file that used the array shown below:
>> 
>> 
>>   B CD   
>>   E
>> * */Week #/* */Game #1/* */Game #2 /* */Game #3/*
>> 8
>> 9 1 141 137  
>>135
>> 10   2  97 135   
>>134
>> 113 193   172
>>  138
>> 124 151 185  
>> 125
>> 135 197 127  
>> 186
>> 146 153 138 137
>> 157 158 188  134
>> 168 132 150   132
>> 
>> Your suggested formula (=SUMPRODUCT($D$9:$F$16>100;$D$9:$F$16<129), with a 
>> modified target range, results returned are:
>> 
>> *<100*   *1*
> 
>> *100 thru 129* *2*
>> *130 thru 139* *10*
>> *140 thru 149* *1*
>> *150 thru 159* *3*
>> *160 thru 169* *0*
>> *170 thru 179* *1*
>> *180 thru 189* *3*
>> *190 thru 199* *3*
>> 
> 
> 
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: users-h...@openoffice.apache.org


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

Re: Detecting/Counting Cell's Values if data is within a Specific Range of Values

[Vince]

Hi Dennis:

Your suggestion ( =COUNTIF(F342:N380;">99")-COUNTIF(F342:N380;">129"), 
also worked here.


I have used your suggestion here to generate result in an array, shown 
below:


<100 1
100 thru 1090
110 thru 1190
120 thru 1292
130 thru 13910
140 thru 1491
150 thru 1593
160 thru 1690
170 thru 1791
180 thru 1893
190 thru 1992
200 thru 2090
210 thru 2190
220 thru 2290
230 thru 2390
240 thru 2490
250 thru 2590
260 thru 2690
270 thru 2790

I need to add a couple more rows to that array, not that I expect to 
reach a bowling score within the range of 280 to 300, inclusive, anytime 
soon!


Thanks.

Regards,
VinceB.

On 5/20/2016 12:01 PM, Dennis E. Hamilton wrote:

There might be a more direct way, but try

  =COUNTIF(D342:L380;">99")-COUNTIF(D342:L380;">129")

If you mean between 100 and 129 inclusive.  Adjust as necessary.

  - Dennis

Re: Detecting/Counting Cell's Values if data is within a Specific Range of Values

[Vince]

Hi Brian:

I created a play-file that used the array shown below:


B
C
D
E
*
*   */Week #/*  */Game #1/* */Game #2 /**/Game #3/*
8




9
1   141 137 135
10
2   97  135 134
11
3   193 172 138
12
4   151 185 125
13
5   197 127 186
14
6   153 138 137
15
7   158 188 134
16
8   132 150 132

Your suggested formula (=SUMPRODUCT($D$9:$F$16>100;$D$9:$F$16<129), with 
a modified target range, results returned are:


*<100*   *1*
*100 thru 129*  *2*
*130 thru 139*  *10*
*140 thru 149*  *1*
*150 thru 159*  *3*
*160 thru 169*  *0*
*170 thru 179*  *1*
*180 thru 189*  *3*
*190 thru 199*  *3*




I was expecting to use some sort of a Boolean expression or domain 
limits; however,  I will read up on =SUMPRODUCT( ... ).


You might have guessed that the array data is derived from my bowling 
scores. Since JAN 2008, I have recorded, within a Calc sheet, the scores 
for more than 1500 games; lots of data to play with and, at the same 
time, learn about using AOO-Calc.


Thank you.

VinceB.

__

On 5/20/2016 1:34 PM, Brian Barker wrote:

At 11:01 20/05/2016 -0400, Vince Bonly wrote:

I am using this
=COUNTIF(D342:L380;">100")
... . Possible data values found within D342 and L380 include: 0 
through 300. However, what I really want to count is all data values 
between 100 and 129, ...




I should have written: "... what I really want is to count all data 
values from 100 to 129, inclusive; from 130 to 139, inclusive; etc. etc. 
thus yielding an array like this:



<100 1
100 thru 1292
130 thru 13910
140 thru 1491
150 thru 1593
160 thru 1690
170 thru 1791
180 thru 1893
190 thru 1993
200 thru 2090
210 thru 2190
220 thru 2290
230 thru 2390
240 thru 2490
250 thru 2590
260 thru 2690
270 thru 2790




As an alternative to what has already been suggested, you could use:
=SUMPRODUCT(D342:L380>100;D342:L380<129)

The two comparisons each generate an array of boolean values. When the 
array of products is formed, TRUE is interpreted as 1 and FALSE as 0 - 
so the result is 1 for each cell for which both criteria are true and 
0 otherwise. Summing those 1s effectively counts them and gives you 
the result you need.


I trust this helps.

Brian Barker

Re: Detecting/Counting Cell's Values if data is within a Specific Range of Values

[Brian Barker]

At 11:01 20/05/2016 -0400, Vince Bonly wrote:

I am using this
=COUNTIF(D342:L380;">100")
... . Possible data values found within D342 and L380 include: 0 
through 300. However, what I really want to count is all data values 
between 100 and 129, ...


As an alternative to what has already been suggested, you could use:
=SUMPRODUCT(D342:L380>100;D342:L380<129)

The two comparisons each generate an array of boolean values. When 
the array of products is formed, TRUE is interpreted as 1 and FALSE 
as 0 - so the result is 1 for each cell for which both criteria are 
true and 0 otherwise. Summing those 1s effectively counts them and 
gives you the result you need.


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

RE: Detecting/Counting Cell's Values if data is within a Specific Range of Values

[Dennis E. Hamilton]

There might be a more direct way, but try

 =COUNTIF(D342:L380;">99")-COUNTIF(D342:L380;">129")

If you mean between 100 and 129 inclusive.  Adjust as necessary.

 - Dennis

> -Original Message-
> From: Vince [mailto:wa.two...@verizon.net]
> Sent: Friday, May 20, 2016 08:02
> To: AOO Mail List <users@openoffice.apache.org>
> Subject: Detecting/Counting Cell's Values if data is within a Specific
> Range of Values
> 
> Using AOO 4.1.2 and Win 8.1
> _
> 
> I am using this
> =COUNTIF(D342:L380;">100")
> in a Calc cell, and it returns a count of all data values within the
> D342:L380 that are greater than 100 .  Possible data values found within
> D342 and L380 include: 0 through 300.
> 
> However, what I really want to count is all data values between 100 and
> 129, for example.
> IOW, count if data value within cells D342 through L380 is between 100
> and 129.
> 
> What is the required formula/function in Calc to reach my goal?
> 
> Regards,
> Vince B.
> 
> 
> -
> To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: users-h...@openoffice.apache.org


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

Detecting/Counting Cell's Values if data is within a Specific Range of Values

[Vince]

Using AOO 4.1.2 and Win 8.1
_

I am using this
=COUNTIF(D342:L380;">100")
in a Calc cell, and it returns a count of all data values within the 
D342:L380 that are greater than 100 .  Possible data values found within 
D342 and L380 include: 0 through 300.


However, what I really want to count is all data values between 100 and 
129, for example.
IOW, count if data value within cells D342 through L380 is between 100 
and 129.


What is the required formula/function in Calc to reach my goal?

Regards,
Vince B.


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