I have a list of dates in Col A, then data in B. I want to take an average of the data between the first Tuesday of every month. What's the best way to do this?
So in Jan 2014, that would be all data from the 7th until the 4th. Inclusive of the 7th, not inclusive of the 4th.
I am wondering if anyone could possibly help me figure out an excel function that would allow me to make a list of payments by year (from 2008 to 2015) that are received on the second tuesday of every month.
View 9 Replies View RelatedI am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?
View 3 Replies View RelatedI have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
I have the formulas below that work great. The issue is they are all based on Today. I need them to read cell B2 and base them on that instead of today.
Example: Today is Tuesday 5/13/14. So if I put in =TODAY()+7-WEEKDAY(TODAY()-6) it will give me this coming up fridays date 5/16/14. Perfect.
I want to add something to that so it looks at B2 instead of today, so if B2 had 6/16/14 (falls on a Monday) as a value it would give me 6/20/14(that is the next friday after 6/16/14).
Sunday =TODAY()+7-WEEKDAY(TODAY()-1)
Monday =TODAY()+7-WEEKDAY(TODAY()-2)
Tuesday =TODAY()+7-WEEKDAY(TODAY()-3)
Wednesday =TODAY()+7-WEEKDAY(TODAY()-4)
Thursday =TODAY()+7-WEEKDAY(TODAY()-5)
Friday =TODAY()+7-WEEKDAY(TODAY()-6)
Saturday =TODAY()+7-WEEKDAY(TODAY()-7)
I am trying to use configure a code similar to this...
=IF(TEXT(A1-1,"dddd")="Friday",A1-1,IF(TEXT(A1-2,"dddd")="Friday",A1-2,
IF(TEXT(A1-3,"dddd")="Friday",A1-3,IF(TEXT(A1-4,"dddd")="Friday",A1-4,
IF(TEXT(A1-5,"dddd")="Friday",A1-5,IF(TEXT(A1-6,"dddd")="Friday",A1-6))))))
But to identify the date closest that is not a Monday, Tuesday or Friday.
For example... The attached sheet cell C33.
i have the following formula
=SUMPRODUCT(--(TEXT(DSA!$E$5:$E$10000,"mmmyyyy")=$C$4&$C$3),(DSA!$AW$5:$AW$10000)*(DSA!$F$5:$F$10000="")*(DSA!$AJ$5:$AJ$10000="TLS"))
which basically looks at the Month and Year in reference cells then looks through my data on a sheet called DSA..
I have a colum on the DSA sheet AX5:AX10000 where there is a number
I want to be able to calculate the average of these numbers but only if the conditions in the above formula are met.
For example, if the month was MAR 2009 then i would only have the average of numbers present in Colum AX on the DSA sheet for March 2009.
I am trying to input monthly budgets based on a yearly budget for which I already have a few month's budgets. For example:
Jan - ?
Feb - ?
Mar - ?
Apr - ?
May - ?
June - $1105
July - $1325
Aug - $1470
Sept - $835
Oct - ?
Nov - ?
Dec - ?
TOTAL YEARLY BUDGET - $10,000
I need to find out what the other 8 months are, on average.
I'm looking for something along the lines of
each month's average IF SUM(A3:L3)=M3
I have a sheet that contains a number of documents entered into a system by a user.
Each month those number of documents are average by the number of working days in a month.
I'm using this formula, =AVERAGE(C4,22)
I would have to edit this for each month with the number of working days.
Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?
I am looking for a macro that will allow me to find the mean for all of a specific time during a month. For Example, I want to find the average percentage of capacity for every day at 11:00 am for the month of January.
Percentage of Capacity By Hour Hour Month/Day/Year
0.00% 11:00 1/22/2007
0.00% 12:00 1/22/2007
0.00% 13:00 1/22/2007
0.00% 14:00 1/22/2007
0.00% 15:00 1/22/2007
0.00% 16:00 1/22/2007
0.00% 17:00 1/22/2007
0.00% 18:00 1/22/2007
0.00% 19:00 1/22/2007
0.00% 20:00 1/22/2007
0.76% 21:00 1/22/2007
1.59% 22:00 1/22/2007
1.38% 23:00 1/22/2007
2.68% 0:00 1/23/2007
1.75% 1:00 1/23/2007
3.65% 2:00 1/23/2007
3.40% 3:00 1/23/2007
2.41% 4:00 1/23/2007
I have sales data for Clients that has the client name month and year(combined for date) and revenue for each month. I would like to take all clients in a given month and see the average revenue per client and then be able to display that in a bar chart by month. I also have the data loaded into a PowerPivot Data Model.
View 1 Replies View RelatedSo I have some data that I would like to have average only if that data was entered in the same month and year as specified in another cell. What I have tried so far is:
[Code] .......
-RenewalMonths is a dynamic range where each cell in the range shows the month of the date in that row.
-RenewalYears is the same but for the years.
-RenewalOverallStuff is a dynamic range where I would need to average the data that meets the criteria.
I have a list of groundwater level values in 800 different wells that were measured in April and October of each year from 1982-present. The problem is that they are listed in order:
Date ELV
04/16/1981120.37
09/17/1981119.25
04/10/1982120.52
10/10/1982118.1
04/10/1983124.36
10/10/1983120.43
etc....
I want a formula that will average all the April values and another one that will average all the October values.
I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.
I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).
I have two columns: 1) Last Date of each month from 2005 and 2) Value.
Date format: dd.mm.yy
For example:
31.01.05 1.7
28.02.05 -0.7
31.03.05 2.6
30.04.05 0.4
....
How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.
See Attached.
The YTD 10 column is fine that will change everytime you update each month. I was wondering if there was a formula I could use for the Rolling 12 month Column.
At the moment it is averging from Mar 09-Feb10 which is correct but when it comes to putting in data for Mar 10, the Rolling 12 month column will obviously not change.
Is there a way of moving the formula along one each time you input data into a new month. So when Mar 10 has been entered the rolling 12 month column will pick up Apr-09-Mar-10 then when Apr 10 has been entered the formula will change again to May-09-APr-10?
I have a workbook with two sheets - DATA and SUMMARY.
DATA has two columns - date and data_value. Data will be added to this sheet on a regular basis
SUMMARY has two columns - month and average
In the column for average I would like a formula to calculate the average of data_value for each month without having to manually determine the range for the particular month.
I am trying to create a macro (pretty new) to take a sheet of data and out put the average of each column by date. The data will be filled every month and the numbers i need should be one for the 1st, the 2nd and so on for each day.
Here is a sample of the data in the sheet, i would attach a spreadsheet but cant seem to find out how.
TimeStampUTC
Air Volts
Dry Volts
Dirt
Dirt Fraction
Temp
12/1/13 0:47
4.1180
2.3714
0.5735
-0.6800
11.1674
[Code] ............
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)
I'm having difficulty understanding this concept, more so the formula.
So to figure out the MTD for Day 1,2,3,4, how would it be?
I want each day to have a MTD
etc.
Day 1: 33,966,611.42
Day 2: 33,966,611.42
Day 3: 33,966,611.42
Day 4: 34,074,098.36
I have a column called "Weekly Working Hours" which totals the number of hours worked per week. The cell is filled in every Saturday.
In the next column I have "Average Weekly Working Hours per Month" which needs to calculate the average number of weekly hours every four weeks, filled in every Saturday.
Please see attached file. I am referring to columns J and K ....
Cell C6 contains a drop down from which a month can be selected, range is Oct-12 to Sep-13
Column AB will contain a YTD sum of actuals, Column AC will contain sum of budget.
For example,
Actuals: If Mar-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6
Budget: If Mar-13 is slected in cell C6, I would like call AC10 to sum E6+G6+I+K6+M+O6
2nd scenario
Actuals: If Jun-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6+P6+R6+T6
Budget: If Jun-13 is selected in cell C6, I would like cell AC10 to sum E6+G6+I+K6+M+O6+Q6+S6+U6
I've been reading up about an offset function but can't get to grips with it - should I use that, or an index/match mix?
I have 5 years of data in 2 columns:
Col A. Col B.
8/2/2004 Value 1
to
7/31/2009 Value n
I have a table set up as follows
1 2 3 4 5 6 7 8 9 10 11 12
2004
2005
2006
2007
2008
2009
I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...
I tried the following to no avail... I'm just getting a zero value:
=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))
How do I average the data in column G, based on two conditions (time in column H = $H$1, month in column I = month in $I$1)
Here's the formula I came up with, but it is including blank cells in the calculation.
{ =AVERAGE (IF ($H$3:$H$100=H1, IF (MONTH ($I$3:$I$100) = MONTH(I1), $G$3:$G$100, FALSE), FALSE)) }
I have a workbook with two sheets. The first has a list of job positions open, columns designated to stages in the employment process and in these columns, my staff enter the dates that they completed a particular stage.
"Position | Date Opened | Stage 1 | Stage 2 | Stage 3 | Stage 4"
Pos 1 | 01/01/2014 |02/01/14|03/01/14|07/01/14|09/01/14
This has a great number of entries and they are increasing and decreasing every day depening on the amount of jobs available.
On the second sheet, I am trying to set up a table which shows the average working days it is taking to complete each stage, divided into the months in which the job position was opened (i.e. for positions opened in january, the average completion working days for stage 1 was X amount of days etc...)
I have tried using =IF(AND(Logical, Logical),TRUE,FALSE) but this doesn't return any info as the logicals will always be false. I tried the OR function, but that requires only one of the criteria to be true to return a value. What I need is for the formula to return a number of days between two dates, ONLY if the opened date falls in one month.
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
View 9 Replies View RelatedI 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.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I have a table that looks like this (its basically a historical data of a stock exchange):
Date
Index
January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range.
Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.
View 13 Replies View Related