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
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
how to create a formula that creates an average but excludes the top 10% and bottom 10% values?
View 5 Replies View RelatedHow do I calculate the average of an array in VBA code? I do not want to do it in the spreadsheet to save time and space.
View 9 Replies View Relatedhow to find the average times of two different sets of groups. There are the baseline times and intervention times. Both groups have three subtopics: Time Fell Asleep, Woke Up, and Duration (how many hours I slept). I am not sure how to find the average of each subtopic. Here are the times....
Baseline times:
Fell AsleepWoke UpDuration
2:30 AM10:00 AM7:30 hrs
4:30 AM12:30 PM8:00 hrs
[Code]....
I have a column of percentages, how do I get an average % rate at the end of my column?
View 7 Replies View RelatedI want to calculate the average for 6 cells across 6 different worksheets.
For instance in sheet 7 i want to calculate what the average of sheet 1 A1, Sheet 2 A1, Sheet 3 A1, Sheet 4 A1, Sheet 5 A1 and Sheet 6 A1 is. However some the cells contain blanks/zeros which are resulting in an error message. The overall figure still needs to be divided by 6 to get the average.
I need to calculate the average based on SKU patterns.
For example, if the SKU begins with "CZD", then take the average SKU that meets that pattern.
SKU
Items Sold
CZD80
45
[Code] ......
I have two columns of data. Column B is age column C is gender. I want to calculate average male age and average female age. Suggestions?
Sample
B C
57 f
53 f
47 m
40 f
42 m
Average female age is ___
Average male age is ___
I need to find the average time it takes students to take exams . I use the following formula =text(end time - start time, "h:mm"). I am able to calculate the amount of time it takes a student to take the exam. Now I need a formula to calculate the average time students take to complete a test. I have over 80 times i need to average. Whenever i try a formula I keep getting 0.
View 9 Replies View RelatedI had an Excel 2003 spreadsheet which used =average(if... array formulas.
I'm trying to do the same thing in 2007 but it's not working....I know there is an averageif function in 2007 but some of the people who'll be using the spreadsheet are still on 2003 so I need a formula that works for both.
I have Column A which is an Employees birth Year, Column B which is salary, and Column C which is a list of Years.
I need a formula to read the Year in Column C, refer to Column A finding all the rows that match that year, than refer to Column B (salaries) and find the average of the salaries.
I am using the following array formula to give the average number of days aging on overdue reports for my staff where Manager and Aging_on_Overdue are named ranges in my input data: {=AVERAGE(IF((Manager="Joe Smith")*(Aging_on_Overdue>0), Aging_on_Overdue))}
This works fine as long as Joe Smith has Overdue reports, but returns a # DIV/0 if he has none. How do I get a formula to return 0 if he has no overdue reports? I need this because I have to do a weighted average of the group and my SUMPRODUCT formula returns a #DIV/0 for the group if any member of the group has no overdues.
Trying to average multiple cells that are not consecutive and contain zeros that i want to disregard. I have tried to do an array formula but the cells must be consecutive so I am at a loss. This is what i tried. =AVERAGE(IF(A2,A8,A12<>0,A2,A8,A12,””))
View 9 Replies View RelatedI have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.
I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.
Trying to calculate the lost revenue value for when a piece of machinary is offline for a period of time based on the hrly price during that particular period. The machines come on/off line at various times during the year so i am trying to make this a bit dynamic so i only need to add new info and not have to change the formula
Column F - Off date of machine
Column G - Restart date of machine
Column H - Hrly time
Column I - Hrly price
I thought this formula (an array) would work for me
=average(if((Column H>=Column F) * (Column H<= Column G), Column I)) but it only calculates that average price for the first period and not new average prices for other periods.
what adjustments I can make to my formula or is there a better formula to use?
I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.
Assuming that B1=0 and C1=1 (in the # of days row)
# of Days 0123456789101112131415Total Files
File Count011712113203000000049
I am trying to determine the average reduction amount of appraised property values. I have two columns in my spreadsheet. Column H has appraised value of property. Column I has the accepted value of the property. Sometimes the accepted value is the same as the appraised value, sometimes it is reduced, and sometimes it is rejected.
I want to be able to find the average reduction amount when the accepted value is less than the appraised value and is not rejected.
Column H always shows a numerical value (i.e. $250,000), but Column I may have a numerical value or show "rejected".
I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "date-time" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.
View 3 Replies View RelatedI'm using Excel 2003. I have about 190 rows that I use on any given day to enter start times & end times. I calculate the difference in Column E. Is there a formula that will calculate the average time as I enter them in the rows? Some days may have only 100 entries, other days may have as many as 190. I don't want to keep adjusting the average formula for column E.
View 2 Replies View RelatedI'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:
{=AVERAGE(IF($B$5:$B$53=B$2,OFFSET($C$5,(COUNT(C5:$C$53)-1),0,12,1)))}
I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/b]of the Offset function was setup simply to get the 12 months which preceeded the reference date.
I'd like to calculate an average over a variable range. In col.A there are grades from A4:A21. In col.C there are the values for the start row of the range and in col.D the values for the end row of the range.
For instance the value in C4=4 and D4=9. In cell F4 I want the average calculated over A4:A9. Value in C5=10 and D5=15. In cell F5 I want the average calculated over A10:A15.
I have a row of data starting in cell E4 that could, theoretically, go to the far right end of the spreadsheet. I need to enter a formula in cell D4 that calculates the average of every other cell in this row, starting with E4, that is E4,G4,I4,K4...
View 9 Replies View RelatedI am making a summery table of lots of soil contaminant data.
In my summery table, for each determinand I want the average value automatically taken from data from a number of other tables within this same spreadsheet.
For example, for pH- I would average the pH value found in each table, from cell D6, G24 and AB9.
The problem:
Out of the three tables where I take each pH value from, on occasions only one will have a value- the other two will be "0".
How do I ensure that, when averages are taken, it will always ignore '0' values?
How do you calculate the average of the last 20 values in a dynamic list? The list is in column A and updates every day by adding a new value in column A.
View 3 Replies View RelatedI have few data that I need to calculate average:
A B C
9 3 6
5 2 4
3 6 8
I need to calculate the average of column A/B/C --> Average (A2:C2) and Average (A3:C3) and Average (A4:C4). The end result should be pasted to another workbook from A1:A3
I would like to use VBA since I have written some VBA to other parts of the report....
I have the following data, where a negative number represents a sell of shares and a positive number a purchase and column C shows at what price the transaction happened.
A
B
C
D
E
F
1
Ticker
Quantity
Price
Ticker
Average Price
[Code] .......
And I've been asked to calculate the average price per ticker. For now it doesn't matter whether it's based on a first in first out principle or first in last out principle.
In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
I am trying to calculate a weighted average and have used a sum product to do so. My problem is that the values decrease over time and I have done this in cells d13 and e13 and this seems to work ok. My problem is that I have many months so I don't want to use the formula this way as it will be very long. I tried to use sum and offset to try and effectively sum each row but this doesnt work.
Weighted Average
B
C
D
E
F
G
3
months
4
rate
original value
1
2
3
4
[Code] ...
My sheet looks like this - example:
ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03
I need to calculate the average days between the dates for each ID? How do I do this?
