I know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
View 9 RepliesI have created a population pyramid and want to add another chart on the secondary axis which I can do. The part which I seem not to be able to complete is getting the line chart secondary axis to read from the primary y axis. It seems to add a new axis, even when I delete this the line chart does not read correctly.
View 1 Replies View RelatedI have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
Attached is an example : Budget Example.xlsx‎
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
View 3 Replies View RelatedI am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
View 1 Replies View Relatedi have the following table of information
Year DOB
7 01.09.96 -31.08.97
8 01.09.95 -31.08.96
9 01.09.94 -31.08.95
10 01.09.93 -31.08.94
11 01.09.92 -31.08.93
and a list of dates i need to look up the date to see which year it falls in and return the year
DATE function won't return TODAY()'s year in the "year" slot.
Is there a way convert, for example, 2/8/1963 to 2/8/2009 without using Concatenate?
1. I need to convert a year into a decimal year ie. 1830 into decimal year (I don't have a month, just year)
2 Year/month into decimal year/month
I just not sure what to do, is the year stored as a number/text/date. What should it even look like? Does 1830 display as 1830.00 using excel.
I'd like to know a formula which can calculate the moving annual total, that is the sum of the last 4 quarters. Now every time the sales from a quarter is known, i have to recalculate the MAT mannually.
View 2 Replies View RelatedI want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies View RelatedI have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
I have to illustrate that the average life of a set of accounts is 6 weeks. I figured that the best way to add significance to this length of time is to show in either a pie, column or bar chart the relevancy of 6 weeks within a 52 week year.
Sounds fine, right? Well, I went blank after that. I couldn't think of how to set up my worksheet in order to generate a chart of this nature.
So, I guess I'm wondering how you guys would set up a table that could then be used as the source for either of the aforementioned charts.
I have a set of data with values and dates (call the dates 'raw dates'). I have added 3 columns with formulae in them as follows:
Year =YEAR('raw date')
Month =TEXT('raw date',"mmm")
Day ='raw date', formatted as "d".
I have a pivot chart with three Axis Fields. In order they are: Year, Month and Day. This is so that the chart itself has three layers of x-axis names (year, month and day) rather than a whole load of dates which looks messy.
I would like to show the values as a running total "in" the raw dates of the data.
When I show the values as "Running Total in" 'raw dates', I get a load of #NAs in the pivot table because the raw dates are not in the Axis Fields. If I select Running totals "in" the Days (which is included in the pivot table setup), I get running totals over each month, starting at zero on the first date in each month. I need to have the running total run over the course of the whole data set, not mini running totals in each month.
When I choose only the 'raw dates' in the axis fields (i.e. taking out Year, Month and Day), running total works fine. The only thing is that I lose the nice looking x-axis. Adding the raw dates to the other 3 Axis Fields also doesn't work - it doesn't even try to give a running total at all, just the original values.
how to achieve a running total without sacrificing the x-axis?
How to get only MONTH' YEAR if there is a DATE-MONTH-YEAR in a cell?
A1: 27-July-2012
Answer D1:JULY' 2012
Pl note "' " is suffixed after JULY (the month).
I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").
I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).
The statement I had was:
=if(A1>=50,1900+A1,2000+A1)
I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.
It seems like it should be pretty simple, but I don't know how to word it in a macro.
Starting Column Example:
12
13
14
99
11
[Code] ......
For some reason I can't get rid of the borders...
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
View 9 Replies View RelatedI have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.
Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).
Example:
98 --> 1998
99 --> 1999
00 --> 2000
01 --> 2001
I have a bar graph (horizontal) for the values say Jan 1.25 Feb 2.63 Mar -1.5. This works fine; now I would like to insert static comparison lines for the values 2 and -2, i.e. a straight valued at 2 that goes Jan to Mar and another straight line at -2 from Jan to Mar..
View 2 Replies View RelatedSerial No Search E220060926320061125420060612520070824620061026720061226820061127920061226 Excel tables to the web >> Excel Jeanie HTML 4
E - Year Month Date
I need F column as Month Date Year Format
I'm after a formula this time ... i've searched the board and can't find what i need.
a cell shows 2009 December
and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..
I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?
View 2 Replies View RelatedI have a query in excel sheet, i have an amount in a column and we want the calculate amt in next col like as...
We want add 5 % in amt in every year.
Amt
Year
Reqd Amt
10000
5
55000
[Code] .......
Example-1
1st Year
10000
B7
2nd Year
10500
B7+B7*5%
3rd Year
11000
B8+B7*5%
4th Year
11500
B9+B7*5%
5th Year
12000
B10+B7*5%
Total
55000
SUM(C7:C11)
Example-2
1st Year
15000
B14
[Code] .........
I have a copy of a year planner that calculates the days of the month and adjusts them according to the year input into the header area.
Would anyone please modify it so that the first column reads August and the last column reads July (instead of Jan to Dec) and still maintain the calculations as required?
Based on a certain customer agreement code in excel, "YES-N", then I need to add one year onto the date listed on the renew date column.
I need the result in cell 'AP'.
I'm trying to find a way so that when new data is available for 2015, I won't have the rewrite the code. Is there a way to do something like :
If rg.value= "abc"& Actual year Then
[Code].....
i have already posted this thred on general forum but didnt received the response.
[code].....
Is there an in-built function within Excel that will help me ascertain what year is next year, and what year is the year before current? I am using =YEAR(TODAY()) to ascertain what year we are currently in, but cannot figure out how to go one backwards and 1 forwards?
View 2 Replies View Related