Return Range of Numerical Values in Single Column based on Frequency Percentage

Hi All,

I have a single column of numerical values that may repeat many times within
the column.

I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered – so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value
– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.

Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

Can you provide an example?

In article <56638f64018ff@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi All,
>
> I have a single column of numerical values that may repeat many times within
> the column.
>
> I require a flexible Formula:
> Use an Input Cell for the specified and changeable Percentage(s) %.
> Column may be filtered – so only take into account Visible Filtered Cells.
>
> The Formula will calculate and Return the numerical range of values that fall
> between the specified percentage % (using the Input Cell) e.g.; 70%.
>
> The Formula should Return two numeric values: a Start Value and an End Value
> – NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
> values that appear 70% of the time in the column; therefore, taking into
> account Repeat / Duplicate values.
>
> The calculated Results: the two numeric values will be returned to separate
> cells on a new Sheet.
>
> Thanks
> Sam

Hi Domenic,

>Domenic wrote:
>Can you provide an example?

A small sample:
Column C
58
53
57
51
53
53
68
55
54
63
51
55
59
53
51
52
56
55
67
53
52
60
57
51
60
53
52
73
52
52
54
53
51
54
53
57
66
53
51
54
57
65
52
52
52
52
52
63
54
51

Cheers
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

On Wed, 26 Oct 2005 20:17:47 GMT, "Sam via OfficeKB.com" <u4102@uwe> wrote:

>Hi Domenic,
>
>>Domenic wrote:
>>Can you provide an example?
>
>A small sample:
>Column C
>58
>53
>57
>51
>53
>53
>68
>55
>54
>63
>51
>55
>59
>53
>51
>52
>56
>55
>67
>53
>52
>60
>57
>51
>60
>53
>52
>73
>52
>52
>54
>53
>51
>54
>53
>57
>66
>53
>51
>54
>57
>65
>52
>52
>52
>52
>52
>63
>54
>51
>
>Cheers
>Sam

How about an example of data and inputs WITH RESULTS??

None of your numbers appear 70% of the time. 52 is the most common entry and
it only appears 20% of the time.


--ron

Hi Ron,

Thanks for reply.

>>Ron Rosenfeld wrote:
>>How about an example of data and inputs WITH RESULTS??
I do not have the RESULTS that is why I require the Formula.
>>
>>None of your numbers appear 70% of the time. 52 is the most common entry and it only appears 20% of >>the time.
My initial quote of 70% was too high for the sample I provided; however, what
I'm trying to find is a Formula that will be flexible enough to deal with any
percentage I choose - If you have a Formula that has correctly identified
that my sample values appear no more than 20% of the time, I would appreciate
your assistance.

Cheers
Sam

Ron Rosenfeld wrote:
>>Hi Domenic,
>>
>[quoted text clipped - 56 lines]
>>Cheers
>>Sam
>
>How about an example of data and inputs WITH RESULTS??
>
>None of your numbers appear 70% of the time. 52 is the most common entry and
>it only appears 20% of the time.
>
>--ron


--
Message posted via http://www.officekb.com

I'm still not sure what you're getting at.

However, to determine the percent of any sample value, you can use the formula:

=COUNTIF(rng,sample_value)/COUNT(rng)



On Wed, 26 Oct 2005 22:18:49 GMT, "Sam via OfficeKB.com" <u4102@uwe> wrote:

>Hi Ron,
>
>Thanks for reply.
>
>>>Ron Rosenfeld wrote:
>>>How about an example of data and inputs WITH RESULTS??
>I do not have the RESULTS that is why I require the Formula.
>>>
>>>None of your numbers appear 70% of the time. 52 is the most common entry and it only appears 20% of >>the time.
>My initial quote of 70% was too high for the sample I provided; however, what
>I'm trying to find is a Formula that will be flexible enough to deal with any
>percentage I choose - If you have a Formula that has correctly identified
>that my sample values appear no more than 20% of the time, I would appreciate
>your assistance.
>
>Cheers
>Sam
>
>Ron Rosenfeld wrote:
>>>Hi Domenic,
>>>
>>[quoted text clipped - 56 lines]
>>>Cheers
>>>Sam
>>
>>How about an example of data and inputs WITH RESULTS??
>>
>>None of your numbers appear 70% of the time. 52 is the most common entry and
>>it only appears 20% of the time.
>>
>>--ron

--ron

Hi Ron,

Thank you very much for the Formula to determine the percentage of any sample
value.

Cheers,
Sam

Ron Rosenfeld wrote:
>I'm still not sure what you're getting at.
>
>However, to determine the percent of any sample value, you can use the formula:
>
>=COUNTIF(rng,sample_value)/COUNT(rng)
>
>>Hi Ron,
>>
>[quoted text clipped - 26 lines]
>>>
>>>--ron
>
>--ron


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

You're welcome.

But please post back in this thread as you develop the concept of what you want
to do next.

Best wishes,



On Thu, 27 Oct 2005 01:00:07 GMT, "Sam via OfficeKB.com" <u4102@uwe> wrote:

>Hi Ron,
>
>Thank you very much for the Formula to determine the percentage of any sample
>value.
>
>Cheers,
>Sam
>
>Ron Rosenfeld wrote:
>>I'm still not sure what you're getting at.
>>
>>However, to determine the percent of any sample value, you can use the formula:
>>
>>=COUNTIF(rng,sample_value)/COUNT(rng)
>>
>>>Hi Ron,
>>>
>>[quoted text clipped - 26 lines]
>>>>
>>>>--ron
>>
>>--ron

--ron

To return the minimum and maximum, we can adapt Ron's formulas as
follows...

=MIN(IF(A2:A100<>"",IF(COUNTIF(A2:A100,A2:A100)/COUNT(A2:A100)>C2,A2:A100
)))

and

=MAX(IF(COUNTIF(A2:A100,A2:A100)/COUNT(A2:A100)>C2,A2:A100))

....where C2 contains your criteria, such as 70% or .70. Both these
formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

With regards to filtered data, I would use a formula or several formulas
(for efficiency) to create a new list of the filtered data on a separate
sheet (which can be hidden) and then use the above formulas on the new
list.

If you'd like to try it and need help, post back.

Hope this helps!

In article <56638f64018ff@uwe>, "Sam via OfficeKB.com" <u4102@uwe>
wrote:

> Hi All,
>
> I have a single column of numerical values that may repeat many times within
> the column.
>
> I require a flexible Formula:
> Use an Input Cell for the specified and changeable Percentage(s) %.
> Column may be filtered – so only take into account Visible Filtered Cells.
>
> The Formula will calculate and Return the numerical range of values that fall
> between the specified percentage % (using the Input Cell) e.g.; 70%.
>
> The Formula should Return two numeric values: a Start Value and an End Value
> – NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
> values that appear 70% of the time in the column; therefore, taking into
> account Repeat / Duplicate values.
>
> The calculated Results: the two numeric values will be returned to separate
> cells on a new Sheet.
>
> Thanks
> Sam

Hi Domenic,

Thank you very much. Formulas works very well.

Cheers
Sam

Domenic wrote:
>To return the minimum and maximum, we can adapt Ron's formulas as
>follows...
>
>=MIN(IF(A2:A100<>"",IF(COUNTIF(A2:A100,A2:A100)/COUNT(A2:A100)>C2,A2:A100
>)))
>
>and
>
>=MAX(IF(COUNTIF(A2:A100,A2:A100)/COUNT(A2:A100)>C2,A2:A100))
>
>...where C2 contains your criteria, such as 70% or .70. Both these
>formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
>With regards to filtered data, I would use a formula or several formulas
>(for efficiency) to create a new list of the filtered data on a separate
>sheet (which can be hidden) and then use the above formulas on the new
>list.
>
>If you'd like to try it and need help, post back.
>
>Hope this helps!
>
>> Hi All,
>>
>[quoted text clipped - 18 lines]
>> Thanks
>> Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1