=AVERAGE(INDEX(E5:IV5,LARGE(IF(E5:IV5<>"",COLUMN(E5:IV5)-COLUMN(E5)+1),5)):INDEX(E5:IV5,MATCH(9.99999999999999E+307,E5:IV5)))
I have this formula where it averages the last five numbers in a collumn, but I want to average the last 5 numbers less the max and min number.
I have a table in a sheet where the rows of data are filled in automatically from other sheets. Each row represents a different week. The data, (lifted from from the relevant worksheet for each row) represents the no. of calls in each week.
Now, I've set up an average to be calculated from the data - it'll do this for every row as long as there is data present. (If the row's corresponding worksheet doesn't have any data the result will be "NA()" - not included in the calculation.)
However, I don't want the average to include the no. in the "most recent" row, as this is "live" data and until the week is over does not represent the data for a full week and therefore skews the average.
So I want the average to exclude the data in the most recent row with a numerical value in it and not a "NA()"
ie - column could be ......
I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.
Here is what I invision(cells are for example purposes)
=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))
So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.
I would also do this with a minimum outlier as well, but i can set that up if this first one is possible.
How do i stop changeCell from going into the minus?
Sub goalSeekSample() Dim seekValue As Double Dim changeCell As Range Inflation = Range("O7").Value seekValue = InputBox("What's the value to seek?") For Each changeCell In Range("N18:N32").Cells changeCell.GoalSeek Goal:=seekValue, _ ChangingCell:=changeCell.Offset(0, -4) seekValue = seekValue + (seekValue * Inflation) Next changeCellEnd Sub
If I wanted to get a cell, say A1, to multiply a value, for example 7, by the results in another cell, say B1, but NOT multiply if that cell's value is a minus number, is there a way of getting Excel to do that?
Normally I'd just type =SUM(A1*B1)*7, but how can that be reconfigured to ignore minus numbers? e.g. if B1 contained -5, ?
i want to know how to prefix a minus sign (-) before numbers in cells in a large range.i m working on a large sheet containing the Numbers with Cr and Dr as suffixes just like 445Dr ... 3331Cr and so..on... in the worksheet
i want to know the method of deleting the suffixes and prefixing - sign infront of numbers having Cr as the suffix.
Numbers with Dr as suffix denote positive numbers
and numbers with Cr suffix denote negative numbers. i want to prefix the -minus sign in front of numbers having Cr in the end.
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.
I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
Field 1 input: Beginning Date (e.g 7/17/09)
Field 2 input: End Date (e.g. 7/23/09)
Need: Average costs on and between these given dates
From table with this information:
Date Market two
7/16/2009 80
7/17/2009 80
7/20/2009 82
7/21/2009 84
7/22/2009 82
7/23/2009 82
7/24/2009 82
7/27/2009 82
I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.
If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)
How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?
I m making a worksheet for our shooting results. I need the average of the
top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.
The sheet looks something like this, numbers are all in one row:
7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32
I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.
I have cells O1 to O300 numbers in random sequences of no more than 10
I need to average the 5 highest numbers in each sequence eg:
O P
1 2 16 (result)
2 20
3 4
4 6
5 14
6 12
7 8
8 10
9 16
10 18
11 blank cell
12 15 9 (result)
13 3
14 6
15 12
16 9
17 blank cell
18 blank cell
19 4 23 (result)
20 16
21 20
22 40
23 8
24 11
25 28
26 blank cell
down to 300 rows, the sequence of numbers can be from 1 to 10 but never
more than 10
I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5})),"
") but that works only for 10 numbers and not for less
Hello, I have a column with 100 numbers in it. I want to be able to choose a number N and then calculate the average, minimum and maximum of the first N numbers in the list.
I have played around with countif and sumproduct but can't figure it. I don't know how index works, and wonder if that is the key.
My worksheet contains values in D2:AA150 Some cells are blank Is the a formula that will average the lowest 10 values ineach row?
View 6 Replies View RelatedI want to pick the top five numbers out of a random list of numbers and get its average. What is the best way to go about doing this?
View 2 Replies View RelatedI am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.
I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.
Column B values are the average of the most recent five values in column A.
The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?
Note: I just want to be able to pick up the most recent average value – the
method whereby it is done is of no consequence. However I cannot think
of an alternative. Example:.....
I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0.
Is there a macro that get the average of the best 3 out of 5 in a range of numbers....if the range doesn't have 5 then adjust with what it has.
ex..
a....65 66 54 33 72
b....57 57 42 70
c....55 45 22 65 80
d....78 34
e....66 66 54 23 56
f....55 66 77
I need to figure out a way to get an average for the numbers in column A, but ONLY those numbers in column A that have the number "1" in column B. example:
Column A Column B
3 5
6 1
4 1
The average would be "5", because the numbers 6 and 4 have "1" next to them in column B.
I have to columns of data
Date and number
4/1/2012861,808
11/1/2011594,930
11/1/20102,740,320
11/1/20121,041,948
10/1/20111,726,000
10/1/20091,628,000
10/1/20082,059,929
10/1/2011 4,002,000
9/1/20062,979,602
9/1/20073,774,000
9/1/2011NA#
I want to take the average of the numbers between if they fall between dates of 2006 and 2007.
I can not do it with averageifs because I have that NA# in the second column. I dont know what to do.
I have something that works but only if it does not contain NA#, below is the example of what works:
=AVERAGEIFS($D$2:$D$131,$B$2:$B$131,""&H2)
I am trying to find a way to average all the numbers placed in one cell with the answer in another.
So in cell A1
1, 2, 3, 2 or 1-2-3-2
In cell A2 would be 2. But the amount of numbers in A1 will vary from time to time.
I have done is created an Officer Evaluation Form in Word for my Police Chief and the Scores for the different observations are: N/A, 1, 2, 3, 4 and 5. If for example there are 4 observations and one of the observations is "N/A" for not applicable or not observed and the rest are all 5's I want the formula to ignore the field(s) with the N/A and still come up with an average of 5. The way I have it set up now which is: =AVERAGE(KOW1,KOW2,KOW3,KOW4) it comes up with an average of 3 when I put a N/A in field KOW1 and all 5's in KOW2-KOW4.
View 3 Replies View RelatedBasic Excel-2010 functions.
I have given a table with some decimal values like e.g 0.3658 0.358 0.485 0.7863 0.558 0.858 0.985, I want to find the sum and average of these number.
1) I have tried the autosum or auto Function =AVERAGE(A2:A7) but it yields #DIV/0! error
2) when I use =AVERAGEA(A2:A7), it yield ans=0
3) puttin =ROUND(AVERAGEA(H2:H23);3) also yields 0;
I have three columns of data, one containing numerical values. I want to average the numbers based on the other two columns of data. Attached is an example file with the desired output I'd like. What is a formula that will do this?
AverageExample.xlsx
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
ex. average A1:F1 if G1:L1 equals M1 or M2
I have one Row that houses numbers 80-90 in seperate cells (11 columns A20:
K20) - these are my Numeric Labels.
I then have several other Rows that span the same number of columns as the
Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric
Labels.
I would like to AVERAGE the Numeric Values in each Row and have a Numeric
Label (value) that corresponds to the calculated average returned as the
result.
Example: Sample Data
A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90
A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10
Based on the above data - the Expected Result is 81
Average = 6; Return Numeric Label = 81
The leading zeros shown in A21:K21 is for alignment purposes only.
I would appreciate two formulas:
1) Includes zeros to be averaged
2) Excludes zeros from being averaged
I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21),
$A21:$K21,0)) but receive the #N/A error message.
I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.
In A2:A9 are text "time" numbers which may cross midnight
1941
1852
0130
2347
0242
2326
0028
2257
Looking for formulas to place in A10:A11
which can return the average & median of the times in the same text "time" format
I'm trying to determine the average of a set of numbers, based on a scale of 1 through 10. There are two columns and it would look something like this:
1 3,452
2 2,990
3 564
4
etc.
I want to determine the STDEV here as well but assume I can just exchange the AVG function with that function, once I know how to tie column B (the numbers) to column A (the rank).