# Averaging Multiple Columns / Rows If Two Criteria Are Met

Apr 30, 2014
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

View 3 Replies
ADVERTISEMENT
May 29, 2007

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 Related
May 19, 2014

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

View 4 Replies
View Related
Dec 18, 2006

1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.

2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L

3. In Column D, I want to specify 3 criterias namely A, B and C

4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo

5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet

View 9 Replies
View Related
Dec 18, 2013

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

View 4 Replies
View Related
Jan 10, 2014

I've not used SUMPRODUCT previously and can't understand how to get results for the attached.

I've tried SUMIFS but it doesn't work because I'm looking down columns and across rows, I'm assuming.

I've attached a summary of what I'm trying to achieve. I want to sum all costs with an R,P,I,G, etc. in column C for December '13 (E3) in the top table.

The second table is actually in a different sheet but is the source of the data I need added.

Sumproduct P&L.xlsx

View 11 Replies
View Related
Oct 20, 2006

I posted this on Mr Excel, but did not get an answer. I hope someone here can help.

I have a rather large financial worksheet that I am trying to extract some specific data from.

The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.

Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......

With me so far?

What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.

In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.

Just in case that is not totaly clear----

criteria to meet

Column G = "Project Code"

Column J = "Exp"

Row 2 > this month

Row 3 = Forecast.......................

View 9 Replies
View Related
Jul 10, 2013

create a formula,for multiple criteria with multiple results in rows.

[URL]

View 1 Replies
View Related
Sep 26, 2008

I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with:

Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00

I want it to look like the following:

Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00

I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.

View 11 Replies
View Related
Aug 30, 2007

I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.

Please see the attached, it is more clear I hope. The two yellow rows would be deleted.

The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"

View 8 Replies
View Related
Apr 4, 2008

I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.

The objective is to:

To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.

TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.

View 3 Replies
View Related
Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.

EG

Store 1 1111 16:40

2222 13:00

Store 2 1111 15:05

3333 16:50

View 9 Replies
View Related
Sep 22, 2006

I have 6 columns of data laid out as follows,

columnA columnB columnC columnD columnE columnF

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting

= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))

View 9 Replies
View Related
Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

View 1 Replies
View Related
Feb 16, 2010

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.

View 3 Replies
View Related
Jan 15, 2010

I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............

View 3 Replies
View Related
May 16, 2009

I'm making up a list of average scores across a season. I have an average column and I need to be able to deduct 10% from this average, then have excel ignore any scores below this new number and make up a new average (kinda like a handicap score).

I have columns for average (BD), 10% of this (BE) "ignore any score lower than"(BF) but Im not sure how to get excel to add up only the scores below the number in BF and make a new average column of only these in BG -

Last column would be to give each person a classification based on cutoffs (for instance 1-10=C Class, 11-20=B Class, 21-30=A Class etc etc). Is there a way to make "if BF=xxxx, make BH A, B, C etc?

View 9 Replies
View Related
Feb 24, 2014

Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.

In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4

89PENS ABC STATIONERY CO LLC [code]....

The attached example file may be a better alternative to view this problem.

View 2 Replies
View Related
Jan 23, 2012

So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...

1.) Take their associated % (Columns C, E, G)

2.) Multiply the % by the Revenue (Column H)

3.) Sum up the total for each of the names (Locatedin Columns B, D, F)

4.) Only sum up the ones that occur in Month 1 of the Year 2012

I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..

Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00

View 3 Replies
View Related
Aug 10, 2009

I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.

I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.

I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.

View 2 Replies
View Related
Sep 29, 2011

Need to create a macro?

Delete rows where multiple column meets multiple criteria.

detail:

delete rows where

Column H is less than 10000

AND

Column C is empty(blank)

Those 2 criteria have to occur at the same time..

View 1 Replies
View Related
Jun 10, 2013

I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.

I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.

I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.

[URL]

View 2 Replies
View Related
Jun 26, 2013

I would like to find the average of a column of data where the rows of data changes where my starting cell is always B53 but the end row of data can change after each simulation. The average function to be displayed at the bottom of the last row of data.

View 4 Replies
View Related
Mar 4, 2014

I am trying to improve how I write my formula. In this case I have replaced this horribly long formula:

=SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,

"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3006,ChargeOff,"OK")

+SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,

"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3031,ChargeOff,"OK")

[Code] ....

with this nice shortened version:

{=SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},

TransactionCode,{3006;3035;3031},ChargeOffCredits,{"OK"},CAAPartner,A10,ChargeOffCredits,"OK"))}

I have pieced this together using information on various forums and it appears to work, but I have a question:

What is the difference between using a comma and semi-colon in this new formula?

View 3 Replies
View Related
Nov 3, 2008

Sumproduct help with criteria in Columns and Rows

View 9 Replies
View Related
Jun 24, 2009

i have a unique problem. I have a spreadsheet with 2 worksheets.

Worksheet A

Dlr# Dlr Name Zip Code

X123 Dane's 50266

X123 Dane's 50266

X123 Dane's 53135

X983 Andy's 50254

X983 Andy's 50254

X348 Ryan's 45678

So, this spreadsheet has some duplicate rows and some unique ones. All are needed as duplicate zips mean that the dealer is sending out multiple mailings to the same zip code.

My task is to transfer these into Worksheet B so that duplicate zips go in different columns but unique zips don't. So this is what Worksheet B will look like:

Worksheet B

Dlr# Dlr Name Drop 1 Drop 2 Drop 3

X123 Dane's 50266 50266

X123 Dane's 53135

X983 Andy's 50254 50254

X348 Ryan's 45678

My spreadsheet has over 500 dealers with over 1500 rows of data in it, so if there is an automated way to create spreadsheet B.

View 9 Replies
View Related
Jul 14, 2004

I have a large spreadsheet that is 65000 rows and colomns A thru W.

I need a macro to delete rows that if Date and Account and Type are

equal and the net of Quantity is zero delete those rows.

Col A = Date

Col C = Account

Col D = Type

Col J = Quantity

What the macro would do is if an account had 250 rows of activity on

say 7/7/2004 (Date) and same type of account and the net of all

Quantity is equal to zero delete those rows.

View 9 Replies
View Related
Sep 14, 2009

I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.

Tried to do it myself but succeded to do my head in only.

View 9 Replies
View Related
Dec 22, 2013

I would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.

Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.

Capture.PNG

View 2 Replies
View Related
Dec 5, 2008

I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.

In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?

View 2 Replies
View Related