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 Replies
ADVERTISEMENT
Rolling Average That Excludes Zeros
Dec 7, 2006I'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, 2008I 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 RelatedData Validation - How To Create Dropdown List That Excludes Duplicate Data
Sep 26, 2013I'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, 2009I 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, 2014I 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 RelatedCreate A Formula To Average Survey Results Per Instructor?
May 28, 2014I 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, 2011Is 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, 2007I 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, 2007i 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 RelatedFormula - Average Values That Match Criteria
Jan 3, 2008The 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, 2006I 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.............
IF Formula - Get Average Of Only Values In That Range Not Including Blanks
Nov 21, 2011I 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, 2013I 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, 2014I 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, 2006I 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, 2010I 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 RelatedFormula To Create Combination Of Text Values
May 22, 2014I 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, 2006Chart 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 RelatedCreate A Formula Which Sums The Values Associated With Several Defined Names
Aug 6, 2007I 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 RelatedFind Top/bottom 3 Values From A Range
Aug 13, 2008I 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, 2008I 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, 2009I 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 RelatedMacro To SummIf Values At Bottom Of Page
Jan 5, 2007Referencing 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 RelatedFind The Top 5 /bottom 5 Values Using The Large/small Functions
May 28, 2009I 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 RelatedIF Statement Contains Or Excludes
Nov 3, 2009I 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, 2013Basically, 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, 2009I 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, 2013I'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, 2008I'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