# Create Formula That Creates Average But Excludes Top 10% And Bottom 10% Values?

May 5, 2014how to create a formula that creates an average but excludes the top 10% and bottom 10% values?

View 5 Replieshow to create a formula that creates an average but excludes the top 10% and bottom 10% values?

View 5 RepliesADVERTISEMENT

I've got a worksheet that has chronological quarterly data for which I'd like to do a 5-year rolling average. The layout is: Column A = Dates, Column B = Values, and Column C = 5-year Rolling Average. My problem is that I want to exclude zero-values from the averaging calculation. I tried the following:

={AVERAGE(IF(B1,B5,B9,B13,B170,B1,B5,B9,B13,B17))}

However, Excel will not accept this formula (I assume an array must be contiguous? Please let me know if that's correct).

I am working with averages of ACT scores and Class rank percentages. I cannot figure out how to exclude all the zeros and/or DNR (do not rank) values that are in those columns. For example if I have 10 items, and 2 are DNR, the I want to exclude the 2 DNR's in my average. I tried putting it in the Row or Column area and unchecking what I wanted to exclude and then drag it into the data area, but that didn't work either.

View 2 Replies View RelatedI'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.

For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?

I am needing to do a formula based on a range of data that will return the top third middle third and bottom third.

Example Data

W X Y

Gross Gross

Margin Margin% Rank

51,241 36.46%2

25,584 19.56%20

19,914 16.26%40

17,188 14.27%52

19,167 15.55%43

6,828 5.23%84

48,076 29.17%3

18,282 13.78%47

16,896 12.75%58

16,124 12.14%64

16,549 12.27%59....................

I first need it to look in the rank column for highest number and divide by 3

Then I need it to give me the average Gross Margin if rank is between 1-32, 33-64, 65-95

I have this for the top and bottom but can't get the mid range to work(except it doesn't automatically calculate)

SUMIF($Y$8:$Y$102,"<33",$W$8:$W$102)/32

I have tried averageif....

Using rank to calculate this may not be the most efficent way

I have an array formula in the attached file to calculate the average of the Top 3 (cell F6). I put that same formula in cell F14 to calculate the average of the Bottom 3 (just change the "large" to "small", however it doesn't work. The result shows zero. How do I fix this?

View 10 Replies View RelatedI am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).

My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average.

Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.

=AVERAGEIF($E$4:$E$34,">0")

=AVERAGEIF(E4:E34,">0")

I am trying to do a stock ( goods) inventry for a catering industry, meaning most good swill keep havinf cost prices changing.

I am trying to use a LIFO system. To achieve the end result, i am trying an average formula and i am trying something as follows:

=SUMIF(I4:I20,">0",I4:I20)/COUNTIF(I4:I20,">0")

To try it out, i created a similar page with the same amounts but using a normal multiplication and division formula, the totals dont marry.

i have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?

View 9 Replies View RelatedThe attached workbook has two tabs:

1. Burn Rate - this is where I need my formula to calculate

2. prorder - this is where the table will be

What I need:

1) from 'Burn Rate', get the 'PO ID' we will look up in the table.

2) go to 'prorder' - when the 'PO ID' there matches that same 'PO ID' from the other worksheet - take the value in column F - this is the number that will be averaged.

In other words - in 'Burn Rate', for a given 'PO ID' (column A), I want to average all of the values that are found in column F in 'prorder', and return that average to 'Burn Rate' (column B).

I am trying to use a macro that creates sheets and places formulae in cells but the resulting formulae are not what I coded. Here is similar

Function CreateSheet(i As Integer)

Dim CellAddress As String

Set Adjustment = Worksheets("Sheet1").Cells(6, 3 + 4 * i)

Celladdress = Adjustment.Address(ReferenceStyle:=xlR1C1)

With Selection

.Offset(22, 9).Formula = "=Sheet2!B14*Rating!" & CellAddress

.Offset(23, 9).Formula = "=Sheet2!C14*K4*Rating!" & CellAddress

.Offset(24, 9).Formula = "=Sheet2!D14*K5*Rating!" & CellAddress

End With.............

I am looking for a formula for the following:

If there is nothing is U6:W6 return blank

Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).

I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.

Project Work Id

8069000

8069000

8069000

8069000.1

[Code]....

I have a list of pricing and I want to be able to change the text of cell, say "D1", which says "Standard" to say "Premium" and all the formulas would change in accordance. The "Standard" pricing would be, Starting D2, "=C2*.3" and I want them all to go to "Premium" pricing which would be formula "=C2*.35".

What would be the best method of creating this function or formula?

I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)

Does anybody know what this formula would look like?

Example:

-----------------------

...| A | B | C | D | E | F |

-------------------------

1 |....| * |.........| 7 |...

-------------------------

2 |....................| 2 |...

-------------------------

3 |....| * |.........| 6 |...

-------------------------

4 |....................| 1 |...

-------------------------

5 |....| * |.........| 5 |...

-------------------------

6 |....................| ? |...

-------------------------

? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)

I need to create a formula that will allow me to use two sheets within the same workbook. I set up a dropdown box in my B2 cell in my first worksheet. My A4 cell is where I am trying to create my formula. I am not even sure how to say what I need. My drop down is filled with numbers from 1-211. I started my formula with =IF(B2=1,Sheet2!B3) this much I can get to work. It fills in the correct information. I do not know how to get it to continue. I need B2=2,Sheet2!B4.

View 12 Replies View RelatedI need a formula that can provide all possible alphabet combination in another cell .

example

COLUMN A A1 = RED

COLUMN B B1:B50 = BELOW COMBINATION / RESULT

RED

ERD

DRE

EDR

DER

RDE

Chart Building With Ranked Data. I have adapted the offset formula for my spreadsheet, but what to do if 2 values tie. I need the top 10 values to populate my chart, but if there are two values which rank joint first place, the formula only extracts the first value. Is there any way I can get round this?

View 2 Replies View RelatedI am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.

View 9 Replies View RelatedI now have everything set up to do exactly what I want except this last bit.

All I want is for the overview page to show the top three referrers for each month. At the moment, it's finding the top and bottom 'scorer' but if more than one has the same score I can only see the first alphabetically.

I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.

I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?

I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................

View 5 Replies View RelatedReferencing the spreadsheet below, I'd appreciate some help with a macro that finds the last row of data in a spreadsheet, and fills in all the data from cell C22 through H23. There are many spreadsheets like this one in my workbook. The last line of data is usually at a different line for each spreadsheet....

View 9 Replies View RelatedI understand how to find the top 5 /bottom 5 values using the large/small functions, but the question I have is: How do I get associated fields. For example I attached a sample sheet with values (Fields: Ticker,TE,Sector,Return). How do I get the Ticker,Sector that the return is for. I'd like to show all the associated fields that relate to the retun, but I can't find the solution on how to do that.

View 3 Replies View RelatedI need a formula to only show me the lines that exclude the initials. Is there a formula to auto-delete or indicate it the text is suppose to be excluded?

I tried using the IF statement but I cannot search for more than 1 exclusion. I actually have about 50 initials I have to exclude.

Example:

TextExcludeCR:1 YearCRCR: Cert equipmentBAAF: (CA) SwitchAFAS/TC: Hot callCFMT: recycle equipmentTC:CR: Cold callBA: Call userCF:Lights out

Basically, I am trying to write a program that will index match through a range and if it doesn't find the value that it is matching to, it will add the value to the bottom of the range it is indexing against. In other words:

My C column has a list of CUSIPS. In column J, I would like for the User to be able to add a list of cusips and then have the ones that don't already exist in the list of CUSIPS in column C be added at the bottom of the range. I have written a macro now that uses the index match, but for some reason it adds all of the cusips that exist in J range to column C, not just the ones that are missing. I can provide a template if necessary.

Sub Filler()

Dim Row As Variant

Dim NumberOfRows As Long

Row = 0

[Code]...

I am try do is a sumif that Excludes a column if it as 100% in there, I have managed to do the sumif (( =SUMIF(A:A,F3,E:E)+SUMIF(A:A,F3,F:F) )) but

I don’t know what to put if I want to ignore it, if it has 100% in a cell

I have attached a test sheet if someone could have a look and try point me in the right direction

It works out a percentage returned if column E / F = the full amount in column k

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

I'm having difficulty understanding this concept, more so the formula.

So to figure out the MTD for Day 1,2,3,4, how would it be?

I want each day to have a MTD

etc.

Day 1: 33,966,611.42

Day 2: 33,966,611.42

Day 3: 33,966,611.42

Day 4: 34,074,098.36

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