Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default 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
  #2   Report Post  
Domenic
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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

  #3   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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
  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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
  #5   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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


  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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
  #7   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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
  #8   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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
  #9   Report Post  
Domenic
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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

  #10   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default Return Range of Numerical Values in Single Column based on Frequency Percentage

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
return a value based on a range Michael NYC Excel Worksheet Functions 3 September 28th 05 05:49 PM
Formula to compare multiple rows values based on another column? Murph Excel Worksheet Functions 4 February 21st 05 03:44 AM
How sum values in column B using values in column A as the conditi oldgrayelf Excel Worksheet Functions 5 February 4th 05 10:03 PM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 03:09 PM
Returning a Value to a Cell Based on a Range of Uncertain Size amc422 Excel Worksheet Functions 7 November 14th 04 04:03 PM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"