I am trying to find a way to sum 3 months together with the data given. (See attched file). Basically a quarter report. Right now the months are in text format (Jan, Feb, Mar, etc). They can be numeric instead of text if it is easier.
View 6 RepliesI have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:
1) 18 months from the date in the cell needs to be highlighted yellow
2) 24 months from the date in the cell needs to be highlighted red
Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.
View 3 Replies View RelatedI am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.
View 9 Replies View RelatedI have 6 rows of data with 12 columns. The first row of data consists of the 12 months and the order changes with the current month -1 listed first. The first column of data is updated from a separate sheet. When the first column is updated I would like the old data to follow the month it was originally listed under until that month becomes the first month again.
View 9 Replies View RelatedI have a spreadsheet for a stock - HOT TOPIC (ticker: HOTT) and I have the daily closing prices for several years going back. If I wanted to create a table that showed which months were the worst performing months -- when it lost the most money -- how do I set that up? I am particularly focusing on June/July to see if those are the worst performing months for this stock.
Looking back say 3 years (so 2004, 2005, 2006). (Maybe year-to- date 2007 too).
I colored the cells that contain the summer months june/july/august...but i am guessing its just june/july that has the worst performance... How do I set this up?
Now I'm trying to do 12-month rolling average for my company data?
View 1 Replies View RelatedOK essentially this is my problem:
I have a daily spreadsheet, uou update for inputs and outputs each day and the spreadsheet gets to an overperformance figure (sorry if the spreadsheet is a lottle unclear, it is a simplified version)
On the first day of each month, the opening target is adjusted and you start again. The probelm is you dont know what the opening target is until roughly 21st of the month.
I created a macro that deletes the opening target and removes all the previous days before the start of the month.
The way i done this was:
Create a copy of "daily" tab, change b5 to equals c2 and copy and paste special as values. Then in d5-d58 and f5 - f58, vlookup values from old "daily" tab , and then copy and paste special as values. Then delete old "daily" tab and remname daily(2) as "daily"
What i have done is essentially created a new tab on 21st March, looking back to data only as far back as 1st Mar and the new opening bal and target is found. The macro can be used on the 21st of each month.
but...macro doesnt work, i have tried a few times and i think it is because i am vlookuping up data from a tab that i later delete...please help!! - thanks a mil to anybody that can give me a steer in the right direction.
See attached file. I have managed to work out how to update a chart automatically when a new row of data is added. However is it possible to tweak the formulas I am currently using so that it will only include 12 months previous.
For example if you add Jan in to row 26 the chart will show Feb-Jan data (Row 15-26).
Book1.xls
I've attached a chart with variance data for each month of the year. I've used an IF formula to "NA" the months with no data, so nothing shows up in the chart. However, I would like to know if it's possible to have the chart only show months with data. The IF formula gets rid of the data point but it doesn't prevent the chart from showing the month on the X axis. In the attached file I manually selected the three months with data.
View 4 Replies View RelatedI have set up a sheet recording an each individual's turnover for my business i.e., hours worked, etc and have set up a data validation table for drop down options of Jan, Feb, Mar,etc....
Rather than creating more work for each month on the worksheet, how can I enable the drop down to contain the data specific to each month?
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 data table with monthly data in columns (65 rows deep), with the months (in format dd/mm/yyyy but showing as Dec 05) running across Row 4.
I want to be able to use OFFSET to identify the current and previous 5 months, in order to dynamically chart various items in the last 6 months worth of data.
The charting bit I'm okay with, and I realise I need to assign Names for this to work, but I'm struggling with the OFFSET & date combination.
I have the following but it starts from a defined reference cell;
=OFFSET('BO Data'!$L$4,0,('BO Data'!$4:$4)-1,1,-6)
I'm trying to interrogate a sheet for specific data based on months, I can't seem to set the input to the range any help much appreciated
Mike54
Private Sub updatestats_Click()
Dim Mth As Variant
Mth = InputBox("Please enter the month you wish to analyse")
Dim AL As Integer
Dim January As Range
Dim February As Range
Dim cl As Range
Set January = Range("B4:B57")
Set February = Range("B58:B113")
'March = Range("B4:B57")
'April = Range("B4:B57")
'May = Range("B4:B57")
'June = Range("B4:B57")
'July = Range("B4:B57")
'August = Range("B4:B57")
'September = Range("B4:B57")
'October = Range("B4:B57")
'November = Range("B4:B57")
'December = Range("B4:B57")
For Each cl In Mth
If cl.Value = "Annual Leave" Then
AL = AL + 1
End If
Next cl
Cells(4, 14).Value = AL
End Sub
In the file attached there is only the data to look at, though file is little bit lager.
Now in cell G10 I've got the stock forecast in 3 months time, this stock forecast is calculated from a date rounded to the closest month (according to my criteria), then the formula looks at the reference number cell D1 to work out the formula. All the steps are in the file.
In order to be more accurate my boss and I want to use the exact date, so that delete all the round month process. My intention is to use the day of the date and then make a fraction of the months to calculate the average. Taking into account that a month have as an average of 30.4166 days.
In the file I did an example, to clarify my messy explanation.
Cells B2, C2, D2 won't be needed in order to do so.
Please have a look at the file. At the end my intention is to to such a formula or a similar one. The formula should be an automatic one when typing dates in cell A10
The result of it is in row 31 and 32.
trial.xlsx
how can I provide a chart that display as a line chart twelve months of data for the item selected starting or ending on the data that I have selected
View 4 Replies View RelatedAttached is a copy of my data and i want to find the mode of each months data without typing it out into long hand form, eg. 111122222333344444 etc. so i believe the answer will be some where around 5 for most of them, but i just don't know a formular to show it automatically on the spread sheet.
View 2 Replies View RelatedI need a formula to automatically change the summary column according to the month we are in.
Ie:
Last Months Data
Nov
Dec
Jan
Feb
1
8
1
7
4
7
9
2
9
'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?
I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))
I'm trying to filter 2 date columns to include only data containing dates within 3 months of today's date.
I see there is a data filter option for "next quarter" but not 100% sure if this covers the quarter from today's date?
I have a dataset that is as follows:
Col1 2 3 4 ...... 45
123 456 xxx xxx xxx
100 234 xxx xxx xxx
221 543 xxx xxx xxx
112 234
112 689
122 956
122 234
This goes on from left to right for 45 odd columns.
I need to copy each set i.e set 1 is 123 down to 122 to say cell a25 and then copy the next set i.e. 456 to 234 below the first set.
I need to somehow write a loop code that knows where to get the 2nd, then 3rd set, etc and copy it to the bottom of the preceeding set
get the formula for the sum of last 3 and 6 values in a row.
Attaching the sample workbook for your reference
I have a spreadsheet which works out how much per annum our company makes. The total figure is in D2.
What I need is to make E2 work out how much it is for the current month e.g. if it's September, it would work out the monthly profit for 30 days.
I need to sum data from the most recent 12 months. For example, on June 1, 2007 I want to find the total from June 1, 2006 to June 1,2007. On the 1st of the next month, July 1, 2007, I want to calculate the total from July 1, 2006 to July 1, 2007. I don't need to keep the total from month to month (on July 1, 2007, June's data is obsolete.)
I am working on yet another worksheet. To simplify the task, let's say I have to columns Date and Amount (several hundered entries). I need to be able to sum the first 60 consecutive months, that do not equal 60. Is there a way to use built-in excel formulas to do so, or would I have to create my own formula. I'm trying to stay away from a macro that is run manually.
I input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.
View 9 Replies View RelatedIs there a way to count the number of times an entree is entered in a spreadsheet.
example In row c i have dates entered
11/1
11/5
12/6
12/9
11/3
etc.
Is there a way to count the number of times that the month of dec was entered.
In column A I have dates set up in the 00/00/00 format. In cell B2 I want it to tell me how many 09s (Septembers) there are in column A.
View 12 Replies View RelatedI need to be able to calculate age in months, and round whatever remainder to the middle of the month (.5). I am calculating z-scores and percentiles for in a childhood obesity project. The DoB and Date of measurements must be converted to read anything from 142.0 to 142.9 as 142.5.
This value will be used to look up the the appropriate LMS (Box Cox, Median, and Standard Deviation) in another excel table. I have to be able to compute age in months so 142.5, 143.5, 144.5, 144.5 etc. It is just not rounding off to the nearest half month.
I am using Ron De Bruin's code for sending emails as below, where it says "your monthly totals for", I want it to insert last months month. ie. if i send the mail in March it should automatically insert February in the field
RDB_Mail_PDF_Outlook Filename, "ron@debruin.nl", "This is the subject", _
"your monthly totals for "March" " _
& vbNewLine & vbNewLine & "Regards Ron de bruin", False
I have the data for month wise in one tab. now i want to pick up the months from dropdown list... in A1 (April) B1(june)
when i m picking the months it is automatically take those 3 months data from the data tab...