background preloader

Excel

Facebook Twitter

Microsoft Excel. Photos du journal - DexLab Analytics. Use Benford's Law with Excel 97-2003 to Improve Business Planning. Ideas for Business Unless you're a public accountant, you probably haven't experimented with Benford's Law.

Use Benford's Law with Excel 97-2003 to Improve Business Planning

Auditors sometimes use this fascinating statistical insight to uncover fraudulent accounting data. But it might reveal a useful strategy for investing in the stock market. And it might help you to improve the accuracy of your budgets and forecasts. This article will explain Benford's Law, show you how to calculate it with Excel, and suggest ways that you could put it to good use. From a hands-on-Excel point of view, the article describes new uses for the SUMPRODUCT function and discusses the use of local and global range names. Benford's Law Explained Also called the "first-digit law", Benford's Law says that for many real-life sources of data, the first digit will be "1" about 30% of the time and that small first-digits will occur more frequently than large first-digits.

The law is named after Dr. Intrigued, he analyzed thousands of sets of numbers. Probability =LOG(1+1/FirstDigit) Data Interactive. Excel xll add-in library. Things to Make and Do in the Fourth Dimension. Turn a digital photo into an Excel spreadsheet!

Things to Make and Do in the Fourth Dimension

Accepts JPG, JPEG, PNG and GIF files, anything larger than 128 pixels will be scaled down. Digital photographs are actually just spreadsheets. When you take a photo, your camera measures the amount of red, green and blue light hitting each pixel, ranks them on a scale from 0 to 255 and then records those values as a spreadsheet. I found a way to actually open digital photos as spreadsheets in Excel. The process involves cracking the digital photo file open, scraping out all of the numbers and putting them into the cells of an Excel spreadsheet. Conditional formatting is then used to colour each cell so that it is completely black for “0”, fully bright for “255” and every whole-number shade in between. I originally did this the long way because I had a lot of free time. De ‘smelly formula' - CM Web.

Timing Excel Formula Calculations. I have written a couple of posts previously about the methodology of timing Excel calculations: A Quantum of Time – measuring time accurately.

Timing Excel Formula Calculations

Using Constant Arrays and Array Expressions in Excel Formulas. How Excel handles array expressions and constants in single-cell and multi-cell formulas is not well documented.

Using Constant Arrays and Array Expressions in Excel Formulas

So here is my attempt to shed some light on the subject. What are Constant Arrays? Constant arrays are Excel formula expressions that allow you to create arrays containing numbers, text, logical values or error values. Constant arrays are enclosed in braces ({}). {1,”Q”,#N/A,TRUE} Columns are separated from one another by , and rows are separated by ; so {1,2,3;”A”,”B”,45.6;#N/A,”Z”,99} creates You have to enter all the items in a row first before adding ; to move to the next row. Constant arrays do not allow you to create arrays containing Dates or Ranges or Defined Names or Expressions or other Constant Arrays.

What are Array Expressions? Array expressions are parts of Excel formulas that produce a result array of values when they are evaluated. A3:A6+B1:B4 – returns a 4 value column array – A3+B1, A4+B2, A5+B3, A6+B4 Suppose you have 2 ranges A1:B4 and C1:D1. Name Manager. For Microsoft® Excel® Description.

Name Manager

How to Create Dynamic Excel Filter (Extract data as you Type) Data Filter is one of the most used excel functionality.

How to Create Dynamic Excel Filter (Extract data as you Type)

Expand view of cell contents when hover over/click on cell. Automatically Sort Data in Alphabetical Order using Formula. Excel built-in data sorting is amazing, but it isn’t dynamic.

Automatically Sort Data in Alphabetical Order using Formula

If you sort data and then add data to it, you would need to sort it again. In this post, I will show you various ways to sort data in alphabetical order using formulas. This means you can add data, and it will automatically sort it for you. When the Data is all Text with No Duplicates. If cell contains text found in list. Use INDEX to lookup multiple values in a list. Learn Microsoft Excel. Excel's VLOOKUP function is excellent when you want to find a value in a table based on a lookup value.

Use INDEX to lookup multiple values in a list. Learn Microsoft Excel

But if your table includes your lookup value multiple times, you'll find that VLOOKUP can't do it. This lesson shows you how to use the INDEX function (plus some other functions) to find all matching values in a list, and return a value from another column in the same row. It also looks at how to do this when you want to return all values which are a partial match (i.e. a wildcard search) to the values in your lookup table. Here's a simple example based on a question I was asked by one of our readers. There are two worksheets, one containing Form data for a mail merge, and one containing Parcel data, some of which is to be included in the mail merge.The Form worksheet includes a row for each landowner who is to receive a letter.

Use INDEX to lookup multiple values in a list. Learn Microsoft Excel. Excel - Get column by finding value in the row. How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP. There are often times in Excel you want to look up a value in a table based on another cell.

How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

Using VLOOKUP or HLOOKUP is a popular method for doing this quickly and easily. (Or, perhaps you read about my improved INDEX-MATCH solution in a previous blog post.) 30 Excel Functions in 30 Days: 20 – ADDRESS. Yesterday, in the 30XL30D challenge, we found items in an array with the MATCH function, and learned that it plays nicely with other functions, like VLOOKUP and INDEX.

30 Excel Functions in 30 Days: 20 – ADDRESS

For day 20 in the challenge, we'll examine the ADDRESS function. It returns a cell address as text, based on a row and column number. Do you need ADDRESS? Do other functions do the same thing, but better? EXCEL INFO. How to unmerge cells and fill with duplicate values in Excel? If you have a worksheet which contains numerous merged cells, and now you need to unmerge them and auto-fill down the original values from the merged cells.

See following screenshots: How could you quickly deal with this task? Unmerge cells and fill with duplicate data with Go To Special command Unmerge cells and fill with duplicate data with VBA code Unmerge cells and fill with duplicate data with one click Recommended Productivity Software Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.Kutools for Excel: Adds 120 powerful new features to Excel. Excel. Advanced Excel Skills - What are they & How to become Advanced Excel user? Now a days, many jobs require advanced Excel skills. Naturally, you are interested to know what are these Advanced Excel Skills?

Based on my experience of training more than 3,000 students in various online & physical training programs, the following 9 areas form the core of advanced Excel skills. Advanced Formulas. Named Ranges Excel. Free Excel Templates and Spreadsheets. Looking for free Microsoft Excel ® templates? This page lists all the popular spreadsheet templates and calculators created by Vertex42.com, along with other places where you can find free Excel templates and spreadsheets.

If you are looking for solutions for your business, see the Business Templates page. Advertisement. 14 Basic Skills For Excel Users « Contextures Blog. Excel Pivot Table Index. Excel: VBA. Comprehensive Guide to VLOOKUP & Other Lookup Formulas. Excel: VBA. WorksheetWorks.com. Dynamic Data Source. 1. Excel Pivot Table Introduction Getting Started with Pivot Tables Use a Dynamic Data Source 2. Create a Pivot Table in Excel 2007 3. Pivot Table Tutorial List Getting Started with Pivot Tables The first step in creating a Pivot Table is to organize your data in a list of rows and columns. Make sure that there is a heading in every column, and no rows that are completely blank.

Then, set up the data range as a dynamic range. 1. 2. Chandoo.org Podcast. PHPExcel - Home. ExcelIsFun.