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

[Sam via OfficeKB.com]

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

 

[Domenic]

Can you provide an example?

 

[Sam via OfficeKB.com]

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

 

[Ron Rosenfeld]

Hi Domenic,


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

 

[Sam via OfficeKB.com]

Hi Ron,

Thanks for reply.
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

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 Rosenfeld]

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,

Thanks for reply.
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

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

 

[Sam via OfficeKB.com]

Hi Ron,

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

Cheers,
Sam

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 Rosenfeld]

You're welcome.

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

Best wishes,

Hi Ron,

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

Cheers,
Sam

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

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much. Formulas works very well.

Cheers
Sam

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