# Average By Weekday Of Month

Feb 9, 2009

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?

## Date Of Last Weekday In Month

Jun 5, 2008

I used a formula I found on this site to find the last friday in a month. = DATE(" & Str(iYear) & ",1+1,0)+MOD(-WEEKDAY(DATE(" & Str(iYear) & ",1+1,0),2)-2,-7)

Is there an equivelant date function that can be used when coding in vb (not within a cell.) The VBA editor does not recognize this. I believe it is because date is reserved for variables. If anyone can make this line of code work in vb so as I dont have to asign it to a cell in a worksheet you would be my hero.

## Auto Fill Week Dates To Match Weekday And Month

Nov 18, 2013

How do I auto fill a series of 2014 dates in the row below the weekdays? I have a row C5 that has a series of auto fill weekdays successfully for 2014. I can't seem to auto fill the weekdates in the row below. I have to make manual adjustments for each Monday, and for the correct month ends, etc.

## Formula To Count Weekday Occurrence Based On Month And Year

May 3, 2007

I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009 - does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.

## Using Offset From Latest Month To Calculate 3-month Average Within A Range

Jul 1, 2009

I 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........

## Weekday/workday Date 1 Month After The Last Contact Date

Sep 20, 2009

I have a list of clients that have specific requests waiting to be actioned, with a number of columns relating to client details and the status of the request across the top of the sheet. One of the columns (D) is "Last Contact Date", where I enter in the date that I last followed up with the client or made contact regarding their request. In the next column (E), I want the weekday/workday date 1 month after the Last Contact Date.

I can use "=D2+DAY(30)" to give me the date 30 days later, or "=DATE(YEAR(D2),MONTH(D2)+1,DAY(D2))" to get one month later, however what I want is the nearest WORKDAY after this date.

So, if the date is on a weekend, I need the Monday date instead.

## Average By Month

Mar 13, 2009

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.

## Average By Month ...

Nov 23, 2006

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

## Take Average Between First Tuesday Of Every Month?

Jan 28, 2014

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.

## Average Times By Month

Apr 25, 2008

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

## Weekday Formula: When I Change The Month, It Automatically Will Change My Formula

May 9, 2006

=CHOOSE(WEEKDAY("1 Jan " &\$C\$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 \$B\$1 " &\$C\$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

B1 = Jan
C1 = 2006

## Getting Average Revenue Per Client By Month?

Jun 26, 2014

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.

## Average If Data Is In Same Month Of Same Year?

Jun 4, 2014

So 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.

## Average Values If Month Equals?

Feb 17, 2007

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.

## Daily Average Based On Day Of Month?

Mar 2, 2014

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).

## Find Average Of The Specific Month

Oct 16, 2009

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.

## Formula For Rolling 12 Month Average?

Mar 16, 2010

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?

## Average Daily Data By Month

Jan 15, 2009

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.

## Macro To Average Data By Day In Month

Jan 13, 2014

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] ............

## 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)

## How To Create Month To Date Average

Aug 20, 2008

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

## Cumulative Sum For Weekly Average Hours Over A Month

Apr 21, 2009

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 ....

## SUM / AVERAGE / OFFSET To Specified Columns Containing Month Range

Mar 6, 2013

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?

## Finding Average Based On Month And Year

Aug 4, 2009

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)))

## Average Based On Time & Month Cells

Apr 25, 2008

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)) }

## Formula To Return Average Days Between Two Dates In A Given Month?

Jan 13, 2014

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.

## How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month

Mar 20, 2008

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 %

## 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.

## Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

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 ...

## How To Calculate Average Of Values Against Selected Date Of Each Month Within Date Range

Jan 2, 2014

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.