Using Offset From Latest Month To Calculate 3month Average Within A Range
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. Jul09).
Jan09
Feb09
Mar09
Apr09........
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
How Do I Create A Running Average That Will Only Calculate The Averages In % Each 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 Replies!
View Related
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 averagewhich would be the average of numbers found between Jan21Feb20, I then need Mar 08 average which would be data from Feb21Mar20 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
Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)
I have log data in two columns: Column A: Date/time (at 30 minute intervals) Column B: Numeric data On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C. The end of the range is determined by the month in the current row. I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting midday and midmonth. I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach. Manual =IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
View Replies!
View Related
Average By Month ...
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
View Replies!
View Related
Average By Month
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.
View Replies!
View Related
Average By Weekday Of Month
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?
View Replies!
View Related
Average Times By Month
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
View Replies!
View Related
How To Create Month To Date Average
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
View Replies!
View Related
Average Daily Data By Month
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.
View Replies!
View Related
Average Based On Criteria :: Within Each Month
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)
View Replies!
View Related
Find Average Of The Specific Month
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.
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
I would like to create a monthly inventory, based on workdays (Monday  Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them. =IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View Replies!
View Related
Automatically Bold And Highlight The Current Month’s Total And Month Name
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name. Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
View Replies!
View Related
Finding Average Based On Month And Year
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)))
View Replies!
View Related
Calculate Interest Per Month
Sample data: 020609 to 180609 earned $2500 190609 to 230809 earned $3500 240809 to 311209 earned $4500 I am thinking if there's a formula that can calculate the interest earned for each month from Jun09 to Dec09.
View Replies!
View Related
Calculate Fiscal Month
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month
function in a spreadsheet that will list all of the days in a given month automaticaly with the entry of the 1st of the month only. Ex; 10/01/05 entered dated 10/02/05 auto fill 10/03/05 " . " . " 10/31/05 end of auto fill I would like the function to stop filling dates at end of the month even for shorted months such as Feb.
View Replies!
View Related
Auto Format Spreadsheet With Various Rows Month To Month
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month. Goal: I need to format the spreadsheet to make it more readable and have been assigned the task of: 1  Inserting a blank row between each row that contains data and filling in with color. 2  Resizing the blank row to make it look like a "thick" border. 3  Auto adjusting the columns to correct size. 4  The last column contains comments and needs to be wrapped text. 5  All of this needs to fit on 1 sheet (landscape). Issues: 1  Each month there will be a different number of rows. 2  I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls? I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.
View Replies!
View Related
Cumulative Sum For Weekly Average Hours Over A Month
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 ....
View Replies!
View Related
Average Based On Time & Month Cells
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)) }
View Replies!
View Related
Autocalculate Last Day For A Month
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days. Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month? IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29". The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
View Replies!
View Related
Selecting A Month To Calculate Invoice
Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates. H I J 27 Qauntity: Cost: Amount: 28 55 R 185.00 R 10,175 29 30 Net amount: R 10,175 31 Vat: R 1,424.50 32 Amount Due: R 11,599.50 Invoice [TableIt] version 09 by Erik Van Geit RANGE FORMULA (1st cell) H28 =SUM(Divenic!G5:G5004) J28 =SUM(H28*I28) J30 =J28 J31 =J30*0.14 J32 =J30+J31 [TableIt] version 09 by Erik Van Geit
View Replies!
View Related
Adding Or Subtracting One Month To A Month Number
I have forumlas that will look at this cell and take action of the month in a different cell is either 1 month greater (Frontmonth+1) or less (Frontmonth1) than "Frontmonth". As we approach December I'm realizing that logic will breadown since the FrontMonth+1 would be 13, not 1 (January) Is there a way to get excel to add 1 month to just the month number so that if Frontmonth = 12, Frontmonth+1 would return 1, not 13?
View Replies!
View Related
Calculate Working Days Left In Current Month
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule. We are open MF and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically. On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month. Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08202007). Is there a way to get Excel to do that?
View Replies!
View Related
Formula To Calculate Date End Of Prev Month
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box. I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13. where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work. I would like a formula that will calculate these previous month end dates correctly. See example below ....
View Replies!
View Related
Calculate Number Of Dates Within A Column Based On Month
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything. So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View Replies!
View Related
Date Function Query (calculate The Last Day Of The Month)
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5) The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0) This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
View Replies!
View Related
Auto Calculate End Date Of Previous Month
I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically? I enter in Cell 1: 1/31/09 Excel calculates in Cell 2: 12/28/08 I enter in Cell 1: 2/28/09 Excel calculates in Cell 2: 1/31/09
View Replies!
View Related
SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View Replies!
View Related
Results By Month And Week Of Month
I have a range of data which is as follows: Week in month: 1 1 1 5 Site: 01/03 02/03 03/03 etc 30/03 etc Leeds 10 9 15 20 Manchester 8 5 1 2 Etc Here's what I need to produce: March 08 April 08 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Leeds Manchester I need to sum week 1 to 6 for each month Mar, Apr and so on. The different sites are in the same order so that doesn't matter too much.
View Replies!
View Related
Last Day Of Month Reverts To First Of Month
I'm doing a web query that brings down stock dates/prices. The dates come down as mm/yyyy. I then format the column as mm/dd/yy which changes the date to mm/01/yy. Then I do a loop that converts the dates to the date of the last weekday of the month. I see the changed dates written back to their cells, but by the time I leave the called conversion subroutine and hit the break on the next line of code, the dates have all reverted back to the first of the month. In a case like this, I use the Step mode in VBE to step through the code while I have the worksheet the code is acting on visible. That way you can actually see which line in the code converts the value back to the original value or if indeed it does put the eomonth date in the active cell. By the way, I'd suggest you use the equivalent of the eomonth worksheet function directly in the macro rather than pasting the date to another cell and reading the eomonth from another cell containing the formula for eomonth.
View Replies!
View Related
Calculate Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am5pm MondayFriday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View Replies!
View Related
Increase The Range Each Month.
I currently have an excel spreadsheet which has monhtly sales totals in it. We have some forumla that compare Year To Date figures with YTD Budgets and we also wish to compare Current Year To Date to Previous Year To Date. The problem I have now is that it currently means updating the range formula for Year to Date Budget and I will also be required to update the Previous Year To Date sales manually too. Im looking for the spreadsheet to recalculate theses figures based on the month that they are in. For instance should I be working on the Spreadsheet in January, it will only use the values in the budget sheet from AprDEC. Currently I woud be required to change the range in the formual to take into account the extra cell required.
View Replies!
View Related
