I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.
[=AVERAGE(IF(($A$3:$A$275=$P6)*($B$2:$M$2=Q$4),$B$3:$M$275))]
I have attached a sample workbook that includes the broken formula.
Average Formula Error.xlsx
How could I add ">0" to the below formula array?
{=IFERROR(AVERAGE(IF(Reports!$A$1:$A$4000=A5,Reports!$H$1:$H$4000/86400)),"-")}
Reports!$A$1:$A4000 = column A lists multiple agent's names
A5 = a specific agent's name
Reports!$H$1:$H$4000 = column H contains a number which has been converted from time to a number
86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)
The average I'm looking for will give me an average of all times which are greater than zero.
I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.
I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).
Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.
Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.
The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?
Location
Parameter
2008
2009
2010
2011
2012
2013
LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33
LocA
beta
4
9
9
8
2
9
LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65
LocB
beta
8
7
6
7
2
9
LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92
I 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
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
View 9 Replies View RelatedI am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.
View 4 Replies View RelatedI can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
i've got a worksheet with 3 columns each declared, called date, place and avspeed
I'm trying to average the avspeed if it falls under certain criteria within the other 2 columns.
The dates are in day format and basically I require the average over the week
If tried using
=AVERAGE(IF(WEEKNUM(date)=45,IF(local=I4,avspeed)))
also declaring it as an array formula but both
also tried the following by summing and then counting the data and dividing one by the other, but so far neither are working
I've been trying to sum up invoices for specific contract lines while also taking into account if the invoice was deleted. There is a deleted column in which a "Y" is entered into the corresponding row meaning this invoice should no longer be accounted for.
I've been able to sum up the invoices per contract item just fine but I can't seem to get excel to account for the deleted column. I've attached a sample workbook which has dynamic named ranges already defined, the current array formula I'm using to sum up invoices and a pivot table that shows what the correct invoice amounts should be.
I would like to count the number of values in column B IF the value in Column A is equal to a value from a range of cells.
I would like the formula to be in cell E7. And the range of cells containing the values is in column D.
Here is my workbook Book1.xlsx
I have an array of data that i need to find multiple data points in: ...
View 11 Replies View Related=COUNTIF(A$2:T$1001,V2 & W2 & X2 & Y2)
I want to copy and paste this formula down column Z.
I want to count or add 1, only when a row of 20 cells (A to T) contains all 4 criteria in V W X Y.
I;'m trying to create an ARRAy formula to SUM with; the formula has to match 3 different criteria's. I'm trying this with no success, here is the last one I tried.
=sum(ifs(DATA!G:G,Sheet1!A27,DATA!B:B,if(Sheet1!B1,DATA!I:I,">=0")
with a multiple criteria index match array!
I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!
I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)
I am running Excel 2003. I have two columns of numbers (column A and column B)
column A has 1s and 0s. column B has latencies (ie. 100-500ms). When column A has a 0, i would like to know the latency of the following row in column B. from this i would like to average all of these latencies that are found. for example.
column A has 1,1,0,1,1.
column B has 100, 200, 300, 400, 500.
so since A3 is 0, then i want the latency of the following row in column B which is B4 or 400. and then average these findings.
I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".
VB:
Sub test()
Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL")
Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT")
Set r = ws2.Range("A1:du4783")
[Code] .....
I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:
Car
Region
Own
Use
Color
Honda
North
Yes
I use it to go for work
Green
[Code] .....
I want to be able to do following (2 separate tasks):
Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):
Use
I use it to go for work
Family trips
Weekend fun
2nd car
Work
Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):
Car
Region
Color
Honda
North
Green
White
I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.
Need to expand the crteria on this array?
{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}
I need to add
{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}
I'm working with a large amount of data (A21:BZ1503) and I'm trying to identify unique situations where any pre-defined combination of multiple columns in one row is flagged by producing a pre-defined value. For example:
I have my pre-defined criteria in worksheet 'X' hidden in my workbook -- note that there are many blank cells.
Worksheet 'X'ABCDE1Dept.CourseInst.Adj.2AGSM1.23MATH101Professor1.44ENGL1051.65ENGLProfessor1.86ETST1002
On worksheet 'A' the various users enter data -- each row is a unique group with data entered into the columns -- again note a cell can be blank:
Worksheet 'A'ABCDE1Dept.CourseInst.Adj.2AGSM110Professor3ETST2204ENGL108Professor5ENGL105Lecturer6MATH101Lecturer..............................
I have sheet full of data containing results of multiple tests on various equipment.The sheet contains many columns of data but below are the specifc criteria i want to use to extract the data. As maintenance is carried out regularly the list is always growing. I want to create a dashboard summary of the "Machines" which i will colour using condition formatting. I will list the machines in the columns and would like the rows below each machine to be populated with the results.
Column 1 Lists the various factories
Column 2 Lists the Machine
Column 3 Lists the Part
Column 4 Lists the result.
Results can either be "ok", "warning", "Alert" only
I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.
The formula I am currently using is:
=INDEX(Trees!B$2:B$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A$2:A$75,(Trees!B$2:B$75-B2))),0),0))
Sheet 1 (Named: Main)
Number
Date
Formula
1
15/06/2012
[Code] ....
Sheet 2 (Named: Trees)
Number
Date
1
05/06/2012
[Code] .....
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
I'm trying to do some averages for a year to date "cover" sheet. Worksheet 2 is Jan, Worksheet 3 is Feb, etc..through December. Worksheet 1 is the year to date averages. My data is a formula on each worksheet in cell B22, I thought I had the function written correctly however Excel won't take it:
=AVERAGE(IF(January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22, November!B22,December!B220, January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22 ,December!B22,""))
There are blanks,of course, until each month is filled in. Could it be because the number that is in B22 on each worksheet is the result of a formula instead of typed number
I need some help with a formula. I've tried daverage, cant seem to get it to work. below is an example of the spreadsheet.
A B C D E
Date Name Project Score Pass?
5/1 Joe Test 55% No
5/1 Jan Quiz 88% Yes
5/4 Jon Test 100% Yes
5/9 Gary Test 75% Yes
5/11 Joe Quiz 90% Yes
5/18 Mary Test 45% No
Ok, I need a formula that I can execute from a different worksheet than the data is on. I need the formula to give me the average of the scores in column D for the criteria in Column C "Test". I tried daverage=(A1:E7, "Project", C1:C2) I cant get it to work. Do you guys know any other way to do this?
In the attached file on the Final Lists tab I have an Array formula in each column.
My problem is that I need to extend this to cover approx 500 columns. Is there any way that I can cut'n'paste or drag the formula across all the 500 columns, have my references increase with each new column, but also retain my $ references (ie my formula format)?
As it is, I am facing having to either type or correct each and every one of the 500 columns. As you can see, I am a little bit of a newbie when it comes to the intricacies of excel.
I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.
The first calculation is simply the average quiz grade for all students, for all quizzes.
The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.
I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.
Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.
See the attached workbook for some sample data and the expected results.
Sample Workbook.xlsx
I am trying to compile a spreadsheet for my job that will find an average of all the break times and meal times that all the employees take. I have the employee list and their respective timeclock punches for the week, so I took that and found their meal and break times for each individual day, but I'm having trouble when it comes to consolidating and averaging the data. The first column lists the employees, but their names are repeated depending on how many times they punched the clock that week, and since not every punch was a meal or break, my time columns contain a lot of zeroes that aren't needed. I need a way to just average each employees' meal and break times.
View 9 Replies View RelatedI have a column with times displayed in this format "0 day 03 hour 32min"
Is there a formula that allows me to average a column of these times and retain the same format?
I am currently using the array formula to find out the most recent date a business as referred a deal. I have been using the below array formula to figure this out...
{=MAX(IF((Opps!A:A=[@Company])*(Opps!B:B=[@[Full Name]]),Opps!G:G,0))}
However, we have now have two types of deals. Opps and Suspects. SO now i need to figure out the most recent date from Opps!G:G and Suspects!G:G... regardless of what the deal type is. Hope that makes sense.
Basically i need to have a formula along the same lines as above but MAX date from if conditions from Opps OR conditions from Suspects
I wanted to do a multiple return vlookup so I used an Index formula. I have account names and am trying to vlookup contacts associated with those accounts (4 max). So I have a list of Accounts all with 3 blank rows in between them.
How do I loop an array formula? So far I have the code..
[Code] .....
This finds the 4 contacts associated with the first account in C5 which I named the cell "myVar". I now want it to find the contacts associated with the accounts in C9, C13, C17 and so on until the first empty cell.