Mechanical Estimating with Excel

Look up error correction

(image placeholder)

posted by jim @ 1:35 PM 48 comments

Excel User Tip: Perform math operations without formulas

"Perform math operations without formulas From J-Walk

Spreadsheets, by their nature, rely on formulas to manipulate numbers. But creating formulas isn't always the most efficient way to modify a range of values. For example, suppose that you have a worksheet containing a column of prices for various products, and you need to increase all prices by 5 percent.

Excel provides two ways to accomplish this. The 'traditional' technique goes something like this: Insert or find a blank column near the prices. In that column's first cell, enter a formula to multiply the price in that row by 1.05. Copy the formula down the column. Then select and copy the entire column of formulas, select the original prices, and choose Edit, Paste Special. Select Values to overwrite the original prices with the formulas' results. And finally, delete the column of formulas.

The other, slightly more efficient approach also uses the Paste Special dialog box. To increase a range of values (prices, in our example) by 5 percent, enter 1.05 into any blank cell. Select the cell and choose Edit, Copy. Then select the range of values and choose Edit, Paste Special. Choose Multiply and click OK. Then delete the cell that contains the 1.05.

If you need to do this sort of thing frequently, you may want to download a copy of my Cell Math add-in (free).

*get free add-in here

More information about the Cell Math Add-In"

posted by jim @ 6:30 AM 7 comments

Excel User Tip: Avoid error displays in formulas

Excel User Tip: Avoid error displays in formulas: "Avoid error displays in formulas

From the Grand Master J-Walk

Sometimes a formula may return an error message. Usually, you'll want to know when a formula error occurs. But now and then you may prefer to avoid the messages. You can do so by using an IF() function to check for an error.

For example, the formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),'',A1/B1)

You can adapt this technique to any operation. The original formula serves as the argument for the ISERROR() function, and it repeats as the last argument of the IF() function. Like this:

=IF(ISERROR(OriginalFormula),'',OriginalFormula) "

posted by jim @ 6:27 AM 11 comments

Changing Toolbar Location by Allan Wyatt

Changing Toolbar Location: "You can quickly and easily change the location of Excel toolbars to achieve whatever appearance you desire on your screen. For instance, if you double-click your mouse on any portion of a toolbar that is not occupied by a tool, the toolbar is removed from its normal location and appears in its own dialog box.

Once in a dialog box format, you can easily drag a toolbar to any location on the screen desired. If you approach a side of the screen, the toolbar will 'dock' to the side. You can dock toolbars to any of the four sides of the screen.

You should experiment with toolbar locations to determine which is best for the type of work you do."

posted by jim @ 6:16 AM 6 comments

Excel 12 New and Improved?

As part of the Excel team’s work to increase the number of rows and columns in Excel 12, we also increased a number of the other “limits” in the product. This work falls into a two categories.

First, we increased a number of limits to support our “big grid” work. These are limits that we increased to make sure that all of Excel’s features could scale to handle more rows and columns. A lot of folks have already asked about these sorts of limits in comments to my first post, in emails, and in comments on other blogs and websites. For example, we increased the number of rows allowed in a PivotTable from 64k to 1 million (2^20 to be precise), we increased the amount of memory that Excel can use from 1GB to the maximum allowed by Windows, and we completely eliminated the limit on the number of rows of a column or columns that can be referred to in an array formula.

Second, we took the opportunity to increase a number of other limits that our customers had asked us to increase over the years. For example, we increased the number of colours allowed in a single workbook from 56 (indexed colour) to 4.3 billion (32-bit colour), and we increased the number of characters that can be stored and displayed in a cell formatted as Text from 255 to 32k. We increased the number of levels of sorting possible on a range, and the number of conditional formats possible on a cell. Some of these limits – like the number of levels of sorting possible on a range – obviously require UI changes; I will discuss those in later posts when I cover the other work we have done in those areas.

Here is a list of all of the major changes we made to Excel 12 in the area of limits.

The total number of available columns in Excel
Old Limit: 256 (2^8)
New Limit: 16k (2^14)

The total number of available rows in Excel
Old Limit: 64k (2^16)
New Limit: 1M (2^20)

Total amount of PC memory that Excel can use
Old Limit: 1GB
New Limit: Maximum allowed by Windows

Number of unique colours allowed a single workbook
Old Limit: 56 (indexed colour)
New Limit: 4.3 billion (32-bit colour)

Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

Number of levels of sorting on a range or table
Old Limit: 3
New Limit: 64

Number of items shown in the Auto-Filter dropdown
Old Limit: 1,000
New Limit: 10,000

The total number of characters that can display in a cell
Old Limit: 1k (when the text is formatted)
New Limit: 32k or as many as will fit in the cell (regardless of formatting)

The number of characters per cell that Excel can print
Old Limit: 1k
New Limit: 32k

The total number of unique cell styles in a workbook (combinations of all cell formatting)
Old Limit: 4000
New Limit: 64k

The maximum length of formulas (in characters)
Old Limit: 1k characters
New Limit: 8k characters

The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limit: 64

Maximum number of arguments to a function
Old Limit: 30
New Limit: 255

The number of characters that can be stored and displayed in a cell formatted as Text
Old Limit: 255
New Limit: 32k

Maximum number of items found by “Find All”
Old Limit: ~64k (65472)
New Limit: ~2 Billion

Number of rows allowed in a Pivot Table
Old Limit: 64k
New Limit: 1M

Number of columns allowed in a Pivot Table
Old Limit: 255
New Limit: 16k

Maximum number of unique items within a single Pivot Field
Old Limit: 32k
New Limit: 1M

Length of the MDX name for a Pivot Table item; also the string length for a relational Pivot Table
Old Limit: 255 characters
New Limit: 32k

The length at which fields’ labels are truncated when added to PivotTable; this also includes caption length limitations
Old Limit: 255
New Limit: 32k

The number of fields (as seen in the field list) that a single PivotTable can have
Old Limit: 255
New Limit: 16k

The number of cells that may depend on a single area before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
Old Limit: 8k
New Limit: Limited by available memory

The number of different areas in a sheet that may have dependencies before Excel must do full calculations instead of partial calculations (because it can no longer track the dependencies required to do partial calculations)
Old Limit: 64k
New Limit: Limited by available memory

The number of array formulas in a worksheet that can refer to another (given) worksheet
Old Limit: 65k
New Limit: Limited by available memory

The number of categories that custom functions can be bucketed into
Old Limit: 32
New Limit: 255

The number of characters that may be updated in a non-resident external workbook reference
Old Limit: 255
New Limit: 32k

Number of rows of a column or columns that can be referred to in an array formula
Old Limit: 65,335
New Limit: Limitation removed (full-column references allowed)

For those of you that read this far, thanks. Next post I will step back a bit and review all the areas where we have made feature investments in Excel 12. It is a pretty big list, and I am excited to share it with you.

PS updated to fix a typo

posted by jim @ 10:40 AM 10 comments

Returning Proper Names

Excel Tips by Allen Wyatt

To return all the proper names, there are a couple things you could do. One method would be to bypass using a formula altogether. Instead, you could use the AutoFilter feature in Excel and

1. Select any cell in your data table.
2. Choose Data | Filter | AutoFilter. Excel adds drop-down arrows at the right of each column header in the table.
3. Use the drop-down list at the top of the salaries column to choose Top 10. Excel displays the Top 10 AutoFilter dialog box. (Click here to see a related figure.)
4. Adjust the center control from 10 to 5.
5. Click on OK. Excel displays the top five salaries in the list.

When you follow these steps, you may actually end up with more than five records visible, particularly if there are ties in the employee salaries. The filter identifies the top five salaries and then displays all the records with salaries matching those.

If you don't want to use the AutoFilter, another option is to simply make sure that there is something unique about each of the records in your employee list. For instance, if the employee names are in column B and the salaries are in column C, then you could use the following formula in column A to make each record unique:

=C2+ROW()/100000000

This will add the row number divided by 100,000,000 and will make a unique value. If you have (for example) identical salaries of 98,765.43 in rows 2 and 49 in column A they will be:

98765.43000002
98765.43000049

The large number (100,000,000) is so that if you had an identical number in row 65536, you would get:

98765.43065536

And even in this case the rounded value to 2 decimal places would still be the real number. If the LARGE and VLOOKUP are done with the "non-unique" values in column A, then you will return the largest salaries (and their associated people), based on the person's position within the list.

A third approach is to use the RANK and COUNTIF functions to return a unique "ranking" for each value in the list of salaries. If the salaries are in the range B1:B50, enter the following in cell C1 and copy it down the range:

=RANK(B1,$B$1:$B$50)+COUNTIF($B$1:B1,B1)-1

You can now use INDEX on the ranking values to return the name associated with each salary.

Finally, a fourth approach is to create a macro that can return the desired information. There are many ways that a macro could be implemented; the following is just one of them:

Function VLIndex(vValue, rngAll As Range, iCol As Integer, lIndex As Long)
 Dim x As Long
 Dim lCount As Long
 Dim vArray() As Variant
 Dim rng As Range
 On Error GoTo errhandler

 Set rng = Intersect(rngAll, rngAll.Columns(1))
 ReDim vArray(1 To rng.Rows.Count)
 lCount = 0
 For x = 1 To rng.Rows.Count
     If rng.Cells(x).Value = vValue Then
         lCount = lCount + 1
         vArray(lCount) = rng.Cells(x).Offset(0, iCol).Value
     End If
 Next x

 ReDim Preserve vArray(1 To lCount)
 If lCount = 0 Then
     VLIndex = CVErr(xlErrNA)
 ElseIf lIndex > lCount Then
     VLIndex = CVErr(xlErrNum)
 Else
     VLIndex = vArray(lIndex)
 End If
errhandler:
 If Err.Number <> 0 Then VLIndex = CVErr(xlErrValue)
End Function

The parameters passed to this user-defined function are the value, the range of cells to lookup in, the "offset" from this range for the lookup (the number of columns to the right is positive, to the left is negative) and the number of the duplicate (1 is first value, 2 the second, and so on).

To use it, for example's sake, assume A1:B1 contain column headers, A2:A100 contains the salaries, and B2:B100 contains the employee names. In cell E2 you can enter the following to determine the largest salary in the table:

=LARGE($A$2:$A$100,ROW()-1)

In cell F2 you can enter the following formula to determine if the row has any duplicates and keep track of the current "value" of that duplicate:

=IF(E2=E1,1+F1,1)

In cell G2 you can use the following formula, which invokes the user-defined function:

=VLIndex(E2,$A$2:$A$100,1,F2)

Copy cells E2:G2 to E3:G6, and you will have (in column G) the names of the employees with the five largest salaries.

posted by jim @ 6:07 AM 13 comments

New Excel Calculator

You must get the calculator at excel-it.
You can paste directly into a cell or get data from a cell to use in calc's

posted by jim @ 7:40 AM 8 comments

Easiest Way to Link Cells in Microsoft Excel

Easiest Way to Link Cells in Microsoft Excel: "This seems so much harder that it really is. To link a cell to another worksheet, just copy the cell you want to link to. Go back to the cell where you want the data from that cell, and hit Edit�Paste Special�Paste Link.

Now, to link to another workbook, you do the same thing. The only rule is that you must have the workbook you copy from be open until you paste the link. But you copy and paste it using the exact same method"

posted by jim @ 6:59 AM 11 comments

Concatenate in Microsoft Excel

Concatenate in Microsoft Excel: "First of all, what the heck is concatenation anyway? We think of Excel as being only able to perform arithmetic functions on data. Wrong. Excel has many features that allow you to work with text data as well.

There are two ways to create concatenation formulas: the hard way and the easy way.
The Easy Way

Suppose you have First Names in column A and last names in column B. Your current task requires them both to be in the same cell. This is so simple, it's ludicrous. Refer to the graphic below.

In the example, we want a space between the first name and last name, so we type it between quotes.

Suppose you wanted to automatically create filenames to assign to a data list that you're going to upload into an online shopping cart program. You have all your graphics prepared as thumbnails and as regular size. You already know that you've used the SKU number, that you added a 't' in front of all the thumbnails, and that they're all JPG files. You've got the SKU number in column C of your worksheet already. You know there must be an easy way, and so there is.

Here's our thumbnail formula. How simple is that?

Our full-size filename formula is even easier:

Copy the formula down! Select all your formula cells, choose Edit�Paste Special, Values, OK. Done.
The Hard Way

This is the one most used by people who don't know the easy way.

Now, I don't know about you, but I can never remember how to spell CONCATENATE!"

posted by jim @ 6:57 AM 9 comments

New Excel Tool Bar to Use on Your Browser

You must get this new tool bar for your browers. While you are at it please check the rest of the site. It is a great site for Excel.

Excel-It

posted by jim @ 6:15 AM 2 comments

Excel - Nested IF Functions

Excel - Nested IF Functions: "Using Nested IF Functions Effectively in Excel

Nested IF functions are a simple way to do a complex conditional formula. Nested IF functions allow you to use up to seven IF functions to impose conditions.

Essentially, the way a nested if statement would work is like this:

=IF('if this condition stated here is true', then enter 'this value, else if('if this condition stated here is true', then enter 'this value, else enter'this value'))

It looks quite long and imposing, but its really quite simple once you understand the concept. To illustrate, let's say you have a spreadsheet that you use to keep track of your sales force. The rate of commission each sales person receives is based on the amount of sales they have generated for that month. For example:

From $1 to $10 earns 10% commission
From $11 to $100 earns 15% commission
Anything over $100 earns 20% commission

Assuming the amount of sales is in column B, starting at row 4, and that the column containing the commission is formated for percentages, this is what the nested IF function would look like:

=IF(B4<=10,'10', if(b4<=100, '15', '20'))

This nested IF function says that if the cell B4 is less than or equal to 10, then put '10' in this cell (the commission), if the cell B4 is greater than 10, but less than or equal to 100, then put 15 in this cell.

If the number in cell B4 is greater than 100, then put 20 in this cell.

This simplifies data entry for the spreadsheet as you now only need enter the amount of sales and the commission percentage is worked out for you. It also means there is less chance of making errors on the commission when entering the data"

posted by jim @ 7:43 AM 15 comments