Follow formula can be used in Excel when conduct the annual projection to avoid possible lack of sufficient data which may cause various format errors, such as divided by 0.
Assume annualized project is based on year to date information, in comparison to last year’s year to date number together with the last year’s summation, it can achieve the seasonal adjustment by using simple strait-line method:
A1-A12: Monthly numbers of last year
B1-B12: Monthly numbers of current year
C1: The current month number within a year, for instance, June is 6 in 12 months.
A13: Last year’s Year-to-date =SUM(A1:INDIRECT(ADDRESS(ROW(A1)+C1-1,COLUMN(A1))))
A14: Last year’s summation = SUM(A1:A12)
B13: Current year’s Year-to-date.
B14: Current year’s seasonal adjusted annualized projection = ISNUMBER(ROUNDUP(B13/A13*A14,-2),0).
The formula is: =MAX(ISNUMBER(ROUNDUP(A13/C1*12,-2),0),B14)
Can also use ROUND() or ROUNDDOWN() function.
Basically, it is to determine if seasonal adjustment should be used based on the validity of the data. The formula can guarantee a valid projection number will be generated.
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
How to Upload Data to ArcEditor
Save data in Excel file. Open ArcCatalog, connect to that folder, find the table/worksheet in Excel file, right click it, click Create Feature Class, click From XY Table, a pop-up window Create Feature Class From XY Table will be shown. Select with XY columns and select right folder and file name for new SHP file destination, then click OK.
If it does not show on the folder, right click the folder to refresh it.
In ArcEditor, menu Windows, select Table of Contents. In the layer you want to add, right click it, click Add Data, and then upload the SHP files you have just created.
Done.
If it does not show on the folder, right click the folder to refresh it.
In ArcEditor, menu Windows, select Table of Contents. In the layer you want to add, right click it, click Add Data, and then upload the SHP files you have just created.
Done.
Excel File as Email Attachment for Reporting Services
The scenario is data are pulled out from SQL Server through a rather complicated query without use temporary table, and then copy the dataset to Excel. There are some presentation formats needed in Excel. This setting is the result from requirement since the report readers want it in Excel with live data.
First, using SQL Server Management Studio to arrange SQL Server side work, though it is not necessary. Create a SQL file, organize the SQL query, make sure no temporary table used, and the result should be a single dataset. Complicated formatting as well as query logic should be arranged within a single query by using inner queries as well as WITH AS statement.
If one dimension of original dataset from SQL Server is dynamic, make sure it would be the records dimension (vertical) rather than field dimension (horizontal). This is particularly important because if you have a dynamic horizontal dimension, any change would need human intervention by changing SELECT CASE part in query. You would need to make it as automation as possible. For details how this is work, please refer to http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html. If the dynamic dimension is to be at vertical direction in final report, you can write a special stored procedure to do the transpose transformation, or in Excel, using its built function. By adding CREATE VIEW AS, create a view which sits inside of SQL Server. SQL Server side work has been done.
In Excel, organize the presentation worksheets and dedicate a special worksheet for internal use. All live data including report covering period and report generating date time are to be organized in this internal sheet. Next step is to arrange the automation get external data directly from the view in SQL Server. Click Data in menu, Get External Data, From Other Sources, From Data Connection Wizard, Other/Advanced, A popup window will show. In Provider tab, select SQL Server Native Client 10.0, Next, make a right selection in Select or enter a server name, click Use Windows NT Integrated security, make a right selection in Select the database, then click OK. Finish it. Make sure the dataset from SQL Server will be arrived at right position of that internal worksheet.
Next is to create a button and write a VBA script to complete following tasks:
1. Make a transpose copy of dataset within the internal sheet, if it is necessary.
2. Copy whole or partial dataset to presentation worksheet by using Paste Values. Make sure there is no function in presentation worksheet at all. This does not only show internal works to the end users, but also make readers easily use the data as static.
3. Copy header and footer dynamic data to presentation worksheet by using Paste Values. Make sure this part is also of static data.
4. Hide the internal worksheet. If it is necessary you can also protect the hiden sheet by set password. All you may wish to completely delete the internal worksheet.
5. Automatically send email and attach this Excel file.
VBA Example:
Sub Process()
Sheets("Internal Use").Select
ActiveWorkbook.RefreshAll
Range("A5:AW68").Select
Selection.Copy
Range("B70").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Range("D1").Select
Selection.Copy
Sheets("Totals").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False
Sheets("Internal Use").Select
Range("A3").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWorkbook.SendMail
Recipients:="someone@somewhere.com", _
Subject:="Weekly Report "
End Sub
First, using SQL Server Management Studio to arrange SQL Server side work, though it is not necessary. Create a SQL file, organize the SQL query, make sure no temporary table used, and the result should be a single dataset. Complicated formatting as well as query logic should be arranged within a single query by using inner queries as well as WITH AS statement.
If one dimension of original dataset from SQL Server is dynamic, make sure it would be the records dimension (vertical) rather than field dimension (horizontal). This is particularly important because if you have a dynamic horizontal dimension, any change would need human intervention by changing SELECT CASE part in query. You would need to make it as automation as possible. For details how this is work, please refer to http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html. If the dynamic dimension is to be at vertical direction in final report, you can write a special stored procedure to do the transpose transformation, or in Excel, using its built function. By adding CREATE VIEW AS, create a view which sits inside of SQL Server. SQL Server side work has been done.
In Excel, organize the presentation worksheets and dedicate a special worksheet for internal use. All live data including report covering period and report generating date time are to be organized in this internal sheet. Next step is to arrange the automation get external data directly from the view in SQL Server. Click Data in menu, Get External Data, From Other Sources, From Data Connection Wizard, Other/Advanced, A popup window will show. In Provider tab, select SQL Server Native Client 10.0, Next, make a right selection in Select or enter a server name, click Use Windows NT Integrated security, make a right selection in Select the database, then click OK. Finish it. Make sure the dataset from SQL Server will be arrived at right position of that internal worksheet.
Next is to create a button and write a VBA script to complete following tasks:
1. Make a transpose copy of dataset within the internal sheet, if it is necessary.
2. Copy whole or partial dataset to presentation worksheet by using Paste Values. Make sure there is no function in presentation worksheet at all. This does not only show internal works to the end users, but also make readers easily use the data as static.
3. Copy header and footer dynamic data to presentation worksheet by using Paste Values. Make sure this part is also of static data.
4. Hide the internal worksheet. If it is necessary you can also protect the hiden sheet by set password. All you may wish to completely delete the internal worksheet.
5. Automatically send email and attach this Excel file.
VBA Example:
Sub Process()
Sheets("Internal Use").Select
ActiveWorkbook.RefreshAll
Range("A5:AW68").Select
Selection.Copy
Range("B70").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= False, Transpose:=True
Range("D1").Select
Selection.Copy
Sheets("Totals").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False
Sheets("Internal Use").Select
Range("A3").Select
ActiveWindow.SelectedSheets.Visible = False
ActiveWorkbook.SendMail
Recipients:="someone@somewhere.com", _
Subject:="Weekly Report "
End Sub
A Microsoft Usability Bug
in Excel, sometimes the function does not show the calculation result, instead it shows the formula itself. To correct it, one would need to go to Format Cells, change from Text datatype to numerical datatype, such as General.
Technically it is not a bug. In terms of usability, it is. This is because when you change from numerical datatype to text, the calculation still work, until you touched cell, the formula showed up. After that, you won't be able to figure out what went wrong.
http://www.excelforum.com/excel-general/562740-formula-does-not-calculate.html
Technically it is not a bug. In terms of usability, it is. This is because when you change from numerical datatype to text, the calculation still work, until you touched cell, the formula showed up. After that, you won't be able to figure out what went wrong.
http://www.excelforum.com/excel-general/562740-formula-does-not-calculate.html
Koncord Applied Excel Functions
We have uploaded some applied Excel functions. These are some basic functions, but very much useful in day-to-day working. This Excel file include some functions of string parsing, dates, telephone number parsing, and coordinates parsing.
Following is the link:
Koncord Applied Excel Functions
Following is the Terms of Services of Koncord Partners: http://koncordpartners.blogspot.com/2010/06/terms-of-services.html
Following is the link:
Koncord Applied Excel Functions
Following is the Terms of Services of Koncord Partners: http://koncordpartners.blogspot.com/2010/06/terms-of-services.html
Date Format
People in United States use date format of mm/dd/yyyy. British, Australia and New Zealand use dd/mm/yyyy. Format of yyyy-mm-dd is an ISO 8601 international standard, and is favored by European countries and all Chinese spoken countries. Canada is THE country currently using all of these three formats. Format of yyyy-mm-dd is official format for Canada. Because they are a Commonwealth member country, they also use British format. And because they are heavily influenced by United States, in day-to-day life Canadians use American format most.
It is for sure IT industry should use yyyy-mm-dd format, not because it is an ISO 8601 standard, but also it is only meaningful format when you conduct textual sorting. For instance, when you use date as part of file name, it can be directly sorted by Windows Explorer.
In Microsoft Excel, if yyyy-mm-dd format is needed, please go to Format Cells, chose Locale as Chinese (Hong Kong S.A.R). It can be found in Type.
Sunday is traditionally regarded as the first day of the week. Different industries may have their own standard. However, the International Organization for Standardization (ISO) specifies that the week begins with Monday, which is also the standard for IT industry. So, day 1 in a week is Monday. The week ends at 23:59:59 of Sunday. Beware, Microsoft Excel's function weekday() does use Sunday as day 1 in a week. In addition, in Microsoft SQL Server DATEPART(WEEKDAY, dateToCheck) does also generate 2 for Monday.
Look at following three times:
Time 1: 2000-01-01 11:59:59 AM
Time 2: 2000-01-01 12:00:00
Time 3: 2000-01-01 12:00:01 PM
How many seconds between Time 1 and Time 2 above? How many seconds between Time 2 and Time 3? Are you sure? Would you think again, please? We human being just would like to confuse ourselves. Then look at these:
Time a: 2000-01-01 11:59:59 PM
Time b: 2000-01-01 0:00:00
Time c: 2000-01-01 0:00:01 AM
Time d: 2000-01-01 0:59:59 AM
Time e: 2000-01-01 1:00:00 PM
Time e: 2000-01-01 1:00:01 AM
So, if it is possible, please use military time (24 hour time system) instead of 12 hour time system.
Look at following three times:
Time 4: 2000-01-01 00:00:00
Time 5: 2000-01-01 24:00:00
Time 6: 2000-01-02 00:00:00
Are they at same time? Indeed, the format of Time 5 would never exist in any official document, except in some railway time tables, where 24:00:00 used only to describe the arrival time. Time 4 and Time 6 are often confused, though there is 24 hours difference. However, for the purpose of reporting period ending time, it is suggested to use format either as 1999-12-31 24:00:00 or 1999-12-31 23:59:59, but not 2000-01-01 00:00:00. This is particularly important for week report, because the reader could not tell from the glance at the date. For instance, 2010-04-12 00:00:00, how can you directly tell it is one second from 2010-04-11 00:00:01 or 2010-04-12 23:59:59? No, both are incorrect. The correct answer is it is one second from 2010-04-12 00:00:01 or 2010-04-11 23:59:59.
When the first week starts in a year? ISO 8601 provides the first week in a year starts from a Monday of a week with includes the first Thursday in that year. Therefore, the first week must include the January 4th. This approach does indeed emphasis the majority of the days in first week are in new year. This approach can also be used to determine the first week of a fiscal year as well as the first week of a month.
http://en.wikipedia.org/wiki/Date_and_time_notation_by_country
http://en.wikipedia.org/wiki/Calendar_date
http://en.wikipedia.org/wiki/ISO_8601
http://24hourtime.info/the-24-hour-time-system/
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/12-hour_clock
It is for sure IT industry should use yyyy-mm-dd format, not because it is an ISO 8601 standard, but also it is only meaningful format when you conduct textual sorting. For instance, when you use date as part of file name, it can be directly sorted by Windows Explorer.
In Microsoft Excel, if yyyy-mm-dd format is needed, please go to Format Cells, chose Locale as Chinese (Hong Kong S.A.R). It can be found in Type.
Sunday is traditionally regarded as the first day of the week. Different industries may have their own standard. However, the International Organization for Standardization (ISO) specifies that the week begins with Monday, which is also the standard for IT industry. So, day 1 in a week is Monday. The week ends at 23:59:59 of Sunday. Beware, Microsoft Excel's function weekday() does use Sunday as day 1 in a week. In addition, in Microsoft SQL Server DATEPART(WEEKDAY, dateToCheck) does also generate 2 for Monday.
Look at following three times:
Time 1: 2000-01-01 11:59:59 AM
Time 2: 2000-01-01 12:00:00
Time 3: 2000-01-01 12:00:01 PM
How many seconds between Time 1 and Time 2 above? How many seconds between Time 2 and Time 3? Are you sure? Would you think again, please? We human being just would like to confuse ourselves. Then look at these:
Time a: 2000-01-01 11:59:59 PM
Time b: 2000-01-01 0:00:00
Time c: 2000-01-01 0:00:01 AM
Time d: 2000-01-01 0:59:59 AM
Time e: 2000-01-01 1:00:00 PM
Time e: 2000-01-01 1:00:01 AM
So, if it is possible, please use military time (24 hour time system) instead of 12 hour time system.
Look at following three times:
Time 4: 2000-01-01 00:00:00
Time 5: 2000-01-01 24:00:00
Time 6: 2000-01-02 00:00:00
Are they at same time? Indeed, the format of Time 5 would never exist in any official document, except in some railway time tables, where 24:00:00 used only to describe the arrival time. Time 4 and Time 6 are often confused, though there is 24 hours difference. However, for the purpose of reporting period ending time, it is suggested to use format either as 1999-12-31 24:00:00 or 1999-12-31 23:59:59, but not 2000-01-01 00:00:00. This is particularly important for week report, because the reader could not tell from the glance at the date. For instance, 2010-04-12 00:00:00, how can you directly tell it is one second from 2010-04-11 00:00:01 or 2010-04-12 23:59:59? No, both are incorrect. The correct answer is it is one second from 2010-04-12 00:00:01 or 2010-04-11 23:59:59.
When the first week starts in a year? ISO 8601 provides the first week in a year starts from a Monday of a week with includes the first Thursday in that year. Therefore, the first week must include the January 4th. This approach does indeed emphasis the majority of the days in first week are in new year. This approach can also be used to determine the first week of a fiscal year as well as the first week of a month.
http://en.wikipedia.org/wiki/Date_and_time_notation_by_country
http://en.wikipedia.org/wiki/Calendar_date
http://en.wikipedia.org/wiki/ISO_8601
http://24hourtime.info/the-24-hour-time-system/
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/12-hour_clock
Subscribe to:
Posts (Atom)
Posts
