Hi guys I hope you can help me out.
I have a large data set where my results are given as a numerical value and another column with the number of observations for each value. I want to calculate quartiles and maybe some percentiles, but I have not been able to do this.
Cheers Peter Kyvsgaard
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi Sithsense
I have attach a sample of my data here. The observations are made over several decades and I want to have the result for each year. I hope you can see where I want to end up.
What is the expected result for 1985?
The quartiles should be something like 241, 247 and 255.
Sorry I am unable to understand the logic
Could you please explain how you are arriving it so that I can offer the formula based on your logic
I did it manually by adding observations so that there is two observations of 238, 7 for 241 and so on. Then I used excels quartile funktion. This is just a far to time consuming method.
@pedter
add the formula of percentile AND the manualy result in your file and add it on the forum.
looks like a pivot table solution, although percentile is not available in PivotTable.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
This is how I got the quatiles, but I am looking for at better way.Data sample2.xlsx
@pedter,
I used several helpcolumns (light yellow cell) to get the result (green cells).
See the attached file.
Last edited by oeldere; 03-03-2015 at 03:08 PM. Reason: changed text in green cells
For a single cell formula try this "array formula" in M3
=PERCENTILE.INC(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX($B2:$B12))))>=$B2:$B12,$C2:$C12),M2)
confirm with CTRL+SHIFT+ENTER and copied across
I limited the formula to the 1985 rows but if you want you can take the whole data range and add another condition to check the year, thereby using the same formula to get the Percentiles for all the years
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks