I have a collection of data where the lower the value, the better. When I use Quartile/Percentile, it shows higher values as having a higher rank. Any way to reverse the order, without changing the underlying data (ie. multiply all values by -1)?
Also, I have a graph of the data showing the 1st, 2nd (median) and 3rd quartile and then a particular data set (in this case, company results) over a number of years. Any way to automatically add the Percentrank as a data label for the specific company results? For example, the value 200 puts this company at the 65th percentile. I want the data label to show 65, not 200.
In need of a solution here...
I have several sheets in a workbook containing different sets of data. I then have one master sheet which references specific cells from all of these data sheets so only the info I need is displayed in an organized manner on one sheet.
The first problem I ran into was that when I referenced the cells from other sheets, if the cell is blank, the reference would display a 0, instead of blank. So to fix this I did an IF statement, where if the cell was "" then it would display "". This fixed the problem until I started charting the condensed data on the one master sheet (which references the other sheets using the IF statement). When I plotted this data, the empty strings "" would be plotted as a value of 0, instead of being left off the plot. There are option to plot "gaps" as zero, or as nothing, and I told it to plot it as nothing... only problem is that it doesn't see it as a gap, it sees it as "" which then plots as 0.. making my line graphs fall to zero, when it is far more accurate in my case to have these data points not displayed.
So I changed the IF statements to place #N/A in the cells instead of "", which fixed the problem for the charts.
NOW I am trying to take averages and perform statistics functions on this set of data and I am having some issues, as taking the average of a range containing #N/A cells will simply yield #N/A. I was able to use DAverage(), DMin(), DMax() and DStDev() with a criteria of #N/A to fix the problem, but I am also in need of quartile or percentile data. Unfortunately there is no DQuartile function (that I am aware of).
If there any kind of DQuartie or QuartileIF statement that I am unaware of?? Any suggestions on how I could do this? Thanks.
Howdy, I'm trying to calculate the 25th, 50th (ie median) and 75th percentile
of costs to maintain infrastructure using a dataset which has various (ie 11)
categories to analyse. I'm using the auto filter option to select each
category.
ie
Group Roads
1 $33,911
1 $27,500
1 $34,834
....
11 $11,782 etc
Any advice regarding an appropriate formula would be greatly appreciated.
Cheers
Pete
I'm trying to find the 25th percentile value for a column of numbers, but I want to exclude all the "0" values from the calculation.
Do you know if there is a way to do this in Excel?
I know the standard percentile function would be =percentile(range, .25).
Thank you very much.
I am having difficulty setting up a Excel sheet for the following problem.
An industrial Engineer at ABC Products wants to determine whether there are more units produced on PM shift then AM Shift.
Sample of 54 Am workesr with a mean number of units produced is 345 with a standard deviation of 21.
Sample of 60 PM workser with a mean number of units produced is 351 with a standard deviation of 28 units.
Significance level is .05
Trying to figure out if the PM shift is larger.
I have a data set based on an uneven distribution of cards in a 60 card deck, each with a value ranging from 0-5.
I need to build a probability distribution of all the random samples of 10. So, how many random sets of 10. have an average value of 1 (or a total of 10) etc.
Then, I need to build a variable distribution so that it changes as I change the sample size. So, with a sample of 20 cards, what does the distribution look like?
Can this be done in excel? Should I post what I have now to work with?
Hi everyone, first thanks for the help.
I am trying to create a histogram and let me explain some of the data.
I have a list of data separated like so:
3.5
3.6
3.8
3.8
3.9
3.9
etc...
Now I need to calculate the frequencies of these like so:
3.5 (up to but not including) 4.0
4.0 (up to but not including) 4.5
I can count out the frequencies myself but i need to create a histogram (bar graph) with the y axis as the frequency and the x axis as the IMR(infant mortality rate: ie the 3.5 (up to but not including) 4.0).
I am having problems with calculating the frequencies because when setting the bin values because they include 4.0.
If this is confusing I can provide some more information, but cannot find a good tutorial that would create a histogram anywhere remotely close to the histogram i drew by hand.
Hi Lords and Ladies!
I was wondering if there are any statistical tools in excel?
What I need is a comparison program to do the following:
Column A: abcdefgh
Column B: bcaghfrnreo
Column C: 80% match (roughly)
Is that possible to do in excel?
Regards,
Don Defy
Morning All,
Does anyone know of a free tutorial that can explain how to get statistical data in Excel? - I have seen it done before (in excel) for 'Linear regression', I am wondering if the same can be done for "Polynomal"?
Rgs
Neville
