We help IT Professionals succeed at work.

How to find additional cumulative percentage in a histogram

536 Views
Last Modified: 2012-02-12
Hi guys,

I have the following histogram results made in Excel:
Bin     Frequency    Cumulative %
0                 1             0.00%
35              276           0.60%
70             4444          2.90%
105            801           4.63%
140           2375          9.76%
175           3444          54.99%
210           7777          79.46%
245            87            91.39%
280         99999          95.98%
315            87            97.96%
350            34            98.86%
385            55            99.29%
More         77           100.00%
It is possible to find out what are the bin values and/or frequency values for the cumulative percentages of 80%, 90% and 95% by using Access or Excel capabilities?
Thank a lot
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
how is this histogram results presented ?
is it like data in a regular worksheet ?
if that is the case , you can use vba from access to read the values and make comparison..

are you looking for exact values of 80%, 90% and, 95% ?

upload a copy of the excel fle..

Author

Commented:
Thank you capricorn,
Yes I am interested in these values and also 50% and 60%.
You want all the data used for histogram or only the the histogram I posted here?
Thank you and I will send the data you want a little later - I have to go for 1 hour
Thanks again

Author

Commented:
I attached the file with the data you requested.
Thank you
Book1.xls
CERTIFIED EXPERT
Top Expert 2016

Commented:
where do you want to display the results ?

also, in the excel file you uploaded, the values you wanted to find, does not exists.

Author

Commented:
You can display the results on other worksheet.
I am not sure what you are saying. I would like if possible what are the bin values and frequency values if cumulative percent are 50%, 60%, 80% ,90% and 95%.
Thank you
CERTIFIED EXPERT
Top Expert 2016

Commented:
in the excel file you uploaded, the values you wanted to find, 50%, 60%, 80% ,90% and 95%. does not exists. so there is nothing to find
CERTIFIED EXPERT
Top Expert 2016

Commented:
sorry, i think i misunderstood what you want to find..

Author

Commented:
Do I have to understand that what I want is not possible to get with Access or Excel?
Thank you
CERTIFIED EXPERT
Top Expert 2016

Commented:
the way i see it, perhaps, not sure it can be done by reverse calculation but, i am not well verse with this. ask this question to be deleted and post another question and add the Math zone.. sorry
What's the application? How much accuracy is needed? is this something you need to repeat for many data sets or is this it?

The solution is to fit a cumulative frequency distribution to the bin bounds and interpolate to your required percentages. I've attached a linear interpolation, implemented directly in Excel.

A smoother interpolation, say cubic splines, could be done with a little bit of VBA programming.

If the data are rough and it makes more sense to fit a curve that doesn't go exactly through the bin values, that can also be done with a spline with fewer knots and some fancier math involving some linear algebra. I can do that too but haven't time right now; someone on the Math forum probably knows of a software library.
Linear.xls

Author

Commented:
Thank you for your help ifpugnacious.
Can you send me a link or something to see the formula you used to interpolate the cumulative percentage?
Thanks again

Author

Commented:
"A smoother interpolation, say cubic splines, could be done with a little bit of VBA programming."
Can you please do it?
Thank a lot,
Marian
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I want to compare some yearly statistical results using the same cumulative percentages of different years.
Unfortunately histograms in Excel doesn't allow me to do that.
"A smoother interpolation, say cubic splines, could be done with a little bit of VBA programming."
Can you please do it?
Thanks a lot,
Marian
My Excel 2010, maybe with some add-ins, does. Look at Percentile functions under Statistics.

Author

Commented:
I will do it.
I don't have Excel 2010 but I will try.
Thanks again

Author

Commented:
Thank you very much

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.