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 RepliesI need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx‎
Actually with the help of array formula I"m taking out Average, Min, Max value of ranges and I was omitting zero but the problem is occurring that if i have originally zero in my ranges then Average & Minimum function is not working properly.
View 8 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 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.
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 RelatedThis formula is returning 0.2578 when it should return 2.473
{=AVERAGE(('Data-Design'!E2:E6000)*('Data-Design'!Q2:Q6000=1))}
What am I not understanding?
I want the average of column E when column Q=1
code for an assignment, I have a table of values, the 5 colums are arrays, one with 20 student names and the other 4 are grades for 4 subjects, each colum being one subject.
I need code to find the average of each student and get it to display in a separate colum and averages for each subject also getting it to display in another row.
I'm working on building a workbook to track sales progress, and I'd like to perform an analysis on some of the data with regards to sales efficiency.
To simplify:
Column A = Initial Contact Date (mm/dd/yyyy)
Column B = Close Date (mm/dd/yyyy)
As an example, I would like to calculate the average number of days between the Initial Contact Date and the Close Date to calculate the average number of days in the prospecting cycle. Basically, the formula should function as AVERAGE((B1-A1)+(B2-A2)+(B3-A3)...) and so on for the entire column.
I know one solution would be to insert a new column that performs the subtraction between the two dates, and then I could AVERAGE this new column. However, I want to perform similar calculations to analyze the time period between other key milestones in the prospecting cycle - rather than have a lot of extra columns in my sheet, I was wondering if there's some type of array formula that will calculate what I need.
I have tried to find information on this subject, but it seems diifficult to find exactly what I am looking for. I have provided a test example of the operation that I would like to acheive. The first example (Method 1) works well enough but would require defining more detailed functions beyond the scope of average. This would probably be ok, but more importantly, I would like to source particular arrays in this manner for other tasks beyond averages and even standard deviation and am not quite sure how this can be done. In an attempt to describe the situation, I have provided a second example (Method 2). In this example, I am trying to take the average of the jth column array to create an array of averages. I am not sure if I am incorrectly setting up the matrix, if the functions don't perform this way in the matrix, or if this is simply not possible.
Sub test()
a = 5
b = 5
Redim X(1 To a, 1 To b) As Double
'Assumes there is a matrix of values [a x b] drawn from worksheet
For j = 1 To b
For i = 1 To a
X(i, j) = Cells(i, j)
Next
Next
'----METHOD 1---------------------------------------------
'I know this way works using average as an example, but...
Redim AverageX(1 To a, 1 To b) As Double.......................
I'm using the formula =AVERAGE(B16:L16) to give me the average.
However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.
Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.
Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400
In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
Cell: F6 is to represent "average weekly offering".
D2:D53: represents the Total Given Year To Date.
I need a Weekly Average for Offering, so is it possible to have a formula that can show the average weekly offering in cell F6?
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
View 2 Replies View RelatedI'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up.
My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report.
Then, I need the following logic as VB macro.
for row = 6
F6 = (G6*G4) + (H6*H4) + (I6*I4)+...+ ("max_col:6" * "max_col:4") / (G4+H4+I4... "max_col:4")
I have a large amount of data in 2 columns which I want to statistically average to see if a relationship exists between two variables.
Column A: Contains Iodine concentrations ranging between 0 and 22000 ppm.
Column B: Contains Nitrate (NO3) concentrations ranging between 0 and 22 %.
I have over 7500 rows of data. In theory there is an endless amount of data but it will all be kept in these two columns
What I want to do is create average values of NO3 based on set Iodine ranges.
e.g. I want to know the average NO3 grade for the cells where the Iodine concentration is between 0 and 99 ppm.
I want to be able to calculate this average based initially on 100ppm Iodine bins but dont know what clause I would need to add into the formula or if this is even possible.
I would also like to be able to edit the bin size to see if I can discern a better relationship between the two using different ranges. As such I can create the bins in a separate column but again I don't know the best way to do this to make it work well in a formula.
I have data regarding the percentage of QC for the team.
I need to find out the individual's average QC scores from the list already available.
The columns that is in the primary sheet goes like this: Date/Name/Comments/QC percentage
I am trying to average a data set where some rows at the moment are blank but will update at a later date, I am getting a div/0 error, the formula I am trying to use is
=IF(C23:C30="","",AVERAGE(C23:C30))
I thought this would look at the data and see if its blank leave it blank but if there are numbers there it would give me an average.
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 am working on formula to return an average of data.
Currently it is matching a text criteria.
Thus if (the text in) column a = (the text in) column b, (return the average of) column c.
The formula that I am using is =IF(A:A,B:B,AVERAGE(P:P))
This is returning - #value!
Now is this a formatting problem in column P? Or is the formula I am using incorrect?
I know that the text criteria (col A & B) matches.
I am trying to do an average of quartely results (2008 results) and because certain quarters can have no data present then my quarter shows 0%. But if i do a simple average of all Qs then the 0% affects my overall year to date. I need to keep the 0% because it may be a legit value. So i've devised a way to is if we actually have something in the cell (using isblank). So i test with isblank and if something is present then i use the Quartely value. It could be 50% or it can be 0%.
I would like to incorporate the snapshot but i can't because i do not have access to uploading a picture. If any of you wish i could email the pictures. When i test out my formula in different cells its works but when i combine them all together i get #VALUE! error.
In the example the error comes from the Q3 which test out and should give "" but when its added to the formula i gert #VALUE!. Shouldn't the average function disregard "" cells?
My formula is:
=average((if(and(isblank(F33),isblank(J33),isblank(N33))," ",R33)),(if(and(isblank(T33),isblank(X33),isblank(AB33)),"",AF33)),(if(and(isblank(AH33),isblank(AL33),isblank(AP33)),"",AT33)),(if(and(isblank(AV33),isblank(AZ33),isblank(BD33)),"",BH33)))
Maybe my logic is flawed or there is a better way to do it. Either way i would like your opinion on it.
I need to count the daily average of a task to a week ending number.
I need to see the current average after each day during the week. Example – Mon = 2, Tues = 4 AVERAGE is 3 – Wed = 2 AVERAGE IS NOW 2.6…and so on averaging out after each day is added.