Carolina's Technical Blog

Troubleshooting Common Pivot Table Issues

2009-10-11T22:59:00.000-07:00

Symptom:

I keep getting the error "The PivotTable field name is not valid."

Hypothesis:

When you try to create a pivot table, you get the following error message:

The PivotTable field name is not valid. To create a PivotTable report,
you must use data that is organized as a list with labeled columns.
If you are changing the name of a PivotTable field, you must type
a new name for the field.

Result:

This message means that one or more columns in your data source do not have a header name. To correct this problem, go to the dataset you are using to create the pivot table and make sure that all columns have a header name.

Excel PivotTables

2009-10-11T09:42:00.000-07:00

Excel PivotTables are a powerful data analysis tool, but they are not always easy to use.

Symptom:

Response Is Slow With External Data

Hypothesis:

You're using certain external data sources For some types of external data sources, including large databases, on-sheet layout will be too time consuming. For example, a report based on a cube created with the OLAP Cube Wizard in Microsoft Query is likely to be slow to display layout changes. If you start to do on-sheet layout and data is slow to appear after you drag the fields into place, you can click PivotTable Wizard on the PivotTable toolbar to return to step 3, and then click Layout.

You need to change the page field settings Use Layout if your PivotTable or PivotChart report is based on external non-OLAP source data and you want to create a page field that retrieves data for each item separately. Learn about using page fields to work with large databases. For PivotTable and PivotChart reports that are based on source data from OLAP databases, the page field options are not available.

Procedure:

If you have not yet completed the PivotTable Wizard, but suspect that the layout process may be slow, click the Layout button in step 3 of the wizard. This step displays the Layout dialog box, which lets you define the PivotTable layout without the delays that might occur when using drag and drop after the wizard is complete.

If you have already completed the PivotTable Wizard and find that the layout process is slow, select the Always Display Items command on the PivotTable menu. This command turns off data display, letting you complete the layout without waiting for data retrieval. When the layout is complete, select the command again to turn data display back on.

Another way to speed data retrieval is to use a Page field so that only part of the data to be retrieved at once (although this is not possible with OLAP data sources). Here’s how (when using the Layout dialog box to define the report):

Drag a field onto the Page area.
Double-click the field to open the PivotTable Field dialog box.
Click the Advanced button.
Select the option Query External Data As You Select Each Page Field Item.

Result:

This technique can also help when you receiving Out of Memory or Out of Resources error messages. It is also possible to avoid these messages by selecting the Optimize Memory option in the PivotTable Options dialog box.

When you create a table linked to external data, you might run into problems with slow response in your worksheet. Sometimes error messages pop up, too. You might not be able to fix all these problems because they might occur because of problems in the external data source itself or, if the data source is remote, in the connection to it. There are a few things you can try that may make a significant difference, however.

Many of these problems arise when laying out the PivotTable after completing the PivotTable Wizard. As you drag fields onto the PivotTable, Excel tries to retrieve the data, which is a process that can be slow.

Font: Peter Aitken

Excel Sum If With Multiple Criteria

2009-09-26T21:37:00.000-07:00

There are many times that it become necessary to SUM cells based on multiple criteria. The example below will show you 2 ways that this can be done.

Symptom:

My formula works okay with just 1 criteria, but I can't get multiple criteria to work.

Range Data!C2:F11115
Criteria B5*data!c2:c11115<20000 f =" B5">

Hypothesis:

Using * as AND can't be put in criteria?

It seems the Criteria field are limited. The suggestion is adding a column with the IF condition you want to use, then sum that column.

Using sumproduct function instead use sumif.

Procedure 1:

The best way to get multiple criteria to work over a range is not to use sumif but the sumproduct function.

=sumproduct( --(criteria 1) , --(criteria 2), range to sum over)

So for your example it would be something along the lines of

=SUMPRODUCT(--(Data!C2:C11115<20000),--(data!f2:f11115=staff!b5),data!j2:j11115) value =" value." value =" 0." style="font-weight: bold; color: rgb(255, 102, 102);">

Procedure 2:

If your wanting to do a single criteria then SUMPRODUCT(--(range=criteria)) or SUMPRODUCT(N(range=criteria)) can be used, but of course you can use COUNTIF for that so using double negatives or N becomes redundant. To count items then just use all the conditions within one argument like this... SUMPRODUCT((range=criteria)*(range=criteria)*(rang e=criteria)) to achieve a COUNT or include a second argument to indicate you want to sum the result if all the criteria in the first argument is true.... SUMPRODUCT((range=criteria)*(range=criteria)*(rang e=criteria),range ) for a SUM.

Result:

Note you can have as many criteria as you like using the sumproduct function method!

You need to have the double negative and the criteria in brackets for this to work. Arrays should also be of the same size too.

When you have multiple criteria then * means AND and + means OR.

Information Technology (IT)

2009-09-24T18:42:00.000-07:00

IT is enabling fundamental changes in how the work is carried out. The activities most susceptible to change, according to Oliveira (1996), are those intensive in information and can distinguish three groups.

A first group relates to the production: physical (increasingly affected by the robotics and control instrumentation), the production of information (influenced by computers in bureaucratic tasks such as accounts receivable, accounts payable, billing, and others) and the production of knowledge (CAD, CAM, credit analysis and risk, software production, etc.).

The second group refers to coordination; telecommunications is the main instrument of change that affects the physical distance, the nature of time on the job, which stores more information and organizational memory maintained by databases.

Finally, the third group refers to the management: it affects both the direction, which allows monitoring the environment and making decisions to adapt the organization to the environment, and the control that measures performance and compares with the plans to maintain the desired direction.

The information supports the decision, as a production factor, influences the behavior of people and become an important vector because it can increase the synergy or cancel the results of all these.

Application of IT in Production

Information Technology (IT) contributes towards making logistics more efficient and effective in the generation of value within a company. As a result, there are numerous IT systems available for application in logistics. IT applications related to output operationalize the different models used in the Production Planning and Control (PPC). In this sense, the emergence of systems MRP (Materials Requirements Planning), even in 70 years, allowed to equate the problem of calculating material requirements for product mix production. MRP systems evolved into MRP II systems, which have been given greater coverage, including the aspects of planning such as capacity planning (CRP - Capacity Requirements Planning), and allowing the management of other resources (equipment, labor-intensive etc.), and materials.

MRPII systems were the state of art instrument in terms of production planning, especially in intermittent production systems. On the beginning of the 80th, come to rival the just-in-time (JIT), introduced by the Japanese to control inventory in automobile production. The next step was the inclusion of various other aspects, such as accounting, finance, business, human resources, engineering, among others. This new generation of systems were called "ERP", generically called Enterprise Management Systems (Laurindo & Mesquita, 2000).

The next evolution in IT is integrating the various stages of the supply chain, initially by now via EDI and B2B e-commerce - business to business. Porter (1999) consider the key area of PPC for companies, and classifications of the types of manufacturing must be related to the choice of information systems for PPC to be adopted.

A recent alternative for integration systems strategy is the best of breed (BoB), in which the best solutions are obtained for each part of the company and incorporated later (Light et al., 2001). It is worth quoting the application integration APS (Advanced Planning and Scheduling) with ERP systems in order to get more elaborate solutions for PPC (Wiers, 2002).

Conclusion

For some time, IT was considered a mere item of support to the organization, which did not generate any return for the business. This picture began to change from the time when IT applications were no longer just a way to automate tasks and began to contribute and enrich the entire organizational process, optimizing activities and reducing the cost of internal and external communication.

Companies have recognized the importance of these investments to optimize their efficiency levels and increase their competitiveness, but in general, IT alone is not able to generate profits for business. To provide effective results, we need systems and solutions that are directly associated with an organizational goal. In this sense, the technological capability influences the strategies of the organization to structure a relationship to the dynamic nature of adding value to the various organizational practices.

In conclusion, IT is permeating the value chain in each of its points, transforming the way activities are performed and the nature of the interconnections between them. IT is also affecting the competitive scope and reshaping the way products and services meet customer needs. These basic effects explain why IT has acquired a strategic significance, and differs from many other technologies used in business.

Information System for Distribution Center : a Case Study

2009-09-17T18:15:00.000-07:00

Logistics technology can considerably improve collaboration and provide effective management of supply chain by providing efficient flow of promotional plans and real-time load data, thereby allowing the use of collaborative logistics to lead to the achievement of cost reduction and efficiency in the supply chain.

ABSTRACT

The difficulties in Information Systems Management have created ongoing demands for information systems to improve the performance of business activities. Thus, the appropriate use of Information Technology becomes essential in virtually all aspects of collaboration in the supply chain. Electronic Data Interchange - EDI has made an important contribution in the integration of logistics activities. Therefore, this article aims to describe the use of EDI as a key tool in managing the internal logistics operations company in the construction and management with external partners, through a case study. The literature presents concepts related to Collaborative Logistics, Information Technology and EDI. Among the results achieved in the logistics of the company are described the use of EDI as well as advantages and disadvantages obtained after its implementation. Besides these, I highlighted the benefits of using EDI, such as faster business transactions, reduction in inventory, improved physical distribution of materials and information, effective customer service and greater integration among the members of the supply chain.

Keywords: Collaborative Logistics, Information Technology, EDI.

For further information on my research, send e-mail To: carolinapimenta@gmail.com

STORAGE MANAGEMENT IN A SMALL SUPERMARKET

2009-09-17T17:02:00.000-07:00

Understanding what is meant by strategic supply and how to implement supply strategies is central to unlocking value within the supply chain.

My research domains are in the areas of logistics & supply chain management and manufacturing systems & management, focusing in the system of controlling and localization of stock.

ABSTRACT

The reduction of storage cost, associated with better work conditions are important factors that should be evaluated when introducing the system in a vertical style and also in a store management. The main theme of this work is the storage management, focusing in the system of controlling and localization of stock. In order to make it happen, some steps were considered: evaluations in the conditions of controlling the stock; measurement of storage spaces to sketch the lines of storing products and proposal of a localization patterns of the products. Besides the literature read about the subject, a study was developed in a supermarket located in Zona da Mata Mineira, in which data was collected through interview, document survey, and a non-participant observation. After making up the present storage system used in the company, an analysis was done to verify which system of locating and controlling is more effective to the company in order to optimize the present results of the storage process. To conclude a new layout proposal was made, as well as a controlling system of storage and a new pattern of storing the products.

Keywords: Logistics; Storage; Retail.

VIEIRA et al. / Revista P&D em Engenharia de Produção No. 8 (2008) p. 57-77

For further detail of my research click here.

Problem with Excel, the VLOOKUP() function

2009-09-15T13:46:00.000-07:00

Symptom:

I have a lookup formula on one tab that references data on another tab. First cell that has lookup formula works correctly. Next formula down the column returns wrong value. Below are the formulas.

Raw gas Lift Volume

=LOOKUP(A2,'April Data'!B$2:B$28,'April Data'!E$2:E$28)
=LOOKUP(A3,'April Data'!B$2:B$28,'April Data'!E$2:E$28)
=LOOKUP(A4,'April Data'!B$2:B$28,'April Data'!E$2:E$28)

Hypothesis:

1 - The third argument of the VLOOKUP function is a number (or a calculation that returns a number) representing the column number from within the table that data is returned. You have a range reference ('April Data'!E$2:E$28) as your third argument instead a number for the column.

2 - If this data is text then you should use a fourth option argument, FALSE, in the VLOOKUP. If the data is numerical then the reference table needs to be sorted in ascending order or the VLOOKUP will return incorrect results.

Procedure:

Here is what the first few rows of the spreadsheet tab that has the vlookup formula on it looks like. I tried to put in some row and column references. This tab is labeled apr adj.

The lookup formula in cell B2 tries to find a match for the data in cell A2 (1-4) (tab apr adj) on the tab April Data in the range B2:B28 and return the corresponding value from the range E2:E28. It returns the correct value of 11160.

The lookup formula in cell B3 tries to find a match for the data in cell A2 (2-4) (tab apr adj) on the tab April Data in the range B2:B28 and return the corresponding value from the range E2:E28. It returns the incorrect value of 5487 rather than the correct value of 4650.

I thought that the third argument of the vlookup function could be a range. The help screen in Excel for the vector form of lookup shows the following:

LOOKUP(lookup_value,lookup_vector,result_vector)
Would ('April Data'!E$2:E$28) not be a result vector?

The lookup_value (tab apr adj cell A2 etc)and lookup_vector (tab april data range B2:B28) both contain general type data. The result_vector data (tab apr adj range E2:E28) is numeric. The range of data that is being searched is sorted by the lookup_vector.

Result:

Assume you have data in A1:B5. A1 has the first well, with a name of "1-4". Next to that well name in B1 is its volume. A2 has the next well "name" of "2-4" and in B2 is its volume, etc.

Assume further that you type a well name of "1-4" in D1 and in E1 you want to return that well's volume. Here is the formula:

=VLOOKUP(D1,$A$1:$B$5,2,False)

The number 2 represents which column from the table ($A$1:$B$5) to get the data from when there is well name match.

Learning Model LDSBC

2009-09-15T10:37:00.000-07:00

The Learning Model was recently introduced as the tool to provide a spirit-centered framework for learning and teaching.

Describing the Learning Model, College President J. Lawrence (Larry) Richards said, “The model combines what prophets and scriptures have said about learning and teaching. It produces desired outcomes that help students become gospel centered, competent in lifelong learning with go-to-work-right-now skills, and confident in their ability to build families and serve in the Church and community.”

Implementation of the Learning Model places an emphasis on actual learning experiences that are shared. For example, when the emphasis is placed on the “Prepare” portion of the model, students accept responsibility for being prepared to learn. In the section about “Teach One Another,” students move from passive to active involvement in the classroom experience. In the “Ponder and Prove” section, students consider implications, life applications, and demonstrate skills-based competencies.

The Learning Model has the power to change the way students learn, and to accelerate their comprehension, because teachers and students invite the Spirit into the process. Students learn more when they teach. Teaching allows them to act and not be acted upon. And action authorizes the Holy Ghost to teach,” President Richards explained.

How to use nested IF statments in Microsoft Excel

2009-09-08T23:10:00.000-07:00

IF function is one of the most useful functions available in Excel. By introducing decision making to a spreadsheet, the IF function test if a certain condition is true or false. It is possible to Nest or place one IF function inside another, so we can increase the spreadsheets' flexibility.

The basic form of the IF function is:

=IF(logic test, value if true, value if false)

Nesting IF functions increases the number of possible outcomes.

For example, let's work on an Hypothetical Savings Plan Deduction. The higher the income, the higher the deduction rate. We can use an IF function to determine what the deduction rate will be.

For this example, if the income is:

less than $16,400, the deduction rate is 12%
greater than or equal to $16,400, but less than $68,350, the deduction rate is 17%
greater than or equal to $68,350, the deduction rate is 25%

The first deduction rate is handled by the logic test and the value if true argument of the first IF function. To do this, we write the beginning of the IF function as:

=IF(A5<16,400,A5*15%)

To add the second and third deduction levels, we nest one IF function inside another. For example:

=IF(A5<16,400,a5*12%,if(a5<68,350,a5*17%,a5*25%))

The logic test of the Nested IF function, checks to see if the income is greater than or equal to $16,400, but less than $68,350. If it is, the deduction rate is 17%. If the income is greater than or equal to $71,950, the deduction rate is 25%. Additional rate changes could be added another nested IF functions inside the existing function.

Just remember that limit of seven nested IFs though, because there is no way to increase this using an IF statement. However, when you really need more than eight conditions, there are other functions that will serve you better than Nested IFs. One set of functions are the LOOKUP functions.

Creating multiple users

2008-10-07T16:02:00.000-07:00

Multiple users at once
To add multiple users at once to the operating system simply use the Filewritefields() script function to write multiple Net User command lines into a Batch file. Once you have written the batch file you can execute it by running the WWDOSCommand() script function.

FOR increment =0 TO 10

Uname = "TestUser"+StringFromIntg(increment,10);LogMessage(Uname);FileWriteMessage( "c:\NewUser.Bat", -1, " Net User "+ Uname + " password /add", 1 );

NEXT;

ReturnValue = WWDosCommand( "c:\Newuser.bat", "maximized" );

The above script will create a Batch file called NewUser.bat. This file will look like:

Net User TestUser0 password /add
Net User TestUser1 password /add
Net User TestUser2 password /add
Net User TestUser3 password /add
(....)
Net User TestUser1o password /add

This file, once executed using the WWDOCCommand() function, will create 10 new users (TestUser0 thru TestUser10 all with a password of Password) on the local machine.

Creating multiple user in AD

You can use csvde -i filename.csv before that prepare the list of user LDAP attributes in the csv file like sAMaccountname, firstnam, lastname, password firstname - fn lastname -ln password - -pwd -i - this switch imports objects to active directory database if you want to know the LDAP attributes first export some objects from AD using csvde -f filename.csv and then you will get a clear picture of what to do now you can add your new user ids in the specified format and using the import switch you can import the object to AD

Multiple commands in batch file loop
In a batch file is possible to execute more than one command for each iteration of a FOR loop (i.e. more than one command for each file of the set):

Create a secondary batch file that processes each file and call it like this: for %f in (*.txt) do call process.bat %f=process.bat=@Echo OffCommand1.exe %1Command2.exe %1etc...

Best to include the PowerShell equivalent: ;-)gci *.txt % {Command1.exe $_.FullName; Command2.exe $_.FullName}

About me

2008-09-09T17:11:00.001-07:00

Hello!

I'm from Brazil.