I have a large set of daily rainfall and evaporation data (see attached sheet) which I would like to sum into monthly data. I have previously been doing this in Access, can anyone show me a quick way to do it in excel?
View 3 RepliesI 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 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 RelatedI 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).
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.
PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.
We have a workbook that we create each month that has one worksheet per day of the month, labeled 12_01_2011, 12_02_2011, 12_13_2011...etc. The pages are an empty template with formulas and fields in place that we simply copy and paste the results of an SQL query into. Presently, we are copying the page manually several times over, and then manually renaming the pages with the new dates for the upcoming month.
So, here is my question. Macro that I might use that would:
1) Make a copy of the template for each day of the month.
2) Label each page in sequence with the dates for the upcoming month.
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
I have a two rows of data one containing names and the other containing corresponding numbers. The names are static and the numbers change on a daily basis. I want to be able to copy the numbers to a static table next to each name on a daily basis (so I can see what the value was a few weeks ago).
Is there anything I can write to do this job?
My thinking was to set a vlookup to grab the data but i'm not sure how this would work because the vlookup would change daily when the numbers change
I have attached a tiny part of a massive data set I am working on. As you can see in column 2, the data is roughly every 15min for 5 days. The data I am interested in averaging is color coordinated in column 3 (if you scroll down you can see a different color for each day's data set.)
In column 5 the dates are summarized into days as opposed to the 15min breakdown. In column 6 is the problem. How do I get the averages of the relevant data in column 6 in such a way that I can drag the formula down and the next cell will automatically calculate the average for the NEXT day, REGARDLESS of how many temp readings there are, as this data fluctuates from day to day.
Excel Problem 2.xlsx
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 RelatedIs 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
Column A = Date
Column B = Numbers
Column C = results
As you can see Column C, I manually did what I want the formula to do, which is get last number from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays).
I am trying to create a macro that would copy three cell values - Date, Amount, Rate (from Sheet1) and paste them into Sheet2 as values.
In addition, this macro will be run for each business day's file, which only has the 3 values only as of that date; I want Sheet2 to be updated on a daily basis with historical data from past days (when the macro was run) and pasted as values.
For example, if i run the macro today, it should copy Date, Amount & Rate from Sheet1 ('Data' tab in sample.xls) and paste them as values in the next row after yesterday's data in Sheet2 ('Historic data' tab in sample.xls).
Sample attached; what is the best way to do this?
I have used the "Import External Data-Web Query" to gather financial data.
This data is updated daily by the web site. The data fills up columns A2 to E 6000.
The data on Column B is of importance and I need it to be stored daily. I need a code that will store todays Column B data in column F, tomorrows Column B data in Column G, dayafter's column B data in Column H and so on..
In short, I need to create a database automatically..
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.
I have a spreadsheet that I am using for budgeting and I want to be able to track my savings by logging data from one cell.
If cell A1 represented my savings I want that copied to B1 then the next day when A1 changes the new value would be copied to B2 and so on.
Any way that I have tried so far B1 always gets overwritten when the new value comes along.
I'm trying to create a macro to loop through daily one minute data.I believe the flowchart would be something like:
Create Variable
For each day in recordset
Loop through each minute record
Run system rules
Copy to Seperate worksheet
End
Additional Info:
Data is in columns B-G (Date,Time,Open,High,Low, Close)
Sample system could be something like:
If Current record close price is > Past 2 records Close Price Then
Buy 100 Shares
Liquidate if poistion moves against by 10%
Take Profit if position increases by 5%
Else close by days end
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
=IF((C26-B26)/B26
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.
View 7 Replies View RelatedI have a spreadsheet that contains developer related data. I want to be able to do a weekly analysis of average construction time and costs. The problem I am running into is properties enter rehab phase and exit rehab phase throughout the week.
For example:
I want to know the average $ spent and total rehab time week 1 of april. I may have 4 homes that entered rehab that week and 3 that move out of rehab that week. Moreover, I need to ensure those homes that entered and are still in rehab for that week are counted. How do I accomplish this feat?
I have daily Open, High, Low, Close stock prices. Separately, I need the same data but in weekly format, so:
1. The weekly open is the open of the first working day.
2. The weekly high is the highest of each of the daily highs.
3. The weekly low is the lowest of each of the daily lows.
4. The weekly close is the close of the last working day.
How can I do this using formulas?
OHLC sample.xlsx
I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).
I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?
I have two columns.
A column = contains dates but does not always have 5 days in a week. Holidays are not entered.
B column = price data for each day
All I want to do is get the highest price from the previous week. So for example last week highest price was 5000 then column C will display 5000 for this entire week. I tried using WEEKNUM and WEEKDAY but i am clueless on what to do after that. I'm trying to avoid macros or VB since im not that advance with that. But if I have to I will.
I have daily production sheets (Excel sheets) that I fill out every day indicating the day's production totals. The files are named by the day's date. For example today's sheet (4/24/13) would be 042413.xls. However, I would like to be able to pull certain pieces of information into another separate spreadsheet. For example, my boss asked me how many of a certain product we made over the last month. So, I'd like to pull the product info, the date made, and quantity made into another sheet. And I'd like to be able to do this automatically for a date range that I specify.
View 8 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 a daily data dump that starts at the beginning of 2012 up to the current date. I have an executive view that allows the user to see metrics(in a data graph) from the current date back to a date selected from a drop down menu. I would like to have it so they can choose what the end date will be as well as the start date. I have tired using offset with very little luck. Is there a way I can accomplish this task with VBA? Everyday a new row of data (for the previous date) is added to the front sheet. So row 1 is a header row and then row 2 is 01/01/2012, row three 01/02/2013, etc.
View 7 Replies View RelatedI have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
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?
I have create an excel formula that I need to input data daily.
Every day, I need to insert a line and input my new data.
Below is my formula
=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))
The problem is that when I insert a line. Most of my variable will change according except C60:D154
As you can see below
V159 change to V160 = This is good
C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?
=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))