Average Based On Percentile Of Different Column

Feb 26, 2013

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).

View 2 Replies


ADVERTISEMENT

Excel 2007 :: How To Use Percentile Based On Bucket List

Jun 9, 2014

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

View 1 Replies View Related

Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column

Oct 2, 2008

I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

View 2 Replies View Related

Calculate Average For Column Based On Moving Start Column?

May 3, 2013

I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.

Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2

So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.

View 5 Replies View Related

Average From One Column Based Upon Criteria From An Adjacent Column

Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies View Related

Average Column Based On Another Columns Value?

Jul 29, 2014

I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example

comp 1409 99.7
comp 1409 99.5
comp 1409 100
comp 1308 65.7
comp 1509 99.5

I would like that to become

comp 1409 99.73
comp 1308 65.7
comp 1509 99.5

View 6 Replies View Related

Average Column Based On Criteria Of Another

Nov 30, 2007

I am trying to get the average of two columns
column A and Column B
Column A has text (survey type) i.e. Move In, Mid Year, Year End
Column B has the scores given on that survey type i.e. 70
I am trying to get the average of of all the Move In cells from column A
for each survey type so that I get average of all the scores in column B.

View 3 Replies View Related

Finding Average Based On Certain Criteria Of Another Column

Apr 24, 2014

I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.

10
32.4

11
38.6

12.5
23

[Code] .........

View 2 Replies View Related

Average Cells Based On Dulplicates In Other Column

Nov 27, 2006

I was wondering if there is a way for excel to compare cells and identify when they are identical. My objective for this is as follows. Lets say i have some data entered in cells A1:A10. Suppose cells A1,A3,and A4 had the same value (112-343). Now suppose cells B1:B10 had values also, lets say B1=2, B3= 2.5 and B4=8. I need for excel to go through the A1:A10 range and realize when the A cells have identical values, after this is realized i want excel to take the values in the B cell that correspond to the identical A cells, average them and then store them in cell C. And if there are mutliple identical cells i would like the routine to be able to identify this also. lets say A1=A3=A4=(112-343) and A6=A8=A9=(154-456) ....

View 7 Replies View Related

Calculating The Average Of A Column Based On Multiple Conditions

Feb 21, 2008

How do I calculate the average based on multiple conditions? Is my syntax incorrect?

Here's what i'm looking for:

=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))

View 9 Replies View Related

Average One Column Based On Another Column

Dec 14, 2006

I am trying to calculate averages for a ratio I have calculated. Is there a way I can use VBA or Excel functions to average the G column depending on the value of the D column? In other words, find the average value of column G for all Industry Group = 103. Then, find the average value of column G for all Industry Group = 106. Then put industry group # in column H and avg. value in column I.

View 2 Replies View Related

Average Column Of Cells But Ignore Errors And Return Average Of Numbers That Are There

Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies View Related

VBA To Get Average Of Range Of Cells Based On Named Range In Different Column

Apr 10, 2013

I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.

I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.

View 1 Replies View Related

Conditional Array / Percentile

Feb 8, 2014

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).

View 1 Replies View Related

Percentile Values With Different Combination

Mar 10, 2014

I have set of people where by based on Level & designation want to find the percentile on their CTC.

Attached is the sample file. Percentile.xlsx‎

View 6 Replies View Related

Get Percentile Value Over Dynamic Range?

Jul 4, 2013

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)

View 3 Replies View Related

Quartile/Percentile/Rank

Jun 18, 2008

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.

View 9 Replies View Related

What Is The 90th Percentile Of The Defined Array Abc

Jan 31, 2014

I have a table with a set of duplicate values like given in the sample table below;

abc22
abc12
abc33
abc44
def60
def56
def46
ghi78
ghi88
ghi98
klm108
klm118
klm120
klm125
klm140

i need to derive the percentile value of one particular text in another sheet. i.e

i want to get- what is the 90th percentile of the defined array abc

if i use the formula percentile(vlookup(abc,table1,2),90%), the cell is referring only to the first value. i want the entire range to be referred.

View 1 Replies View Related

Returning All Data From A List Above The Nth Percentile

Feb 15, 2006

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...

View 10 Replies View Related

Percentile Applied To Portion Of Group Of Numbers

Nov 29, 2011

I have a list of 400 numbers. I wish to filter down the list to those 500 and then of that list get the 95 percentile.

I have tried

=IF('GRML ECP 01'!O$2:O$584 500 I get a blank. When I run the function manually on the numbers > 500 I get 1800.

View 3 Replies View Related

Find Numbers That Are In 25th And 75th Percentile

May 2, 2014

I use the following formula to pull the median from a list of numbers...

=MEDIAN(IF(($H$2:$H$7800=$H7813)*($T$2:$T$7800""),$T$2:$T$7800*0.01))

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.

View 4 Replies View Related

Formula To Calculate Average Values In Column B Where Value In Column A

Mar 11, 2013

I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:

=AVERAGEIF($A$2:$A$21,E1,B2:B21)

Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.

Year value specified in: F1
Month value specified in: G1

View 9 Replies View Related

Average Column Depending On Values In Another Column

May 17, 2009

I have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.

Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc).
My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).

Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.

For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.

For an example I have attached a month of February where there are blank date cells at either end of the column.

View 13 Replies View Related

Average Based On Top 90%

Sep 4, 2009

I have a column of data. Some are "--" and others have a number of 1-100.

I need to average the top ranked 90% of that data without including the "--" in the average. Is there a function I can use to do this?

View 9 Replies View Related

Conditional Median Formula Into Conditional Quartile / Percentile?

Apr 15, 2014

Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!$B$15:$DZ$15=1000)

View 2 Replies View Related

Average Based On Conditions?

Jul 1, 2014

I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.

View 5 Replies View Related

Average Based On Certain Cell Value

Feb 20, 2014

I have attached spreadsheet so it may visualize

What I want to do is enter a date in cell F2 and it will look up that value in column c and will then add up that days number and the 2 days numbers before that date in column d and average them out.

So in the example i have used 20 january 2014, the 20th ,19th and 18th of january have values of 100,40 and 10 so in cell I2 it should return the value 50 ( the average of the 3 numbers )

But this would change with new dates going in cell F2.

trial.xlsx‎

View 2 Replies View Related

Average Based On Criteria

Aug 26, 2006

I have two columns. Column A has lots of repeated values, and column B is a list of numbers. For example:

A 1
A 3
A 4
A 2
A 6
B 1
B 1
B 2
B 1
C 3
C 3
C 2
C 4

Is there any way to output the following:

A 3.2
B 1.25
C 3

Where the second column are the averages. Any Excel function or VBA method will be fine.

View 9 Replies View Related

Average Based On Many Criteria

Oct 18, 2006

i have a excel spreadsheet that contains point of person. Every week a person can or can not compete. That means that there is some days that is blank wouthout points. I want to get the latest 5 points of a person and then out of that 5 i want to get the top 3 point.

View 6 Replies View Related

Average Every Other Column

Oct 21, 2008

Is there a way to average every other row in excel?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved