I 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?
After survey questions and responses are in excel, how do you analyze this data using a correlation matrix and regression analysis for reporting or testing hypotheses?
I am wondering if there is way to analayse date and time data in a way that would allow me to look at time differences.
I have two columns of data, one has the date and time an entry was made onto a client database, the other shows the date and time an amendment or deletion was made.
What I would like is a formula that shows the time period difference between the two entries.
So for example if one column shows 14/02/2014 14:26 and the other shows 14/02/2014 14:28 it would show 1 (minute) as the result.
I have over 1000 entries to look at and manually it is taking a looooooooong time!
I need to know 1-Number of students that passed with 70% and above in English, Maths and Biology 2- Number of students that passed with 50%-69% and above in English, Maths and Biology 3- Number of students that scored below 50% in English, Maths and Biology 4-Number of student that where absent for each of the subjects (English, Maths, biology)
Note: Total of all the columns per subject must be equal to the total number of students in the sheet.
I have a row of text (for example say they are cells c3, d3, e3, f3) that have one of four text strings (red, yellow, green, blue). In cell b3, I need it to report back the column heading of all the cells that have the text "blue".
So if cells c3 and f3 have text "blue", then I need their column headings to be inserted into cell b3.
I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
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.
I 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.
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).
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.
I 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;
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")
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
Attached 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.
I 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 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.
