Condtional Quartile statement

1. Condtional Quartile statement

Hi,
I have a column with powers in it another column with light levels in it

like:
col A colB
23 742
23 674
22 430
22 231
23 800
20 100

I want to get the first and second Quartile of all the data in the range for
each of the powers. My spreadsheet is large, over 30,000 rows and the
data is not organized well.

How would I extract out quartile "1" of col B to output in Cell C1
I only want the quartile for data in rows where Col A = 23w?

thank you
BillReese

2. Re: Condtional Quartile statement

30000 rows sound like an awful lot for an array formula but here goes

=QUARTILE(IF(A1:A8=23,B1:B8),1)

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

"Verizon news" <BillReese@Unicom.com> wrote in message
news:oBY1f.4040\$nz.1267@trnddc03...
> Hi,
> I have a column with powers in it another column with light levels in it
>
>
> like:
> col A colB
> 23 742
> 23 674
> 22 430
> 22 231
> 23 800
> 20 100
>
>
> I want to get the first and second Quartile of all the data in the range
> for each of the powers. My spreadsheet is large, over 30,000 rows and
> the data is not organized well.
>
> How would I extract out quartile "1" of col B to output in Cell C1 I
> only want the quartile for data in rows where Col A = 23w?
>
> thank you
> BillReese
>

3. Re: Condtional Quartile statement

Thank you, I tried to do the same exact thing, but I was trying to put the
"IF" on the outside of the quartile..

now it works very, very good.

Tom

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:ePFLgPGzFHA.2348@TK2MSFTNGP15.phx.gbl...
> 30000 rows sound like an awful lot for an array formula but here goes
>
> =QUARTILE(IF(A1:A8=23,B1:B8),1)
>
> entered with ctrl + shift & enter
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
>
> "Verizon news" <BillReese@Unicom.com> wrote in message
> news:oBY1f.4040\$nz.1267@trnddc03...
>> Hi,
>> I have a column with powers in it another column with light levels in it
>>
>>
>> like:
>> col A colB
>> 23 742
>> 23 674
>> 22 430
>> 22 231
>> 23 800
>> 20 100
>>
>>
>> I want to get the first and second Quartile of all the data in the range
>> for each of the powers. My spreadsheet is large, over 30,000 rows and
>> the data is not organized well.
>>
>> How would I extract out quartile "1" of col B to output in Cell C1 I
>> only want the quartile for data in rows where Col A = 23w?
>>
>> thank you
>> BillReese
>>

>

4. Re: Condtional Quartile statement

Thanks for the feedback

--
Regards,

Peo Sjoblom

"BillReese" <BillReese@Unicom.com> wrote in message
news:BXZ1f.20945\$HM1.3170@trnddc04...
> Thank you, I tried to do the same exact thing, but I was trying to put the
> "IF" on the outside of the quartile..
>
> now it works very, very good.
>
> Tom
>
>
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
> news:ePFLgPGzFHA.2348@TK2MSFTNGP15.phx.gbl...
>> 30000 rows sound like an awful lot for an array formula but here goes
>>
>> =QUARTILE(IF(A1:A8=23,B1:B8),1)
>>
>> entered with ctrl + shift & enter
>>
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>> "Verizon news" <BillReese@Unicom.com> wrote in message
>> news:oBY1f.4040\$nz.1267@trnddc03...
>>> Hi,
>>> I have a column with powers in it another column with light levels in it
>>>
>>>
>>> like:
>>> col A colB
>>> 23 742
>>> 23 674
>>> 22 430
>>> 22 231
>>> 23 800
>>> 20 100
>>>
>>>
>>> I want to get the first and second Quartile of all the data in the range
>>> for each of the powers. My spreadsheet is large, over 30,000 rows and
>>> the data is not organized well.
>>>
>>> How would I extract out quartile "1" of col B to output in Cell C1
>>> I only want the quartile for data in rows where Col A = 23w?
>>>
>>> thank you
>>> BillReese
>>>

>>

>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1