Conditional Average-three Columns In My Worksheet

Jul 19, 2007

I have three columns in my worksheet.

Column 1 reports the name of three people, John , George and Jim, column 2 the year for which they are paid (e.g. 2002, 2003 etc) and column 3 the amount of money they received each year (e.g. John received 18$ in 2005, $15 in 2004 and 11$ in 2003). I need a function to give me the average salary earned by each of these people in the period 2003-2004 e.g. John earned $15 in 2004 and $11 in 2003, so the average value is 13. Is there a function to do it automatically (so taking the conditional average---average for john given that year is 2004 or 2005)?

I want this average to be in column 5 (column 4 will contain the names john, george and jim)

View 9 Replies


ADVERTISEMENT

Conditional Average Of 2 Data Columns?

Jan 18, 2014

I am trying to get the conditional average of 2 data columns.

data_1 category data_2 category
2.5 Yes 1.2 No
4.2 No 2.6 No
3.5 Yes 3.9 Yes
4.1 No 2.5 Yes

What I need is the average across the 2 data columns (data_1 and data_2) for category Yes and category No. I know I can use averageif to get the conditional average of 1 column, but that doesn't work with 2.

View 5 Replies View Related

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.

View 3 Replies View Related

Conditional Average Using IF Possible?

Jan 30, 2009

I have a spreadsheet with days of week and places saved as text values, and I need to get averages for specific items,

for example,

Day of week in A1, place in A2, number in A3

I want to get in A4 say, the average of the numbers for all sundays at cambridge.

Any ideas how I could do this?

View 9 Replies View Related

Conditional Average

Jul 10, 2006

As you can see from my sheet example, there are three columns of numbers side by side. They represent fiscal year 06, fiscal year 05, and fiscal year 04. The numbers are recorded each month July through June. Each month, a new number is added for the current year, 06.

At the bottom there are cells that show the Average per month for each month. I need an offset formula, or one that adjusts when the new number is added for the month for the current year. For the previous year I need an average that looks at the current year and then the previous year would have a range average to match the current year’s average criteria and return the average for the previous year.

For example, if the current year is updated through March, and it returns an average for March 06 I need the second formula to return an average for March 05 in the next column, and March 04 in the third. As numbers are added each month, I need both formulas to change the average to expand to include the numbers

View 4 Replies View Related

Average Formula In Excel With Even Columns Only And Columns With No Value?

Apr 26, 2014

i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .

View 6 Replies View Related

Multi-Conditional Average

Mar 10, 2009

See attached example for reference - I am trying to calculate an average if it meets 2 conditions, i.e, calulate the average for the Lead Time column if it is a Bag and On Time. Hope you guys can come through as always!

View 4 Replies View Related

Conditional Weighted Average

Apr 20, 2009

I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:

View 8 Replies View Related

Conditional Math Using AND, Average

Jan 15, 2006

How do I correct to make conditonal formula with number values ?

My main worksheet has at leat 24 columns and as many as 30 rows, all have
numbered values. Rows are defined names.

I want to get average values when at least 2 conditions have certain values.

I tried =SUM(IF(AND(MapKpa:MapKpa=40),(RPM:RPM=1000),grmcyc:grmcyc))
and answer given is really total of all 30,000 row values for grmcyc and not
when mapkpa values = 40 and RPM = 1000.

Also best would be if the conditions allow lets say when Mapkpa is between
40 and 45 and RPMs between 1000 and 1500 to have a window of grmcyc average

I used Sum but really want an average of grmcyc when Mapkpa and RPM meet the
conditions so I can build a results table of what the grmcyc average value
was from 20 to 105 KPA in 5 KPA windows along with smaller RPM ranges.

View 12 Replies View Related

Conditional Average For Each Country

Mar 16, 2007

I have countries in column A and the values in column B.

I want to see the avareges of values for each country.

I have tried a few samples that I have found in the forum but they do not work for me.

View 9 Replies View Related

Conditional Average Of Range

Feb 15, 2010

I am writing this application wants me to calculate running averages and Std Dev. I will copy a row of values into a spreadsheet. The next row will be a new average of each column. So far so good. But the next time I calculate the new average of the column, I do not want to count the row that represents the last average I measured. SO, I'd like to format the rows that are average values (i.e. bold) and then when I calculate the next average, exclude any value whose font is bold. Currently I am using the following line of code to calculate average:

Is there an easy way to limit the values used to calculate average to only those values that are NOT bold?

Sub ColAve ()
Cells(LastRow + 1, ActiveCell.Column).Value = WorksheetFunction.Average(ActiveCell.EntireColumn)
End Sub

View 8 Replies View Related

Conditional Average Function

Aug 25, 2007

How do I perform average for a range and be able to exclude outliers. For example I am taking 100 measurements, some are not measureable and recorded as 9999.9. How do I average 100 values, but exclude all the 9999.9?

View 7 Replies View Related

Prevent #DIV/0 In Conditional Average

Feb 16, 2008

I have an average array formula that works fine when it has something to average. However I get the #Div0 error when there is nothing the average. I need to replace the error message in the cell to be blank. Until there is data to be averaged. I have tried to use ISERROR but struggling to get it to work with my multiple criteria.

This is the formula i am trying to amend:

=AVERAGE(IF('By All Students'!$S$3:$S$100>=80, 'By All Students'!$S$3:$S$100<92, IF('By All Students'!$Q$3:$Q$100="11.1/AR",'By All Students'!$O$3:$O$100)))

View 5 Replies View Related

Calculated Weighted & Conditional Average

Jan 22, 2009

The attachment is only a small portion of the data I need to analyse, but is representative of the full set of data. What I am trying to do is calculate a weighted average of each of the chemicals shown by location and machine usage and also still be able to use the filter or sort funtions. I have created a sample calculation in cell F9 to further explain this calculation.

I would like to be able to use the A-Z sorting icon in any of the columns, while still computing the weighted average of the respected chemicals. As it is now, the referenced cells just remain as the 6-7 cells above the calculation instead of the 6-7 cells that apply to the specific chemical at the location and machine (in this example - caustic at location1 on mch-03). For example, if I try to sort in column F, I'd still like to see my example calculation to reference the same values as it is now, calculating the same resulting 60.719.

View 3 Replies View Related

Conditional Average, Naming A Range

Feb 5, 2007

i'm very new to excel formulas & statistics in general. i only need to calculate averages but am having trouble naming the range in my formulas

in the example below, i want to use the first column to determine the range of data for the mean calculation.... so, I am seeking the average of just the numbers which correspond w/ A only. Similarly, I will then want an average for numbers corresponding to B, and then to C, etc etc...

View 9 Replies View Related

Omit Blanks From Conditional Average

Oct 22, 2007

I have a formula to calculate an average value for a reference range:

= SUMIF(DATA!B32:B61,"<># div/0")/MAX(1,COUNTIF(DATA!B32:B61,))

Because these cells are references the blanks are being treated as zeros so I am getting an inaccurate average value.

Is there a way to make it so it will treat blanks as blanks? I need the zeros to be zeros.

View 9 Replies View Related

Conditional Average From Two Address Functions

Jun 26, 2008

I'm trying to calculate a monthly average from a set of daily values in a separate sheet. I managed to use some nifty code to return the address of the first value in the month, and the address of the last value in the month.

I tried to create an AVERAGE formula to compute the average between these two looked up address and I'm getting various errors, including #DIV/0 which doesn't make sense to me. Here is the summary of the code I'm using, in generic terms.

Formula to lookup first address in month:
=ADDRESS(MATCH(A57,'Sheet2'!$A$1:$A$2000,0),12,1,TRUE,"Sheet2")

Formula to lookup last address in month:
=ADDRESS(MATCH( DATE(YEAR(A57),MONTH(A57)+1,0),'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2"))

Average formula (not working):

=AVERAGE(ADDRESS(MATCH(A57,'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2")):ADDRESS(MATCH(DATE(YEAR(A57),MONTH(A57)+1,0),'Sheet2'!$A$1:$A$2000,0),12,4,TRUE,"Sheet2")))

A57 is the cell in Sheet1 containing the relevant month, for example in this case it is 4/1/2008 (displayed as Apr-2008). Column A in Sheet2 contains the daily dates. Column 12 in Sheet2 contains the relevant data that I want to average.

I've tested the two address lookup formulas and they are working fine. I assume I'm missing some sort of small conversion such that the average formula is not reading the ADDRESS formulas as addresses.

View 7 Replies View Related

Conditional Weighted Average Within Time Span

Feb 23, 2008

have the follwing worksheet"

Date Rate Qty
2:06:56 PM 199.5 116
2:06:49 PM 199.5 343
2:06:40 PM 199.5 226
2:06:28 PM 199.5 48
2:06:20 PM 199.4 162
2:06:11 PM 199.5 95
2:06:04 PM 199.4 449
2:05:58 PM 199.6 1488
2:05:40 PM 199.5 9
2:05:40 PM 199.5 9
2:05:37 PM 199.4 161
2:05:28 PM 199.6 101
2:05:23 PM 199.35 4605....................................

I would like to calculate the weighed average rate( rate*qty/ total qty durig a period) for different periods say 2.00 pm to 2.01 pm, 2.01pm to 2.02 pm etc. Is there a way to do this by using database(dsum,daverage) functions. What will be the content of the criterion range? Or is there any other way of solving this.

View 2 Replies View Related

Weighted Average / Dynamic Range / Conditional Dashboard

Mar 27, 2014

The drivers are simply a start date and an end date.

For example: 1/1/2014 - 3/31/2014

To keep it simple I am only looking at 4 fields in the referneced data set. They are Date, Net Calls, Average Handle Time and Agent.

So records look as follows:

DATE AGENT NET CALLS AVERAGE HANDLE TIME
1/1/2014 Agent 1 30 Net Calls 2.00 Minutes
1/1/2014 Agent 2 40 Net Calls 8.00 Minutes
1/2/2014 Agent 2 05 Net Calls 25.00 Minutes
...etc

I am stuck on the Average Handle Time problem as this needs to be weighted on net call volume contribution by agent conditional on dates being equal to or greater than the start date and less than or equal to the end date.

I have attached a workbook for reference : Dashboard Table miacg.xlsx‎

View 1 Replies View Related

To Get Average Or Count Of Column With Conditional Statements For Date

Feb 12, 2009

The data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.

The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.

My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.

The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)

View 9 Replies View Related

Conditional Moving Average Omitting Adjacent Blanks

May 4, 2008

how do I perform calculations on the last x non-blank instances in a data range?
for example, let's say I have a spreadsheet of 5 baseball players' batting averages (rows are team game number played, columns are at bats and hits for each player). I want to see how each player has performed in their last 10 games played, but some players have not played in every game. If I just use the sum function for the last 10 cells, I won't get the correct information for any player that has missed one or more of the last 10 games.

View 8 Replies View Related

Average With Conditional Month, Year And Date Range

May 8, 2008

I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).

As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.

View 9 Replies View Related

Average Only Columns With Data

Nov 10, 2009

I have twelve columns of data nine of which are filled. I'm trying to come up with a formula that averages a column only if it has data in it, I have attached a sample file. As I add data the average should change without changing the formula.

View 2 Replies View Related

Average Of Columns Of Various Rows

Jul 30, 2009

I have data that I need averaged. The problem is that my data is taken into chunks of time, and I want to average each individual column after each break of time. My data looks like this: ...

View 6 Replies View Related

Average Formula For Different Columns

Feb 1, 2010

Looking for an average formula.

All found formulas average within a string (ex. A1:F1).

My spreadsheet contains a series of three columns.

Example:
Column A reflects a number stat for a month (January).
Column B reflects another number stat for the same month (January)
Column C reflects the difference between Columns A & B.
Column D reflects a number stat for the next month (February)
Column E reflects another number stat for the same month (February)
Column F reflects the difference between Columns D & E.
and so on.

I would like to average the rows stats reflected in Column A, D, G, J, and so on.

Also, I would like to average the rows stats reflected in Column B, E, H, K, and so on.

Also, I would like to average the rows stats reflected in Column C, F, I, L, and so on.

View 9 Replies View Related

Average Unhidden Columns

Jul 25, 2007

I am calculating the average for a range of cells in a row. =AVERAGE(D7:P7). I need to hide some of the columns and calculate the average based only on the visible columns. How can I do that?

View 5 Replies View Related

Step Average Across Every X Columns

Mar 11, 2008

Is there a way to loop through a range of numbers, pick the first 24 columns, do some kind of caclulation for each column and row, the start again on column 25 for another 24 columns, perform the same calculation, go to the next set of 24 columns, etc?

I have tried using, for example, AVERAGE(OFFSET(range,0,0,24,24) and that correctly returns the average for the first 24 columns (range being the range of data I am using like D366:HK389). However, unless I manually change the starting column for the next set, it doesn't return the correct result. I have attached a sample data spreadsheet. There are 9 Locations in the spreadsheet where each Location contains a 24x24 matrix.

View 4 Replies View Related

Average And Cell Data As Worksheet Name

Jan 12, 2007

1. if i use an average function, and there is nothing in the cells in the range that i am choosing, how can i get a zero answer rather than a # div/0 error. I have several sheets and several columns on each sheet where average is being used, but not all of the columns contain data. However, if the average is 0, i need it to show 0 and not the error.

2. is there a way to link a cell's data to automatically name a spreadsheet?

sorry if #1 is confusing, i've tried several ideas. i'm thinking that something like "if the value of (cell)=blank, then (cell value) = 0, but i don't know how to put that into formula format.

View 5 Replies View Related

Calculate Average On Some Of The Columns In A Pivot?

Jul 1, 2014

I have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.

I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.

View 1 Replies View Related

Average Column Based On Another Columns Value?

Jul 29, 2014

I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example

comp 1409 99.7
comp 1409 99.5
comp 1409 100
comp 1308 65.7
comp 1509 99.5

I would like that to become

comp 1409 99.73
comp 1308 65.7
comp 1509 99.5

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved