I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
I am trying to convert quarterly data to monthly
The quarterly data columns go: Mar-14, Jun-14 etc
The monthly data columns goes Jan-14, Feb-14 etc
I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14
Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?
I have a table which has the following columns:
Date - Data1 - Data2 - OtherData1 - OtherData2
I came up with some formulas to count my data monthly. I have 12 tables with this kind of formula in it:
[Code] ....
Where B12 is the year and A213 is my month number. My first try on the "date filter" looked like that:
[Code] .........
And it wasn't working so I thought it was because the 31 wasn't a good idea for non-31-days-months but none of the formulas above are working.
(BTW, IDK why it's not working but I have data in my table for months 10, 11 and 12 and the only calculation tables that are calculating data are the ones for months 9 and 10. The results are the same in these two tables and are counting all my Table1[Data1] and [Data2] (the count is not monthly))
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
I believe there is a way to summarize monthly data with the month in rows and the heading across columns. The goal is to have a formula summarize the data with Q1 through Q4 in rows and headings across columns and vice versa. Sample data attached.
Sample.qrt.data.jpg
How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.
mockup.xlsx
Is it possible to turn monthly data into daily? Repeating the same data for four sets of five working days to turn a CPI monthly inflation index into daily data, to turn example 1 into example 2.
Example 1.
DATEVALUE
2008-01-01 212.174
2008-02-01 212.687
2008-03-01 213.448
2008-04-01 213.942
2008-05-01 215.208
Example 2:
2008-01-01 212.174
2008-01-02 212.174
2008-01-03 212.174
2008-01-04 212.174
I need a formula that i can put in cells that can count datas automatically each month. The data that i have is huge and it updates everyday. As an example i will explain it simply.
Lets say i have a list of product groups in column A. Column B is the date where the samples arrived, Column C is the approval, Column D is the sending date.
I probably need to post a picture of the table: [URL]...
sample workbook: Attachment 197668Attachment 197668
So as you can see, i need to a formula to count the percentage of "approved samples", "not approved samples" and so on (for month december)
The approved sample for month december should have these requirement: If the date of arrival is in December 2012, if the approval says "approved" and NOT EMPTY,if the date of sending is NOT EMPTY.. So from the table, the answer is 2.
The percentage should be the 2, divided by all samples (Telephone) arrived with approval, doesnt matter if the date of sending is empty or not. (which is 4)
I would want a formula that can do this automatically monthly, so i dont need to do the formula time and again.
I have data in the following format (columns A & B):
Code:
Day Value
1/1/2002 2.1
2/1/2002 4.8
3/1/2002 0.1
" "
" "
" "
31/12/2011 5.2
I'd like to be able to write a formula so that I can get the daily data averaged over each month, e.g so that I have something like:
Code:
Day Value
Jan 2002 3.3
Feb 2002 4.3
Mar 2002 3.1
" "
" "
" "
Dec 2011 4.2
I'd like the data in monthly averages so that I can graph them. I have read about array formulas and averageif statements, but I can't seem to combine them to make them work.
What I've got is 2 worksheets. One is updated every month with new data and therefore the style of the new worksheet is likely to change.
The other one is static and needs to take the data from the updated one. I can't just use a basic =sheet1!A1 because the data will get moved around.
I need to be able to tell it to: look in the new sheet, based off the column heading and row heading find the cell and copy it to the same place on the static sheet. e.g.
In G14, I need it to find on the second sheet "Apr 08" as the column heading and "Yell.com" as the row heading and put it in G14.
It would be easy to do it manually, but as the spreadsheet grows, it'll be harder and harder.
How to calculate the monthly sales based on the data given ?
View 4 Replies View RelatedI have a daily rainfall data and I want to convert it to monthly totals. I tried the pivotal table but the arrangement of the data seemed to be confusing (i.e the year, month and day are in different columns).
See the attached data: Raindata_excel_forum.xlsx‎
I have been trying to compile some monthly and annual data from our main sheet to a FY09 sheet (for the fiscal year 2009). The 2 tabs in the uploaded example are the Distribution tab which we use to track the status of every item and the FY09 tab where I need the totals to be calculated for each month as well as the entire year. I have tried several formulas I found while searching the forum but I can not seem to get any of them to work, (I am sure it is because I don't understand them very well).
Since any formulas used will need to be copied 200 or so times, I would really like a VB solution which should also reduce the physical size of the file. I also tried a pivot table but I do not think it will show everything I need.
Basically, I need all the items separated by month on the FY09 tab. Then column 'F' on the Distribution tab needs to be summed up for each item in column 'D' of the FY09 tab for the respective month and multiplied by the respective item price in column'C' with the total value going in column 'E'. Column 'D' and 'E' need to be summed up for each month and cumulative for the entire year. There needs to be an average items and value for each month as well as for the year.
I am to the end of my wits - or maybe it's impossible to do the following with formulas?
I have the data like this:
Column A: Date (which is basically the date for the beginning of weeks)
Column B: Month of the date in Column A
Column C: Year of the data in Column A
Column D: Weekly data.
A: DateB: MonthC: YearD: Weekly data
12/11/200612200619
12/18/200612200644
12/25/200612200650
1/1/20071200741
1/8/20071200737
1/15/20071200741
1/22/20071200741
1/29/20071200741
2/5/20072200732
2/12/20072200736
2/19/20072200740
2/26/20072200735
Maybe it's because it's Friday night, but I just can't invent how to do the following:
Create a new column E that would contain the monthly sum of ColumnD across all weeks of this month - but entered only against the first week of that month (that is currently in Column A)
I.e., in my example it should be:
113
empty
empty
201
empty
empty
empty
empty..................
I am trying to write a macro that will automatically change the source data for a graph. This is an existing graph that is on tab GraphYTD and the data for the graph comes from DataYTD. The number of rows of data will change monthly, but I would like to use the same graph template every month. Here is my
Sub SortYTD()
Dim myBottom As Long
myBottom = Sheets("DataYTD"). Range("B65536").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("DataYTD").Range("F2:I" & myBottom)
ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphYTD"
End Sub
I am getting Runtime error 91 - object variable or with block variable not set.
I have to compute monthly standard deviation (volatility) from daily data.
The problem consists on the fact that the days during a month are not constant and change over time.
I attach the excel file : Price_Time_Series.xlsx
In this xls file, you can find dates in column A, price in column B and returns in column C.
I want to compute standard deviation at the end of the month for stock returns (column C), by considering all available days.
As you can note from the file, the days change over time and they are note constant. How can I compute standard deviation of them?
My excel sheet contains a set of date-marked expenditures. It's all one running list - no starting a new column for each month or anything like that.
I'd like to include a separate column/sheet showing total expenditure for each month. I've tried multiple formulae, along the lines of "Sum the B cells but only those for which the corresponding A cell is in the right value range". Or in programming terms, "for i from 1 to infinity, if x < A(i) < y, add B(i) to sum." Since other formulae on the sheet are independent of item ordering (don't require the data to be sorted chronologically), a plus would be if the solution can be, as well.
A separate thing I'd like to do (which I'm guessing has a similar solution) is to include grand (running) totals by expenditure type. A separate column of (non-numerical) expenditure codes is next to the data.
I am trying to do an automatic input with my daily total sales.. i did it in total but my problem is i don't know how to make the date change.
View 2 Replies View RelatedIn column A I have dates, in column B I have data.
What I am trying to do is get the monthly totals from the data so instead of:
01/02/08 - 52
06/02/08 - 87
14/03/08 - 23
12/13/08 - 12
I would get:
Feb 08 - 129
Mar 08 - 33
I have used the search function but can't seem to find a suitable solution.
I'm trying to figure the best way to extract 12 continuous months worth of data from a table and plot on a graph, each month with a separate plot on the graph (i.e. not the summed total but rather, a line graph with 12 points). The data is based on two, user-generated inputs, that are produced from a dropdown box.
I can create the Data Validation rules, use a Match formula to produce the reference for an Index formula and i have created a bunch of Named Ranges to work with and can easily generate the FIRST value to plot.
E.g. INDEX(DATA,ROW,COLUMN). After the first value, I'm stuck on how to generate the rest.
I have attached my problem for reference : Excel Problem.xlsx‎
In the attached sheet, I have logged my gas energy consumption (kWh) from 2006 to 2012. I want to analyse my relative consumption over time, on a monthly basis.
However, the bills have been generated by different suppliers for irregular periods (eg. 10-May-2006 to 24-Jul-2006, or 23-Feb-2010 to 6-Mar-2010). So, how can I re-tool my sheet to arrive at rough figures that can be allocated against each month?
Or, if there is no dependability about that method, formulae which allow for effective comparison over time? Let's say, daily?
I have a series of 12 monthly data values:
1/31/19990.155375
2/28/19990.179262
3/31/19990.179392
4/30/19990.178993
5/31/19990.17128
6/30/19990.139678
7/31/19990.15518
8/31/19990.15536
9/30/19990.17732
10/31/19990.117725
11/30/19990.134171
12/31/19990.149113
and also a series of the 365 days that comprise these 12 months.
I need to populate each of the 365 days within this period (1/1/1999-12/31/1999) with its corresponding value for the month, i.e. 1/21/1999 (and all days in Jan) will take 0.155375 and 5/4/1999 (and all days in May) will take 0.17128.
How I should set out my data and which formulas I need to use?
I have the following setup:
52 weeks of 2010 cost with the following structure:
week number | wk1 | wk2 | wk3
week ending | 1/10/2010 | 1/17/2010 | 1/24/2010
weekly cost | 57,000 | 35,000 | 42,000
I want to be able to use the above data to present a summary, but instead of weekly, list the data on a monthly basis:
month | Jan | Feb | Mar
monthly cost | wk1-4 | wk5-8 | wk9-12+3days wk13
Any way to set this up automatically instead of having to split the weekly data as many months share the same weeks?
I have a spreadsheet that has 3 columns, date, id and amount for the whole year.
what I need to do is have a macro on another sheet or workbook that has an entry for month. when i enter the month, i need to get all the data for that particular month and then save it with the month name. i am not very sure how i can do this. pivot table does not work as i need to save a file each month.
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))
However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.
Essentially I want to show how many loans funded in October 2013, from ABC Lending, and I want that result to show in cell K9 of the Sample Output sheet. However, since I'm dealing with filtered data, I cannot figure out the correct combination of formulas to make this happen (e.g., SUBTOTAL, VLOOKUP).
Specifically, I'm stuck with the combination of a) counting the results that meet the monthly date criteria, combined with b) the filtered results. The single cell that I want to solve is K9, on the attached "Sample Output" sheet. Once I see how this cell works, I can figure out the rest. This cell is looking for the number of loans that funded in October 2013, from ABC Lending.
The filtered results are located on the "SCORE Data" sheet. The filter is already configured to only show results for ABC Lending, and the relevant column is labeled "Funded" (column "I"). Since the "Sample Output" sheet is looking for loans that funded in that month, and not a specific date, I created column "U" on the "SCORE Data" sheet, to convert specific day-dates to month-dates.
My excel database has a master sheet where all data is included. It consists of 8 columns with two rows of headers (1st row: Sheet title, 2nd row: Categories for the columns like, name, salary, emp. start date, Boro,emp. number...etc.
Master sheet aside, I have 12 other sheets in the same workbook (Feb-Jan), we run a fiscal year, not calender. The data that is included in the master sheet needs to copy over to the corresponding worksheet sheet month as long as the date in the emp. start date falls with the month range. For example, if my master sheet has data in rows 4,5,7,9,19,23,101...600 and the emp. start date is in the range of from June 1st, 20xx through June 31st, 20xx then all the data in that row should copy over to the next available row in the month of June's worksheet. The same should happen the months of Feb - Jan.
I am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:
date
employee (who did the work)
team (for whom work was done)
department (for whom work was done)
type (of work done)
Project (which property worked on)
new/revision (was this a new project or revision of old)
time (spent working on this)
I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.
I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:
- This month (i.e., the 1st of the month to present)
- previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.)
- Year to date
I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112
Value 2345678910111213
And get it like:
Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.