Mechanical Estimating with Excel

Look up error correction

2005-12-15T13:35:00.000-08:00

(image placeholder)

Excel User Tip: Perform math operations without formulas

2005-10-06T06:30:00.000-07:00

"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"

Excel User Tip: Avoid error displays in formulas

2005-10-06T06:27:00.000-07:00

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) "

Changing Toolbar Location by Allan Wyatt

2005-10-03T06:16:00.000-07:00

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."

Excel 12 New and Improved?

2005-09-28T10:40:00.000-07:00

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

Returning Proper Names

2005-09-26T06:07:00.000-07:00

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

Select any cell in your data table.
Choose Data | Filter | AutoFilter. Excel adds drop-down arrows at the right of each column header in the table.
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.)
Adjust the center control from 10 to 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.

New Excel Calculator

2005-09-22T07:40:00.000-07:00

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

Easiest Way to Link Cells in Microsoft Excel

2005-09-22T06:59:00.000-07:00

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"

Concatenate in Microsoft Excel

2005-09-22T06:57:00.000-07:00

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!"

New Excel Tool Bar to Use on Your Browser

2005-09-22T06:15:00.000-07:00

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

Excel - Nested IF Functions

2005-09-20T07:43:00.000-07:00

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"

Returning Blanks with VLOOKUP

2005-09-19T06:28:00.000-07:00

Returning Blanks with VLOOKUP

Check this for another look at VLOOKUP

Lost Links

2005-09-14T06:37:00.000-07:00

Hi All
Had to change the format. I was playing around and really screwed things up
I have been adding the links back, so if you know of a link. Please leave a comment and I will add it

Excel Spreadsheets - Protecting your Worksheets and Workbooks

2005-09-13T07:42:00.000-07:00

Excel Spreadsheets - Protecting your Worksheets and Workbooks: "How to avoid losing important information in your Excel Spreadsheets

Using Protection

Losing important data from a spreadsheet is one of the most irritating and disruptive things that can happen - particularly if the spreadsheet has taken a long time to build. to stop this from happening, Excel has a protection feature that allows you to protect your information. Once a worksheet or workbook is protected no information can be modified or erased until the spreadsheet has been unprotected.

Protecting a Worksheet

This will protect the currently active worksheet. To protect the worksheet, from the 'Tools' menu, select 'Protection' and then 'Protect Worksheet'. You can choose to protect the contents, objects and/or scenarios in the worksheet. You can also use a password. If you decide to use a password write it down somewhere! If you forget the password there isn't any easy way to unprotect the worksheet."

Note: There are several places to get software to find lost or forgotten passwords. I will not tell you about them but you can find them. Try to google for the sites

J-Walk Enhanced Data Form v2: Free Download

2005-09-07T06:26:00.000-07:00

J-Walk Enhanced Data Form v2: Free Download: "Download and Install

The J-Walk Enhanced Data Form is a standard Excel add-in file. It does not use any custom DLLs, and it makes no changes to your system.

To install the add-in:

1. Download the J-Walk Enhanced Data Form v2 add-in (352K).
This is an EXE file that creates the dataform2.xla add-in file. This file can be stored anywhere on your system.
2. Start Excel 97 or later.
3. Select Add-Ins from the Tools menu. This command is not available if a workbook is not visible.
4. In the Add-Ins dialog box, click the Browse button.
5. Locate the dataform2.xla file (the file you extracted in Step #1)

After performing these steps, Excel's Data menu will display a new menu item: J-Walk Enhanced Data Form. The add-in will be available for all future Excel sessions.
Uninstall

To uninstall the add-in, choose Add-Ins from the Tools menu, and remove the checkmark from the 'Enhanced Data Form v2' item.

After performing this step, the dataform2.xla add-in will not be loaded when Excel starts."

I use this form all of the time. It saves a ton of time when adding items to your data set.
Plus it is Free

Another Good Site

2005-08-31T12:30:00.000-07:00

This is S immons Site Also check out his codematic site

Looters

2005-08-31T12:28:00.000-07:00

I could understand the looters trying to get drinking water and food that is going to rot anyway.
But jewlery stores, micro wave stoves ect.
Take them out and shoot them

JLXL - EXCEL LINKS

2005-08-30T09:58:00.000-07:00

JLXL - EXCEL LINKS: " Go here to find a list of great Add-Ins

Work Sheet Legends

2005-08-30T06:20:00.000-07:00

Daily Dose of Excel:

Visit this site and check out the post for using Work Sheet Legends

I found this very useful

I have been very busy the last few days with 2 large hospital estimates.
Should be able to get back to daily posting in a few days

Another great site has been added

2005-08-24T06:51:00.000-07:00

Check out Justin's site

Excel - How to Print Formulas and Functions

2005-08-23T10:49:00.000-07:00

Excel - How to Print Formulas and Functions: "Excel formulas by default don't get printed out. What you usually see is the result of the Excel formula. So what happens when you need to have the formulas print out and not the results?

It is actually possible to do so. There is a way to toggle the formulas in excel. Go to the 'Tools' menu and select 'Options'. Click on the 'View' tab and you'll see a 'Window Options' section towards the bottom. Check the box next to 'Formulas'.

When you click 'Okay' you will notice two things:

1. You now see the formulas in the cells, rather than the results
2. The columns are now exactly twice as wide as they were before.

Don't worry - when you change back to displaying the formula's results (by unchecking the 'Formulas' box), your columns will return to their normal size"

Another new Excel Site is added

2005-08-23T08:11:00.000-07:00

Will's new site has been added
please check it out.
It is a great site

Keyboard shorcuts

2005-08-22T15:05:00.000-07:00

Andrew's Excel Tips: "Keyboard shorcuts save a lot of time. Here are some of the more common ones (they can also be used in a lot of other software other than Excel)

Push both keys at the same time -

Ctrl A Select All

Ctrl C Copy

Ctrl X Cut

Ctrl V Paste

Ctrl B Toggle Bold Font

Ctrl I Toggle Italic Font

Ctrl U Toggle Underline"

If your new to shortcuts, try these and see what a differance they make. Those in the know couldn't live wit out them.

Alternate row shading using Conditional Formatting

2005-08-22T15:01:00.000-07:00

One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task.

Select the range that you want to format
Choose Format, Conditional Formatting
In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula:

=MOD(ROW(),2)=0.
Click the Format button, select the Patterns tab, and specify a color for the shaded rows.
Click OK twice to return to your worksheet.

The best part is that the row shading is dynamic. You'll find that the row shading persists even if you insert or delete rows within the original range.

New Site Added

2005-08-22T06:53:00.000-07:00

I have added a new site to my blogs I read. Please visit Andy's site and you will find many useful tips and down loads. Andy is a master at charts and ect. You will also find Andy at the Ozgrid site

Multiple Criteria Lookup

2005-08-17T06:21:00.000-07:00

: Multiple Criteria Lookup

http://blog.livedoor.jp/andrewe/
Let's say you want to find to something in Column C by refering to criteria in cells D1 and E1 as below (looking up ages is just an example)

First Names are in Column A

Last Names are in Column B

Ages are in Column C

First Name Reference = D1

Last Name Reference = E1

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000))

You can't use entire columns for ranges but entire rows are okay.

or

{=SUM((A1:A1000=D1)*(B1:B1000=E1)*C1:C1000)}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)

or

{=SUM(IF(A1:A1000=D1,IF(B1:B1000=E1,C1:C1000)))}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)

Note: If Column C did not have numerical values the first 2 formulas would return #VALUE! errors while the third formula would return 0. The following formula works better in this case.

{=INDEX(C1:C1000,MATCH(1,(A1:A1000=D1)*(B1:B1000=E1),0))}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)
Let's say you want to find to something in Column C by refering to criteria in cells D1 and E1 as below (looking up ages is just an example)

First Names are in Column A

Last Names are in Column B

Ages are in Column C

First Name Reference = D1

Last Name Reference = E1

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000=E1)*(C1:C1000))

You can't use entire columns for ranges but entire rows are okay.

or

{=SUM((A1:A1000=D1)*(B1:B1000=E1)*C1:C1000)}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)

or

{=SUM(IF(A1:A1000=D1,IF(B1:B1000=E1,C1:C1000)))}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)

Note: If Column C did not have numerical values the first 2 formulas would return #VALUE! errors while the third formula would return 0. The following formula works better in this case.

{=INDEX(C1:C1000,MATCH(1,(A1:A1000=D1)*(B1:B1000=E1),0))}

Must be entered as an array. (Push Ctrl, Shift and Enter simultaneously)"

Automactic Row Numbering

2005-08-17T06:16:00.000-07:00

: "Instant Rows Numbers
Enter this formula anywhere and sequential numbers 1, 2, 3 ....
will appear in sequence in each row.
Note: This won't work if the above row is not blank
but you start in the top row (A1, B1, C1)

Here's a 'short' version (Thanks Frank Brutsaert)

=IF(ROW()=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN())) 1)

A quick way to enter row numbers is to enter the formula in the top cell of a selection, then push Ctrl and Enter at the same time.
This formula also works if there is text above such as column header. (Thanks Nick Osdale-Popa)

=IF(ROW()=1,1,IF(ISTEXT(INDIRECT(ADDRESS(ROW()
-1,COLUMN()))),1,INDIRECT(ADDRESS(ROW()-1,COLUMN())) 1))

This formula enables you to add to add text to the numbers (in this case it is 'T')

=IF(ROW()=1,'T1',IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))=
'','T1','T'&SUBSTITUTE(INDIRECT(ADDRESS(ROW()-1,COLUMN())),'T','') 1))

This will work whether the above row is blank or not (the above formulas were actually designed that way)

Enter =ROW(INDIRECT('1:10')) for 10 sequential numbers in 10 cells as an array formula (Push Ctrl, Shift and Enter simultaneously)
"

Spy Journal Excel Tips

2005-08-16T12:24:00.000-07:00

Spy Journal Excel Tips: "Tabs Shortcuts

Sheets in Excel Workbooks are accessed with the mouse by clicking on the sheet tab at the bottom of the screen.
There are a number of short cuts that can be used instead.
CTRL PAGEUP and CTRL PAGEDOWN will cycle through the sheets with PAGEUP taking you left and PAGEDOWN right.
When you have more sheet tabs than can be displayed on the screen you can increase the amount of space by dragging the scroll bar slider right. However once you have run out of room here there are some buttons at the very left of the sheet tabs that are very useful.
These are similar to a CD player controls with the outer arrows taking you to the front and end of the sheet tabs. The center arrows selects the next sheet up and down from the current sheet.
Additionally right clicking on these arrows displays a list of all sheet tabs that can be accessed. If there are more sheet tabs than this can handle it displays a More Sheets option.

When Clearing Cells Is A Drag

2005-08-11T15:01:00.000-07:00

When Clearing Cells Is A Drag - FREE Microsoft Excel Spreadsheet Tips and Tutorials: "TheExcelAddict.com

Microsoft Excel 'Quick Tip'
When Clearing Cells Is A Drag
By now you are probably familiar with using the Fill Handle. But here's something you may not know. You can also use the Fill Handle to clear the contents of a range of cells by dragging it backwards over the current selection.

Try it. Select a range of cells that contain the data you want to clear. Now drag the Fill Handle backwards over the selected cells. You'll notice that the cells turn gray as you drag. Release the mouse button and the cell contents are gone. The neat part about this is that the cell formats are not affected."

Estimating Templates

2005-08-11T14:24:00.000-07:00

: "Material Lists

Material List templates may also be created to save time when adding materials/inventory to a job. Make a list template of common materials needed for a specific job and re-use the templates for new jobs requiring the same basic parts. Edit the materials listed on a job as needed once the template has been attached."

The J-Walk Blog

2005-08-11T14:21:00.000-07:00

The J-Walk Blog: "Flash Face

An oldie, but a goodie: Ultimate Flash Face. Drag-and-drop face creation. It's amazing how a small adjustment can make a huge difference in the overall appearance.
I thought this was fun. Check it out

John Beardsworth Photography News/Blog

2005-08-11T14:19:00.000-07:00

John Beardsworth Photography News/Blog: "'Tripodophobia'

Uniformed officials seem to have an unnatural fear of tripods. I've observed this before, at City Hall, the Swiss Re, and more pleasantly at the Tower of London. But my recent enthusiasm for digital infrared has meant I've needed to use the tripod as the infrared filter is opaque and you have to compose the picture and then place it over the lens. Exposure times are over a second with the lens wide open, and I like to stop down and use times of up to 30 seconds which blur clouds and trees."

IF Statements in Formulas in Microsoft Excel

2005-08-11T06:21:00.000-07:00

IF Statements in Formulas in Microsoft Excel: "IF Statements in Formulas in Microsoft Excel

A simple example of an IF statement is giving grades for percentages. We’ll assume that our percentage is in cell A2, and our formula resides in cell B2.

An IF statement has at least 3 parts called arguments. When you type an IF statement, the tool tip automatically appears to tell you the 3 arguments required.

The first part of the IF statement is called a logical test, which is also often referred to as a condition.

Tip. Note that all “text” is surrounded by quotes when used in formulas. There are a few exceptions, such as True and False.

We like to think of the commas that separate the 3 arguments as words. The first comma means “give me”, and the second means “otherwise, give me”. So if we wrote the above formula in English instead, it would read like this:

If the value in cell A2 is greater than 60, give me a B, otherwise give me an A.

If we don’t provide the third part of the argument, we simply get the “answer” returned to us: A2 is not greater than 60, so our condition or logical test is FALSE.

Tip. You may only use 7 nested IF statements. That means 8 IFs can be in your formula; 7 being nested inside the first. Even in Excel 2003, there is no indication that the problem with the formula is too many IFs. If you have a requirement for more than 7, use VLOOKUP, instead.

Let’s break down the formula, IF by IF:

*

=IF(A1>60,”D”

If the value in A1 is greater than 60, put a D in cell B2.

*

IF(A1>70,”C”

If the value in A1 is greater than 70, put a C in cell B2.

*

IF(A1>80,”B”

If the value in A1 is greater than 80, put a B in cell B2.

*

IF(A1>90,”A”

If the value in A1 is greater than 90, put an A in cell B2.

*

“F”

Otherwise—if none of the above conditions are met—put an F in cell B2.

Combining IF with other formulas.
IF with AND

Let’s suppose that a sales bonus is based on having made at least 20 sales call, and having made at least 15,000 in sales. We must check both conditions, using an AND statement.

IF with OR

Now, let’s use IF with an OR statement. In this case, a sales bonus is based on having made at least 20 sales calls, OR having made at least 15,000 in sales. If either condition is met, the bonus is given.

IF with OR and AND

Imagine that employees with perfect attendance become a member of an exclusive employee's club. When this occurs, they're given special consideration on bonuses. So, in this case, if they're a member OR they sell more than 15,000 and make at least 20 sales calls, they receive the bonus.

"

Easiest Way to Link Cells in Microsoft Excel

2005-08-11T06:18:00.000-07:00

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."

Sum the value of a cell across multiple worksheets

2005-07-28T12:37:00.000-07:00

Example from office.microsoft.com
Another common Excel task is to sum the value of a cell in multiple worksheets and then display the result in another cell. For example, you may want to sum the number of a particular product that customers have ordered over a period of time, such as by quarterly periods. If worksheets are formatted in the same way for each period, the total sales for the product always appears in the same cell in each worksheet.

Finding the sum in this situation is simple. You can use a formula:

Start Excel. A new, blank workbook appears.
In cell B3 in Sheet1, type 20.
In cell B3 in both Sheet2 and Sheet3, type 30.
In cell A1 in Sheet1, type the following formula:
=SUM(Sheet1:Sheet3!B3)
Press ENTER. Notice that cell A1 displays 80, which is the total sum of the cells in the three worksheets.

Enter data in multiple worksheets at the same time

2005-07-28T12:33:00.000-07:00

Example from office.microsoft.com

As an example, let's say you want to put the same title text into different worksheets. One way to do this is to type the text in one worksheet, and then copy and paste the text into the other worksheets. If you have several worksheets, this can be very tedious.

An easier way to do this is to use the CTRL key:

Start Excel. A new, blank workbook appears.
Press and hold the CTRL key, and then click Sheet1, Sheet2, and Sheet3.
Click in cell A1 in Sheet1, and then type:

This data will appear in each sheet.
Click Sheet2 and notice that the text you just typed in Sheet1 also appears in cell A1 of Sheet2. The text also appears in Sheet3.

Spread Sheet Formating 3

2005-07-28T06:19:00.000-07:00

There is absolutely NO situation in which a circular reference should ever be built into a spreadsheet model.

“But, but, but… This equation requires an iterative solution!”

The answer is still NO, none, never! I know what you’re thinking, “It’s foolish to make such an absolute statement.” OK then, prove me wrong. I’m open to any arguments. I haven’t come across anything yet that has swayed my opinion.

I frequently come across situations in financial models that require an iterative type of calculation to arrive at a solution. Do I create a modeling solution to the problem? Yes. Do I use a circular reference? NO. Is the result the same as it would be if I used a circular reference with iterations turned on? Yes.

The problem with creating a “good” circular reference is that you’ll never know if you happen to accidentally create a bad one. Instead of building in “good” circular references, I recommend that you manage your iterative calculations by automating a “Goal Seek” solution. I’ve done this successfully with several iterative calculations: depreciation step-up on asset purchases, federal and state tax calculations, optimal debt sizing, working capital targets, clawback provisions based on cumulative NPV, you name it.

I’m absolutely convinced that I will never come across an iterative calculation that I can’t manage with an automated goal seek. Whether it’s finance, accounting, engineering, science, medical it just doesn’t matter. You can build iterative solution engines by automating a goal seek and avoid the circular reference in the model. Another drawback to circular logic is that if you happen to need to use a goal seek elsewhere in the model, the circular logic often disables that ability.

Spread Sheet Formating 2

2005-07-27T07:26:00.000-07:00

I hear people talk (sometimes brag) about how they built this incredibly complex formula to perform some difficult task in their model. They’re even proud of the fact that they managed to cram this beast of a formula into a single cell. I’ve even seen some step-by-step examples of how to go about creating monster formulas. Usually the instructions are something like, “Start with simple bits and pieces of logic spread across multiple cells. Then replace a cell reference in your formula with the logic in the cell, and so on.”

Well that’s all well and good. Until a problem crops up in the model and you (or someone else) has to debug your model logic. I can guarantee that when someone else is trying to debug your model and they stumble across this huge conglomeration of nested garbage they will be cursing your name out loud. I am truly bewildered by the following chain of events that seems to occur regularly in the minds of many spreadsheet modelers.

· I realize I need a very complicated formula to give me the result(s) I want.

· I know I have to layout all the pieces of the formula in separate cells so I can understand how the formula is working and debug it before I put it all together.

· I build logic spanning several cells to see how each element of my formula will effect the outcome.

· I combine all the logic into a single cell.

· I delete all the elements I used to create the formula. The very same elements that helped me make sense of the thing to begin with.

· I’m left with a monstrosity in a single cell that contains multiple functions nested several times that seems to give the right answer but no one (including myself) can understand it by just looking at it.

· God help me if I ever need to audit, edit, or try to explain this formula.

I just don’t understand why modelers do this to themselves. If you’ve taken the time to layout a formula across several cells, you know what? Don’t create a headache for yourself and others later by throwing all that mess into a single cell. Just leave it spread out so we can all understand it.

The best spreadsheet models are the ones in which I can click on any cell in the model and see just one or maybe two functions used in any given cell.

Formating a Spread Sheet-1 Inserting Blank Rows

2005-07-26T07:43:00.000-07:00

There will be several posts regarding formating for spread sheets. I will be borrowing from a fellow Excel Guru. Aaron Blood. All of the quotes in the following posts will be from Aaron.

You should always avoid inserting blank columns between periods just for the sake of broken accounting underline formats. Instead use the accounting underline and double-underline features. You can also do some neat tricks with standard cell formatting using a fat white line as a left border. It’s very irritating to not be able to jump to the far left or bottom of a large table of data to see totals just because someone doesn’t know how to use the accounting formats for text.

There is one exception to the rule. That’s when you have a row of like formulas followed by a sum of the row. Since the sum is a different formula than the rest of the row, a blank column prohibits the modeler from accidentally copying over the sum formula when they’re trying to quickly copy/paste formulas across a range.

Have a good Day

USING ADD-INS TWO

2005-07-25T15:15:00.000-07:00

The best reason to use a add-in, is that most of them can cut your time by at least 50 percent when building spread sheets. Most of them have special wizards that walk you through any changes that you want to make. You can google add-in and you will find thousands of them.

Using Add-ins for Excel

2005-07-21T11:14:00.000-07:00

What is a add-in? The add-in is small or large programed spreadsheet that has certian qualities
that help with using excel. I use add-ins all of the time. Some are free some you have to pay for
one add-in I use all of the time is ASAP
This Add-In is Free.
Try it you will like it

Using Validation with Vlookup

2005-07-19T14:19:00.000-07:00

As you can see the drop down there is a list to select from. How you do this, is select from the menu tool bar Data then Validation. A list will pop up asking what type of validation you want
select the list option. From here you will place the location of the list that you want. I always name my list with a range name. It will make it easier to put in the box =Range name

Correcting the N/A error in VLookup

2005-07-18T14:51:00.000-07:00

As you can see the first lookup in the top area shows #N/A
The formula bar shows how to edit the formula to change the result to zero
if there is no match

VLookUp in estimating

2005-07-14T14:45:00.000-07:00

The following sample will show you the formula editor along with the spreadsheet lay out for a simple data base. The first item in the formula is the lookup colunm then the next is the area in which the info resides and the third is the colunm number for the data you want to get. The colunm count is from the first colunm in the data base. In this case it is 2. The fourth item is set to false this way the formula will require and exact match. If not found then it returns #N/A in the next post I will show you how to hide this error

VBLOOKUP FOR EXCEL

2005-07-13T15:01:00.000-07:00

the next post will cover the use of vblookup function in excel and how it can save a lot of time estimating

Conceptual Estimating

2005-07-13T08:10:00.000-07:00

One of my friends asked me today if there are any conceptual estimating programs out in the industry. The answer is yes and no. Let me explain, some software manufactues using a data base say that you can use the information from one job to another. The best way in my opinion is to use Excel. By making a template for each type of project ie hotels, apartments, office ect. You can use the template each time and only have to make minor changes.

I also use a lot of assemblies in estimating, this allows me to cut my time in half on most projects
I also save all the equiment quotes from other jobs and post them in a spread sheet so that I can look them up.

I make templates for central plants and list all of the items required in the plant and 99 percent of time the equipment types and quanites are about the same. You only need to change the sizes.

I use a fit-up assembly for all of the equipment connections and then take of the piping in between the equipment. I can estimate a central plant no matter the size in about 1 hour or less and the end result will be plus or minius 1 percent.

Todays first post

2005-07-12T14:46:00.000-07:00

Well I started this blog to help other estimators with excel and to have a place to post items for help or just let off steam about the industry