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
ADVERTISEMENT
Sep 19, 2009
I have a range of cells (say A1:D8).
I wanna find the address (say $B$2) of the minimum value in that range.
This command does it but it only works on a column. it does not accept a range spanning more than one column.
=CELL("address",INDEX(B2:B7,MATCH(MIN(B2:B7),B2:B7,0)))
this will tell me the address of the minimum value.. but in a column... I need a range of many columns.
So I tried converting things to a Table.. but it never worked. basically the MATCH command is my problem. It only accepts single-column-ed ranges.
View 13 Replies
View Related
Dec 17, 2012
I am having an issue when I try a chart a named range. The named range "Refers To" is
Code:
=OFFSET($A$1,(MATCH("Kevin",$A:$A,0)-1),,,COUNTA(INDIRECT(ADDRESS((MATCH("Kevin",$A:$A,0)-1),1,1,1)
&":"&ADDRESS((MATCH("Kevin",$A:$A,0)-1),100,1,1))))
As far as I can tell, the formula works just fine. If you look in the Name Manager and check on that Name, the highlighted box shows up exactly what it should and if you do a simple MAX test, it displays the correct MAX value from that range.
The problem comes in when I try and add it to a chart. Instead of a displaying all the values in the range, it displays nothing. Doesn't flag up any errors, just nothing.
The reason I chose to do it this way is that the table contents could change on a daily basis and so could the position of the row and I don't want to have to keep changing the references in the graph so make sure the right data is being displayed. Is it just that charts don't play well with the INDIRECT function?
View 4 Replies
View Related
May 1, 2006
the scenario is a spreadsheet for adding call centre operators evaluations (1 being worst and 5 being best) from the clients. in the spreadsheet, i need to create a function to add a quick evaluation based on the operators score from the client
so if the score is 4 or higher it would read "good - improving"
and if it is less than 4 "poor - improving"
i think this is just a simple IF question...but i have forgotten how to do it
View 3 Replies
View Related
Aug 11, 2009
I need to use the ADDRESS function to define a range from which an average can be determined. My current function is as follows:
View 3 Replies
View Related
Jan 22, 2014
I need to calculate a Stdev and Average based on a specific range. However, my range is not constant in the time. For instance, today I may need to calculate these functions based on 30 numbers, but tomorrow on 25 or 15 or 50...
Is there a way to automate this process by changing the number of data points to include in the range in one single cell and avoid manually adjusting the range in accordance with needed data points?
In other words, I would like to change the value, let's say in Cell A1 from 30 to 50 and then Excel would re-calculate the StDev and Average based on 50 data points and not 30. Is that possible?
View 12 Replies
View Related
Aug 4, 2009
I have an excel problem with formula. I’m not sure of the formula I think its sumif but don’t know how to express the criteria . I am trying to create a spreadsheet that will total the data from Colum B, C & D. I have 3 columns with the following data.
Column B Heading Length
Column C Heading Width
Column D Heading Depth
Column E Heading LM (lineal meters) will have the data only from column B. Column F Heading M2 will equal the data of column B & C (remembering the math is M2 = column B x Column C. Column G Heading M3 will equal the data of Column B, C & D (remembering the math is M3 = column B x Column C x Column D. That’s the easy part but in columns H to J, I have other headings and column K is my Rate.
Column L is my subtotal and this is where my problem lies, because the formula is =SUM(E1:J1)*K1 it has the potential to double up on figures when I use the dropdown formula for Columns E,F, and G all data is carried down with them. I want Column E to show only the value, if column B has a number and Columns C & D are empty. I want Column F to show only the value, if columns B & C have a number and Column D is empty. I want Column G to show on the value, if all columns have a number. If all these conditions are met, then only a value will be displayed in either column E, F, G Remembering that the values returned must be multiplied by the value of each number in the columns.
View 3 Replies
View Related
Jul 6, 2009
I have a spreadsheet whereby in each cell of a row there may be a formula or/& conditional statement applicable. I would like to create a macro that can perform the insert of the new row as well copy down from cell above applicable functions as well as conditional formats that maybe resident. Can this be done?
View 8 Replies
View Related
Apr 16, 2009
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
View 6 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
May 22, 2014
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
View 1 Replies
View Related
May 20, 2014
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600
View 2 Replies
View Related
