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

May 5, 2014

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

Rolling Average That Excludes Zeros

Dec 7, 2006

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

Average Function That Excludes Zeros

May 10, 2008

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.

Data Validation - How To Create Dropdown List That Excludes Duplicate Data

Sep 26, 2013

I'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?

Average Top Mid And Bottom

Sep 15, 2009

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

Array To Calculate Bottom Average?

Jun 10, 2014

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?

Create A Formula To Average Survey Results Per Instructor?

May 28, 2014

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

Formula For Getting Average Of Values Without Counting Zero Values In Range?

Dec 23, 2011

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

Formula Average The Values

Apr 4, 2007

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.

Formula- Creates Random Number

Apr 2, 2007

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?

Formula - Average Values That Match Criteria

Jan 3, 2008

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

Use A Macro That Creates Sheets And Places Formula In Cells

Jun 20, 2006

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

IF Formula - Get Average Of Only Values In That Range Not Including Blanks

Nov 21, 2011

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

Formula That Creates Unique Number For Repeating List Numbers?

Jun 26, 2013

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

Changing Text In One Cell Creates Formula Change In Column Below?

Apr 1, 2014

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?

Formula: Average Value Of Cells Left Of Highest N Values In Above Column Range

Sep 24, 2006

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)

Create Formula With Multiple Values

Feb 26, 2010

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.

Formula To Create Combination Of Text Values

May 22, 2014

I 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 Top Or Bottom X Values

Dec 1, 2006

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?

Create A Formula Which Sums The Values Associated With Several Defined Names

Aug 6, 2007

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

Find Top/bottom 3 Values From A Range

Aug 13, 2008

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

Rank Values Into Top/Bottom X Scale

May 21, 2008

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?

Create A Formula To Add Values Assigned Cumulative Dates In A Month

May 12, 2009

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

Macro To SummIf Values At Bottom Of Page

Jan 5, 2007

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

Find The Top 5 /bottom 5 Values Using The Large/small Functions

May 28, 2009

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

IF Statement Contains Or Excludes

Nov 3, 2009

I 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

Adding Values To Bottom Of Row In Range In Excel After Index Match

Jul 12, 2013

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

Sumif That Excludes A Column If It As 100% In There

Feb 25, 2009

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

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

How To Create Month To Date Average

Aug 20, 2008

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