You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Vince <wa...@verizon.net> on 2016/06/01 00:04:49 UTC
Re: Detecting/Counting Cell's Values if data is within a Specific
Range of Values
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 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
200 thru 209 0
210 thru 219 0
220 thru 229 0
230 thru 239 0
240 thru 249 0
250 thru 259 0
260 thru 269 0
270 thru 279 0
> 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
Posted by "Vince B." <wa...@verizon.net>.
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 <ji...@me.com> 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 <wa...@verizon.net> 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 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*
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
>> For additional commands, e-mail: users-help@openoffice.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
Re: Detecting/Counting Cell's Values if data is within a Specific
Range of Values
Posted by James Plante <ji...@me.com>.
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 <wa...@verizon.net> 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 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*
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Detecting/Counting Cell's Values if data is within a Specific
Range of Values
Posted by Vince <wa...@verizon.net>.
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 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*
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org