Better Accuracy

Why do spreadsheets grow very large?

Often they are used as databases, and with Excel 2007 now being able to cater for a millions rows, they can hold quite a lot of data. The typical scenario is to have a worksheet with the data and other worksheets which use pivot tables and lookup functions like vlookup to analyze and report on the data. Another situation that is quite common is monthly reporting. You start off with reporting for a number of branches for one month and then you end up with reporting month after month for a year or two and you now have a very complex set of linked workbooks. e.g. 10 branches by 24 months 240 linked workbooks plus the 24 data sheets..!

Ghost rows can also bloat a workbook. Especially if it came from a very early version of excel... There are techniques to eliminate these.

Vlookup versus Index/Match Let's examine this vlookup formula. =VLOOKUP(E2,A:C,2,FALSE)

There are four parts:

1: Cell Value to use - on some versions of excel there were major issues with dashes in the value to lookup. 2: The range to look-in - Note: How we used the columns, rather than a selected range. e.g. We could have used =VLOOKUP(E2,A1:C6,2,FALSE) instead with the range A1 to C6 specified

3: The Column with the value. In this case the 2nd column of the range is specified

4: Exact or Approximate match...beware this can cause major issues! Setting this to false makes it do an exact match.

* One trick to getting values is to do an approximate match on a list with ever increasing values and it stops when the value is larger than the one it is trying to find. This enables you to use vlookup to get a value with a specific range. Often people will use the more complicated index/offset and match formula to get the same or a more accurate result. e.g. =INDEX(B:C,MATCH(E2,A:A,0),1) or =OFFSET(B:C,MATCH(E2,A:A,0)-1,0,1,1) Noting that the index/match combination is simpler and easier than the offset one. The match part looks up the column with the value and returns the row number. The index formula then uses the row number and simply retrieves the value from the column specified in the range selected. Here's an example doing it across sheets: =VLOOKUP(A2,Sheet1!A:C,2,FALSE) =INDEX(Sheet1!B:C,MATCH(A2,Sheet1!A:A,0),2) Or =VLOOKUP(A2,'Large Data Worksheet'!A:C,2,FALSE) =INDEX('Large Data Worksheet'!B:C,MATCH(A2,'Large Data Worksheet'!A:A,0),1) We often have had to replace every formula for a client changing the vlookup to an index / match for better accuracy.

Linked Workbooks The story gets even better when you have a linked workbook! What if the workbook gets moved? You get those nice messages says that it cannot update the links, when you open the workbook. =VLOOKUP(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$C,2,FALSE) =INDEX('[My Data.xlsx]Large Data Worksheet'!$B:$C,MATCH(A2,'[My Data.xlsx]Large Data Worksheet'!$A:$A,0),1) You can select "Updated" and "Edit Links" when it cannot find the source workbook. This then allows you to select the new workbook. If you don't select the workbook and just say Continue, then you see the last set of values that it remembered. In most cases, it is the monthly reporting scenario were most of these large spreadsheet systems are first created and then the whole system becomes a very complex and prone to error. E.g. The example above of 10 branches by 24 months à 240 linked workbooks plus the 24 data sheets..!

What should you do? Use Index/Match rather than vlookup if looking up text values. Copy and past values into your monthly reports, so you don't have links everywhere. Get a developer in to assist you to get the data directly out of a database using a little vba. Pump the data back into a database for monthly reporting. * Makes comparing months and years a lot easier.

Summary Often spreadsheets start small and grow to become very large and hard to use. By using inbuilt lookup functions and knowing when to separate large lists, you can save a lot of time and improve the usability and accuracy of your data. Being aware of issues with certain lookup functions such as vlookup and linked workbooks, is half the battle with gaining control again. Know when to use a database for your data and not Excel.

This article was written by Tom Bizannes.
Specialising in Microsoft Office and Database Systems in Sydney, Australia.
He works for MacroView BusinessTechnologies a Microsoft Gold Partner located in Sydney, Australia

http://www.macroview.com.au?Source=ezine

Accuracy Paradox $71.7 High Quality Content by WIKIPEDIA articles The accuracy paradox for predictive analytics states that predictive models with a given level of accuracy may have greater predictive power than models with higher accuracy. It may be better to avoid the accuracy metric in favor of other metrics such as precision and recall. Author: Miller, Frederic P./ Vandome, Agnes F./ McBrewster, John Binding Type: Paperback Number of Pages: 84 Publication Date: 2010/08/10 Language: English Dimensions: 6.00 x 9.02 x 0.20 inches

Improving Inventory Record Accuracy $51.95 Record accuracy has become the focus of attention for many businesses because customers have higher expectations of the right item being delivered on time, and competitive pressures do not allow for extra stockholding. Businesses now have the opportunity to become much more effective given the correct information, the development of better communication and integration of systems. For inventory management, the pressure for most companies is to become more efficient and hold less inventory. The accuracy of data has gradually been emerging in importance. The systems only work if the data is correct and with the introduction of integrated data, there are wide ranging repercussions for businesses that draw conclusions and make decisions based on inaccurate records. Where there is a lot of data, there is the opportunity for many and large errors. This is often the case with inventory: many items, large varieties of different items, and fast changing inventory. Measuring how much there is becomes a challenge in many businesses such as consumer retailing and manufacturing processes where inventory changes so rapidly. With large warehouses there is also the risk of inaccurate recording so that items are not there or not in the right place. Improving Inventory Record Accuracy initially discusses how to quantify the problem, set sensible targets for improvement and how to make the case for doing something to improve accuracy. The book then discusses why inventory records do go wrong and how to rectify the major causes of error. Finally the book illustrates the techniques with which everyone can make their records very accurate. This is a practical book that solves a practical problem and shows the ways to improve record accuracy. Tony Wild has amassed many techniques over the years, has tried them out in practice and he presents them here in an accessible style for professional managers in logistics and stock control, retail/wholesale distribution. The book is also intended for use by students on Institute of Operations Management Certificate, DPIM, CPIM and DLM courses.

Accuracy Before Speed Workplace Poster $20.69 Accuracy before speed posters remind employees of the importnace of quality in everything they do. 24"h x 18"w workplace poster available in plastic or paper.Workplace poster states: "ACCURACY BEFORE SPEED THINK QUALITY."Promote a better work environment, improved safety and overall job performance.Eye-catching workplace posters help reinforce common values, training and quality at a glance.Ideal for break rooms, hallways, entryways, near time clocks and other common areas.

Better Focus $9.99 The right combination of nutrients to improve mental concentration and focus. 60 tablets at 692 mg

Switched-capacitor Techniques for High-accuracy Filter and ADC Design $169 Switched capacitor (SC) techniques are well proven to be excellent candidates for implementing critical analogue functions with high accuracy, surpassing other analogue techniques when embedded in mixed-signal CMOS VLSI. Conventional SC circuits are primarily limited in accuracy by a) capacitor matching and b) the accuracy with which a differential amplifier can squeeze charge from one capacitor to another between clock periods. In Switched-Capacitor Techniques for High-Accuracy Filter and ADC Design, alternative SC techniques are proposed which allow the achievement of higher intrinsic analogue functional accuracy than previously possible in such application areas as analogue filter and ADC design. The design philosophy is to create the required functionality without relying on digital calibration or correction means but instead to develop methods which have reduced dependence on both component matching (especially capacitor matching) and parasitic effects (especially parasitic capacitance). However, the proposed techniques are just as amenable to further digital accuracy enhancement via calibration and/or correction as traditional methods. Two popular application areas are explored in the course of this book for exploitation of the proposed techniques, viz. SC filters and algorithmic ADCs - both cyclic and pipelined. Furthermore, efficient system level design procedures are explored in each of these two areas. The validity of the concepts developed and analyzed in Switched-Capacitor Techniques for High-Accuracy Filter and ADC Design has been demonstrated in practice with the design of CMOS SC bandpass filters and algorithmic ADC stages. For example, a 10.7MHz radio IFselectivity filter integrated in standard CMOS, employing the proposed methods, achieves an accuracy greater than ceramic filters. Another example is an ADC with better than 12-bit intrinsic accuracy, albeit capacitors with only 9-bits matching accuracy were used in the realization. The ADC architecture is also very robust and proven in an embedded digital VLSI application in the very newest 65nm CMOS. The power consumptions and silicon areas of the solutions proposed here are lower than other known solutions from the literature.

Wyatt Earp Accuracy Quotes Dark T-Shirt by CafePress $26 Fast is fine, but accuracy is everything. Quotes Dark T-Shirt Tee, TShirt, Shirt Don't waste time deciding on which shirt to put on each morning. This dark shirt t-shirt will never go out of style and hides stains better too. This high-quality t-shirt is pre-shrunk and 100% cotton, which makes it both comfortable and durable.

I Heart Accuracy Funny Dark T-Shirt by CafePress $26 See More at http://LabelMeHappy.com A heart is not a valentine. Get it right. Funny Dark T-Shirt Tee, TShirt, Shirt Don't waste time deciding on which shirt to put on each morning. This dark shirt t-shirt will never go out of style and hides stains better too. This high-quality t-shirt is pre-shrunk and 100% cotton, which makes it both comfortable and durable.

Accuracy and Precision $106.74 In the fields of engineering, industry and statistics, the accuracy of a measurement system is the degree of closeness of measurements of a quantity to its actual value. The precision of a measurement system, also called reproducibility or repeatability, is the degree to which repeated measurements under unchanged conditions show the same results. Although the two words can be synonymous in colloquial use, they are deliberately contrasted in the context of scientific method. A measurement system can be accurate but not precise, precise but not accurate, neither, or both. For example, if an experiment contains a systematic error, then increasing the sample size generally increases precision but does not improve accuracy. Eliminating the systematic error improves accuracy but does not change precision. A measurement system is called valid if it is both accurate and precise. Related terms are bias and error, respectively. The terminology is also applied to indirect measurements, that is, values obtained by a computational procedure from observed data Author: Miller, Frederic P./ Vandome, Agnes F./ McBrewster, John Binding Type: Paperback Number of Pages: 132 Publication Date: 2009/11/24 Language: English Dimensions: 5.98 x 9.01 x 0.30 inches

Magnivision Accuracy 1.50, Gun $19.99 Accuracy 1.50, Gun

Magnivision Accuracy 2.00, Gun $19.99 Accuracy 2.00, Gun

Magnivision Accuracy 2.50, Gun $19.99 Accuracy 2.50, Gun

Purity Accuracy The Album $8.49 Purity Accuracy The Album

Evaluating Measurement Accuracy $129 Evaluating Measurement Accuracy

Air Raid Accuracy $24.99 Air Raid Accuracy - Photographic Print

Enamored of Accuracy $20.24 Author: Marden, Orison Swett Binding Type: Paperback Number of Pages: 48 Publication Date: 2005/12/01 Language: English Dimensions: 9.25 x 7.50 x 0.10 inches

Empathic Accuracy $58.5 No Synopsis Available

Accuracy Of Spatial Databases $159.95 A selection of the major papers from a meeting on database accuracy which took place in December 1988 in Montecito, USA. They look at errors and accuracy in spatial databases, particularly the problems of modelling uncertainties and reliability,

Live Longer, Live Better $11.99 Dr. Peter Gott combines the empathy of an old-fashioned family doctor with the outspoken fervor of a patients’ rights advocate in this comprehensive guide that is a turn-to resource for those with health concerns. Dr. Gott responds to readers’ medical questions with sensitivity and accuracy and is praised for his knowledge of medicine and his warm, compassionate style.

Better To Be $10 Better To Be - Liam Finn

Better $6 Better - Heavy Mojo

Fatigue Fighter $6.5 Stop feeling tired, and get motivated! 60 capsules at 450 mg

Cinnamon $6.5 Good news for healthy blood sugar levels. 60 capsules at 600 mg

Dermasatin DC $7.5 Make your dark circles disappear... guaranteed! 0.5 oz

Dermasatin PE $7.5 Banish under eye puffiness 0.5 oz

Peppermint Oil $5 Soothing. Fast Acting. 0.33 oz

Fenugreek Tea Bags $5 Upset stomach? 24 tea bags

Avalowerin $29.99 Supports a healthy cardio system 30 tablets

Vitamin D3 $8.99 The Sunshine Vitamin We All Need 30 softgels at 5000 iu

Damiana $5 Support for Sexual Health 100 capsules at 400 mg

Red Wine Extract $6 One of the Top 3 Herbs to support Cardiovascular Health 60 capsules at 30 mg

DHEA $5 Find out why this product is often labeled as a fountain of youth. 60 tablets at 25 mg

Osteocare $7.5 Why taking only Calcium may not be enough... 60 capsules at 570 mg

L-Lysine $7.99 Support Your Immune System 60 capsules at 500 mg

Passion Enhancement $7.5 Special formulated cream for Him 2 oz

Hair Builder $6.5 A rich combination of nutrients vital for healthy hair. 60 capsules at 450 mg

Niacin Flush Free $6.5 Want to maintain healthy cholesterol levels? 90 capsules at 275 mg

Ginger $6.5 #1 supplement for digestive health 60 capsules at 450 mg

This entry was posted on Thursday, April 17th, 2008 at 4:12 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.