I'm trying to also find the numbers that are in the 25th and 75th percentile, is there an easy way to do this?
Column H = Performance Rating (1-5)
Column T = The bonus payout % (example 105%)
I'm trying to pull the 25th and 75th percentile for each rating. So if there are 100 employees rated 1, what is the bonus payout that is the 25th percentile? This is just an example, in this case it would be the 25th number in the list....I'm dealing with thousands of bonus %'s.
I'm looking to use the percentile formula given a condition. The percentile formula as I understand it is percentile (array,k), where K is the percentile I want.
What I want to do is select a column for the percentile, but only do it for values above zero (I don't want to get into the full explanation as to why but I cannot cheat and just sort the columns).
The context is that I was doing some averageif calculations previously and want to remove outliers (top 5 and bottom 5% of data).
I have to run the 95th percentile value against a raw data dump from a monitoring tool. The data has multiple devices and each device has about a couple of thousands of samples/values. These values are dynamic, it can be 1000 samples for device 1 and may be 1300 for device 2. The data between 2 devices are separated by an empty row. run a macro using the percentile formula that can accept a dynamic range in the array so that it should not matter how many samples/values each device has, it should provide the 95 Percentile value.
Current Option: =Percentile(array,value)
What I'm looking for is: =Percentile(A dynamic range starting from a designated cell to all the way till the last cell in that range i.e before the empty row that separate the two devices,value)
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.
Is there any function in Excel that returns all values from a list above or below a certain percentage rank/percentile?
For instance, if you want the average of the 2% highest numbers in a list? Or the sum of the 25% most expensive items in a shopping catalogue?
It has to be a "dynamic" function, where you can just drop in a set of figures, sort the list, add a percentile, and perform a calculation on all the figures from the cutoff point signalled by the percentile...
I have a list of 186 random numbers sorted by descending order in column A. In column B I have another statistic of corresponding values to the number in column A. I am hoping to calculate the average of values in column B based on the percentiles of column A. (i.e. for the top 10th percentile values of column A the average B value is X, for the top 20th percentile of values in column A, the average B value is Y).
I am having trouble using formulas in a smart way in excel.Basically, I am trying to calculate percentile for a range. But since my data set is huge, I dont want to select the range in percentile formula manually. my data set includes buckets and would love to be able to search for that field and then calculate percentile on a range. is that possible?
example
see attached excel file -its a small data set, but there are more buckets in the volume column. Using Excel 2007
I have two columns of numbers (this week - Column A and last week - Column B). What I need to do is look at the numbers and pull two lists out in Columns C and D. In columns C a list of the numbers that are missing from last week in column D numbers that are missing that were added from the previous week. The two lists consist of about ten thousand rows of data.
These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.
I want to create a formula that values if the number in the cell is in the biggest 25 numbers in the column and also check in another column the same thing.If the number is within the 25 biggest from the first column or the second then to give me the number of the cell. I created a formula but it is very large in order to apply for the 1st to the 25th biggest number. The formula is:
IF(AND(or(K16=LARGE($K$16:$K$152,1),I16=LARGE($I$16:$I$152,1)),K16<>0),K16,"") but only for the 1st biggest number of the columns.
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
I have a workbook that has 3 columns (G, H I) with numbers from 1 to 7 in each column. I want to tell excel that if a row has a particular 3 numbers in these cells then change it to a specified number. I want to do this for the entire workbook. Is this possible?
I have a list of numbers in a column and I need to find which numbers when summed together equal a figure. I have a list of invoice amounts that I need to match up with payments (the payments are always made for several invoices so I need to come up with sums of several invoices to get to this payment amount).
An example would be I have this in the following section (A1:A10): $17,213.82 $4,563.02 $85,693.42 $1,166.01 $725.90 $580.09 $2,243.75 $240.16 $207.70 $725.90
I need to find which combination of these figures would sum $1,173.76.
I have a file with 2 worksheets: Worksheet "Data" contains Store Locations, Store Numbers, Categories, Brands, Sales, Clusters, % Demand, etc.
Under % Demand on Worksheet "Data", I need a formula to look for % Demand from Worksheet "Stores" that related to the same categories, brands and stores numbers.
I have a file with approx 3600 rows. In Col B there is a 13 digit EAN-13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the B-column to the cell below and find out if there are some missing numbers. If the numbers are incremental then goto next cell. But if there's a gap in the numbers then it should insert the missing numbers with an increment of 1. I think the logic statement should look like this:
i = 1 For x = 1 to last row If the gap between B(i) and B(i+1) < 1 then Insert a row and put the missing value in the cell on the new row. Endif i = i+1 Next x
The comparison has to be made from the 5 digits in place 8-12 in the code.
Here's an example (i've dimmed the digits that shouldn't be compared):
As you see there's a missing number between the third and fourth row. Here the value should be 733098592074 + the checksum that is a bit tricky to calculate. Between the fifth and sixth row there are four missing numbers that has to be filled in. The checksum is OK to leave out at this moment. The important thing is to get the 5 digits.
I have a very large database (42455 lines) which has three columns. The column A is a shipment number which is a combination of numbers and letters, column B is a ship date, column C is a dollar value for each shipment. This range (A2:C42455) is named SHIPMENTS. I have a second database right next to it that contains warranty information. Column D has the shipment number, Column E has the date the warranty call came in. These last two columns have only 2788 lines.
I'm using VLOOKUP in column F to match Ship dates with Warranty Call dates. The formula is:
=VLOOKUP(D2,SHIPMENTS,2,False)
and was dragged down all 2788 lines.
Out of the 2788 lines it did not find dates for 301 of them. Some of those 301 have Shipment numbers that are only numbers (no letters). As a test I copied one of the "unfound and all numbers" shipment numbers and did a control+F for all of column A and it found it. Clearly VLOOKUP is missing some of the Shipment numbers. I made sure that the Shipment number in column A matches the number in column D by cancelling out of the Find window and clicking on the found cell. Then I made sure there are no stray spaces tacked onto the end of the number and there were none. I hit enter.
At this point excel starts calculating again and when I go back up to the VLOOKUP (In column F) it has now found the data I've asked it for originally. I've not changed any of the values of either cell...All I've done is use Control+F to manually find the Shipment number in SHIPMENTS and then verified that there were no spaces in the found cell by clicking on it and using the arrow keys. Once that was done I hit enter.
Somehow this sequence of actions allows VLOOKUP to now find the cell.
I have two spreadsheets. Spreadsheet 1 has 6 columns and column E has a list of numbers. It runs from E2:E739.
Spreadsheet 2 has 9 columns and column A has the numbers I need matched against. Column A runs from A9:A5027. How can I find the numbers from Spreadsheet 1, Column E that are also in Spreadsheet 2, Column A. Once matched I would like to highlight the ROW on spreadsheet 1 that has a match.
