Counting Monthly Data

Jan 31, 2014

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

View 7 Replies


ADVERTISEMENT

Counting Filtered Data And Matching To Specific Monthly Headers

Apr 2, 2014

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.

View 3 Replies View Related

Pulling Data Into Two Columns Labeled “Monthly” & “Non-Monthly”

Aug 3, 2009

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

View 9 Replies View Related

Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total

Mar 6, 2013

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

View 1 Replies View Related

Formula To Convert Monthly Data To Weekly Data

May 8, 2014

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.

View 11 Replies View Related

Add Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart

Apr 28, 2009

I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.

View 4 Replies View Related

Quarterly Data To Monthly

Jan 27, 2014

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?

View 2 Replies View Related

Monthly Analysis On Data

Sep 29, 2007

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.

View 3 Replies View Related

Converting Monthly Data Into Daily Data?

Jan 27, 2014

Basically i have month end data ranging from 31/01/2000-31/01/2009 with a value attached to each. I need to convert this data into a daily series with the month end value being the same throughout the whole month. I've been playing around with excel for a couple hours tonight

View 9 Replies View Related

Formula For Monthly Data Into Quarters?

May 25, 2014

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

View 2 Replies View Related

How To Add Monthly Sales Data Using Formula

Dec 29, 2013

How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.

mockup.xlsx

View 5 Replies View Related

Turning Monthly Data Into Daily?

Jul 26, 2014

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

View 3 Replies View Related

Count Data Monthly Automatically

Dec 3, 2012

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.

View 14 Replies View Related

Monthly Average From Daily Data

Oct 28, 2011

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.

View 7 Replies View Related

Data Finding On Monthly Update

Sep 4, 2008

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.

View 9 Replies View Related

Calculate Monthly Sales Based On Data Given?

Jun 9, 2014

How to calculate the monthly sales based on the data given ?

View 4 Replies View Related

Daily Data Conversion To Monthly Totals

Sep 9, 2013

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

View 9 Replies View Related

Compiling Monthly And Annual Data On One Sheet

May 17, 2009

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.

View 8 Replies View Related

Given Weekly Data Coming Up With Monthly Amounts

Sep 5, 2009

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

View 9 Replies View Related

Graph & Chart: Data Source Changes Monthly

Nov 19, 2006

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.

View 3 Replies View Related

Computing Monthly Standard Deviation From Daily Data

Apr 7, 2014

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?

View 9 Replies View Related

Monthly Total Values For Date Sorted Data

Jun 17, 2014

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.

View 5 Replies View Related

Automatically Create Monthly Totals From Daily Data

May 20, 2014

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 Related

Automatically Create Monthly Totals From Daily Data

Jan 2, 2009

In 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

View 6 Replies View Related

Extracting User-driven Monthly Data Into A Graph

Jan 14, 2013

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‎

View 2 Replies View Related

How To Get Monthly KWh Usage When Bill Data Spans Months

Jun 8, 2013

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?

View 3 Replies View Related

12 Monthly Data Values To Populate Series Of 365 Days

Jan 23, 2014

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?

View 8 Replies View Related

Automatically Split Weekly Data Into Monthly Totals

Mar 13, 2012

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?

View 2 Replies View Related

Save Monthly Data To New Workbook Named As Month

Apr 13, 2008

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.

View 9 Replies View Related

Calculate Monthly Standard Deviation From Daily Data

May 11, 2008

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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved