To Get Average Or Count Of Column With Conditional Statements For Date
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Average With Conditional Month, Year And Date Range
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 Replies!
View Related
Average If Statements
consider that I have two columns of data with say five rows each. Column A contains company names (say Company A,B or C). Column B contains revenue that I'd like to average. However, I want to exclude revenue from one of the companies (say Company C). Keep in mind that my actual number of rows is in the thousands, so manually picking the rows to average is not a viable solution.
View Replies!
View Related
Conditional Column Count
I have this big sheet, as u can see the device name in separate column and the information related to this device below it. i need to count the number of columns that contains = Fa or G only , also i need to count the number of columns that AdminStatus= up AND OperStatus=up ...
View Replies!
View Related
Count Conditional Format Cells In Date Range
Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab. There is an "overall status" column that pulls the worst color that exists for a given row. =IF(COUNTIF(F1:V1,"r")>0,"R",IF(COUNTIF(F1:V1,"y")>0,"Y",IF(COUNTIF(F1:V1,"g")>0,"G",""))) There is also a date for each row. Now we want to pull the worst color in the "overall status" column for a date range...and display that on a separate summary tab. For 4/1/09 thru 4/15/09 - pull the worst color from the "overall status" column from that date range.
View Replies!
View Related
Formula To Check Column A For Date Range And Count Column B
I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A. Here is what I have but it is counting the dates in col A not the order numbers in B?
View Replies!
View Related
Count Filled Cells In One Column Based On Date In Another Column
I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example: AB 1-May 1-May777 1-May 1-May 1-May 2-May999 3-May 2-May 1-May 2-May111 1-May If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
View Replies!
View Related
Conditional Formatting With IF/Then Statements
I need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on. The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46
View Replies!
View Related
Conditional Formatting With If Statements
I have a spreadsheet that has several columns containing names and data. In the first column, is a person's name, in the second, is a client number for that person, and in the third is the "last contact" date. I have a fourth "reference" column that has a conditional statement I was trying to use for formatting. For example, in A1 would be "John Doe," in B1 would be 987, and in C1 would be 06/10/07. The next row would have data for a different person. In column E I have my reference column. If the last contact date is less than 4 days from the current date (which is in cell K2), the reference column (using an if formula) will produce the number 1. If it is 5 or 6 days from the current date, it produces the number 2, and if it is 7 or more, then it produces the number 3. Here is the exact formula: =IF($C2+7<=$K$2, "1", IF($C2+5<=$K$2, "2", IF($C2+4>=$K$2, "3"))) What I want to happen is this: if the reference column produces a 1, then I want the corresponding row to turn red. This would only include the name, assigned number, and last contact date. I do not need the entire row to change a certain color. If a 2 is produced, I would like the row to turn yellow, and if a 3 is produced, I would like the row to turn green. This is going to run for many rows (up to 150). How do I do this using VBA? Keep in mind the numbers in the reference column will change if the "last contact date" is updated to a more current date.
View Replies!
View Related
Count IF, Then, Else Statements That Have Values
I tried Count if statements and sum statements, but nothing is working. I have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print the total number it repeats. This sheet is large. Here's and Example: Here's the data Jane Dog Jim Dog Jeff Dog Jane Cat Jim Dog Jane Dog Here is what the cells should print (just the number) JaneDog = 2 JimDog = 2 JeffDog = 1 JaneCat = 1
View Replies!
View Related
Count Column W/blanks & Values Based On Specific Date
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View Replies!
View Related
IF Statements And Date Logic
I am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week. I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January. I have attached a spreadsheet for everyone's reference.
View Replies!
View Related
Conditional Average Using IF Possible?
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 Replies!
View Related
Conditional Average
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 Replies!
View Related
Conditional Weighted Average
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 Replies!
View Related
Conditional Math Using AND, Average
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 Replies!
View Related
Multi-Conditional Average
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 Replies!
View Related
Conditional Average Of Range
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 Replies!
View Related
Conditional Average Function
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 Replies!
View Related
If/then Statements: Find All Blank Cells In Column
I am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L. Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
View Replies!
View Related
Find Column Reference Than Using A Chain Of IF Statements
I have a table of some numbers, and along the left side is a column representing what each row of data is. So in this case, it is a list of letters. What I'm doing is in each column of the data, I find the maximum number, and then I want to have it list below that what letter that maximum number is from. Here is an example:
View Replies!
View Related
Conditional Average-three Columns In My Worksheet
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 Replies!
View Related
Omit Blanks From Conditional Average
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 Replies!
View Related
Conditional Average, Naming A Range
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 Replies!
View Related
Prevent #DIV/0 In Conditional Average
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 Replies!
View Related
Conditional Average From Two Address Functions
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 Replies!
View Related
Extending Previous 'conditional Statements Solution'
use of SUMPRODUCT and what I believe is called a double unary operator (--). Here's the original post with Paul's solution: http://www.excelforum.com/excel-gene...-new-post.html I've since discovered that a previously unknown column of data (I'm transcribing data from paper documents) needs to be included in the calculations. Specifically, the new column of data (shown in yellow in the attached example) has to be taken into consideration when counting product quantities. An explanatory pseudo code statement, also appearing in yellow in the example sheet, describes.
View Replies!
View Related
Calculated Weighted & Conditional Average
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 Replies!
View Related
Conditional Weighted Average Within Time Span
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 Replies!
View Related
Conditional Moving Average Omitting Adjacent Blanks
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 Replies!
View Related
Formula To Count Multiple Ifs And Take An Average
Im having a problem finding a formula that will count and take an average of multipul ifs. On sheet 1 of the attached file is multipul queries raised for us to answer. On sheet 2 is an empty table which i would like help formulating. In the Submission Lag Time Column i would like a forumla that looks down Column L on Sheet 1 and gives me an average for the amount of days a query has been with us from each company (column B) per month (going off the dates in column D). I the same formula will be used for the other 3 columns, put looking down a different column on sheet 1.
View Replies!
View Related
Average & Count Zeros But Not Blanks
I am trying to make a excell spread sheet that will calculate my students averages for the year. I need to account for zeros in the coarse and I want this excel sheet to track the current average all year long . So I do not need to include my blanks in the average as I go. Also , how do I formulate my average accum to show this formula - Test scores, four of them count as 80% of the total grade. So each test is worth 20% / Lab 10% of total grade and homework is additional 10%. I downloaded the templete from MSN and have tweaked it to my liking except for the coding above. Please advise. I am a below par on Excel. My attachemtn is below of my templete. One note. the templete gave me the room to include 13 ros of homework - but I will not necessarily use all of them... I can make it one row for home work only ....
View Replies!
View Related
Average Function And Not Count The Blank Cells
I've got a spreadsheet that I do every month with columns of numbers that I average. This sheet has to match about 10 others similar. The columns are divided by Weekdays, Saturdays, Sundays. But some months there are no entries for certain cells on Saturday or Sunday. I thought that if I just used the Average function, it would dismiss and not count the blank cells. Alas, apparently not. I've highlighted in yellow the one column that I'm really having trouble with.
View Replies!
View Related
Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days 17, 4 17, 3 17, 5 26, 4 26, 8 26, 10 26, 7 On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View Replies!
View Related
Conditional Format - Can"And" Statements Be Used?
Let's say I have a cell which has a value between 0% and 100% and I want to set three criteria to change colors. The problem is that I only have three conditions that are able to be set and recognize that I need the first to remain as is, so that it stays white if no data in there. Can it be done without a macro? For instance: Condition 1 - Cell Value Is - ("") - the result is shaded white (this works) Condition 2 -Formula is - =F8>=(0.95),(and(F8<(0.99) - I want this green, but it isn't taking it? Can you put multiple statements in a conditional format if the answer is either true or false? Condition 3 - Cell Value Is - <95% - Turn Red (This Works)
View Replies!
View Related
Conditional Statements Using IF: Get A Discount (no Discount) Not To Apply The 10%
I have attached a 2004 excel document that has a conditional statement that says, if an amount is over $400.00 you get a 10% discount, else, the cell says no discount. That is working fine. I also have a absolute cell ready to do the math for the 10% amount. There is also a column that shows the discount amount and column that shows the amount after the discount has been applied. Since I have been just doing one calculation and auto-filling, my problem is, how do I get the record that doesn't get a discount (no discount) not to apply the 10%?
View Replies!
View Related
Count Unique Values, And Also Average The Time Per Assignment
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment. Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes. I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View Replies!
View Related
Count/Average Number Of Specified Name Where Corresponding Cell Is Not Blank/Empty
sumif problem but it wont work with a countif or average if. Column A has various names and Column B has amounts, what I need is to count the number of occurances "John Smith" has an amount in Column B. The previous formula I tried was =sumif(A:A,"John Smith",B:B) but with either countif or averageif it errors too many arguements. I wasn't sure if Dcount or an array would be suitable but have not used them before. Pivot tables I'm sure will be the future with this but haven't got to the foot of that mountain yet.
View Replies!
View Related
|
TRACKER
