# Average Function Based On Criteria

Jul 29, 2006

I'm trying to find the best Macro or Formula to get this done.

Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".

## Average Based On Criteria

Aug 26, 2006

I have two columns. Column A has lots of repeated values, and column B is a list of numbers. For example:

A 1
A 3
A 4
A 2
A 6
B 1
B 1
B 2
B 1
C 3
C 3
C 2
C 4

Is there any way to output the following:

A 3.2
B 1.25
C 3

Where the second column are the averages. Any Excel function or VBA method will be fine.

## Average Based On Many Criteria

Oct 18, 2006

i have a excel spreadsheet that contains point of person. Every week a person can or can not compete. That means that there is some days that is blank wouthout points. I want to get the latest 5 points of a person and then out of that 5 i want to get the top 3 point.

## Calculating Average Based On Other Criteria?

Feb 27, 2012

I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.

What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.

eg.

0.1562818960
0.1558039690
0.1553260431
0.1548481172
0.15437019 2
0.1538922643
0.1534143384
0.1529364124

[code].....

In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)

## Average Time Based On Criteria?

Feb 29, 2012

I am trying to work out average for a particular area based on a criteria.

eg:
E F
Area time
A 01:00
B 02:15
A 01:15
C 01:16
B 01:30
C 03:15

In Cell

E100 - 103 I'm trying to work up the average of each area and using the following formula.

=AVERAGE(IF(\$E\$4:\$E\$61="A",F4:F61,""))

I keep getting an the following error #value!.

i changed the average to median and still get the error.

## Average Based On Criteria :: Within Each Month

Jul 29, 2008

I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month.

So column A = age
and column B = cancel date

Let's so for January 2006 I'd like to find the average age.

Logically this is how I see it...

Average Column A (If column B >= 1/1/2006 and < 2/1/2006)

## Average Column Based On Criteria Of Another

Nov 30, 2007

I am trying to get the average of two columns
column A and Column B
Column A has text (survey type) i.e. Move In, Mid Year, Year End
Column B has the scores given on that survey type i.e. 70
I am trying to get the average of of all the Move In cells from column A
for each survey type so that I get average of all the scores in column B.

## Finding Average Based On Certain Criteria Of Another Column

Apr 24, 2014

I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.

10
32.4

11
38.6

12.5
23

[Code] .........

## Build Average Formula Based On Changing Criteria

Oct 16, 2011

Objective: to create an average variance among exact match RevCode

Example: all RevCodes with "666" should be used to make an average variance statistic

Situation: All RevCode's are not in similar group counts and this creates incorrect averages with a standard formula.

Set of Data

RevCode Observed Expected Variance RevCodeMean
666 200 220 -9.091%
666 205 220 -6.818%
666 207 220 -5.909%
777 500 355 40.845%
767 505 505 0.000%
668 100 105 -4.762%
668 105 105 0.000%

Formula that generates average variance between exact match revenue codes:

=(SUM(B2:B4)-SUM(C2:C4))/SUM(C2:C4)

The solutions can be in VBA or within the spreadsheet either way.

## Average Of Numbers From List Based On Multiple IF-OR Criteria

Feb 11, 2014

Here is my formula that does not return accurate results:

=IFERROR(SUM(SUMIFS(Tenure!\$H:\$H,Tenure!\$E:\$E,{"=1100","=1090"},Tenure!\$C:\$C,{"=12","=14","=13"},Tenure!\$G:\$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!\$E:\$E,{"=1100","=1090"},Tenure!\$C:\$C,{"=12","=14","=13"},Tenure!\$G:\$G,"=1/1/2013",""}))),0)

I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.

I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.

## Average Based On Criteria :: Count Daily Usage

Aug 18, 2006

I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.

## Correct Syntax For Function To Find Average Based On Background Color Of Cells In Range

May 20, 2014

I'm working on a workbook that will track staffing patterns.

The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".

On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] ........

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] .......

I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.

[Code] ........

How might I use the following functions to find the average of the source fields instead of the SUM?

## Function To Return Value Based On Two Criteria?

Aug 10, 2014

I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.

In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.

Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "\$240.00".

I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.

## Excel Sort / Filter Function Based On Set Criteria?

Sep 9, 2012

I have following data to sort/filter

Sector
Flt no
origin

[Code]...

Is this possible with excel functions?

## Locate Date Function/Formula Based On Criteria

Jul 7, 2006

I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference

## Formula/Function To Return Multiple Values Based On Criteria

Aug 25, 2006

I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?

## Average From One Column Based Upon Criteria From An Adjacent Column

Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

## Excel 2010 :: Highlighting Rows In VBA Based On Multiple Criteria And Sum Function

Apr 25, 2014

I am trying to write a macro in VBA excel 2010 that compares 2 sheets.

The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2

AND

on that same row if column 6 on sheet 1 = column 3 on sheet 2

highlight green

** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...

Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID

475-T
03/03/2014
Connect
\$562.95
\$19.95
\$543.00
6G000001450835

[Code] .......

Results >

Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID

475-T
03/03/2014
Connect
\$562.95
\$19.95
\$543.00
6G000001450835

[Code] ..........

## Dcount Function/Formula: Count Based On Two Different Criteria In Adjacent Columns

Jun 1, 2007

I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.

Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this

jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET

and I want to have a count of how many RET's there are for Initials JP ect

## VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1

Jan 8, 2009

I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

## Worksheet.function Average Returning Error "Unable To Get The Average Property Of The WorksheetFunction Class"

Jan 23, 2007

TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)

The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.

## AVERAGE Function Embedded Within An IF Function

Feb 18, 2010

TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations
1- Below expectations
2- Met expectations
3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221

## Average With Criteria From Other Rows?

Apr 30, 2014

I worked on a countifs function, but i needed a average of column N.

the countif formula is

A2 = current month

=COUNTIFS(
'Location'!J3:J999,">="&\$A2,
'Location!J3:J999,"<="&EOMONTH(\$A2,0),
'Location'!M3:M999,"Name")

With the exceptions from above i need to get a average from column N.

## Finding Average With 2 Criteria.

Mar 10, 2009

I have 2 rows of data
1. title
2. value

eg

Row 1: Title1, Title2, Title1, Title4, Title2, Title1
Row 2: 2 , 3 , 0 , 1 , 2 , 6

In the above I am looking for the average of Title1, where the value is greater than 0. There are 3 occurances of Title1, but only 2 have values. The answer i am after is Sum is 8, average is 4 (ie 8/2 and not 8/3)

## Weighted Average With Criteria?

Dec 5, 2012

Ceate a formula for weighted averages according to criteria?

I want to know what was the average price I bought a product at on today's date. The average price needs to be weighted against the quantity of product sold.

## Average With Criteria From Another Column

Oct 10, 2006

I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)

## Average Over More Sheets With Criteria

Nov 5, 2009

I would like is the average of numbers that are on certain sheets with a certain criteria. I have an excel example that might explain more.

## Find Average With Criteria

Aug 4, 2009

In column A is the staff name, column B is the start date, column C is the end date and column D is the difference.

I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.

## Criteria Weighted Average

Jan 5, 2007

I have trouble finding the right formula for a weighted average price. The formula which i used in cell B2 of the attached file doesn't exclude irrelevant numbers. In the formula "= sumproduct(a5:a21;b5:b21)/sum(b5:b21)", i like to exclude cells B8, B9, B15, B19, B20 because the list prices are missing. How can i exclude these irrelevancies in the formula above?

## Multiple Criteria Average

Sep 19, 2007

I have a large set of data. I need to be able to search this data routinely using an origin and destination zip code and sum the search results. Is this possible in Excel?