Thursday, July 28, 2005

Sum the value of a cell across multiple worksheets

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:

  1. Start Excel. A new, blank workbook appears.
  2. In cell B3 in Sheet1, type 20.
  3. In cell B3 in both Sheet2 and Sheet3, type 30.
  4. In cell A1 in Sheet1, type the following formula:
    =SUM(Sheet1:Sheet3!B3)
  5. 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

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:

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

    This data will appear in each sheet.
  4. 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

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.

Wednesday, July 27, 2005

Spread Sheet Formating 2

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.

Tuesday, July 26, 2005

Formating a Spread Sheet-1 Inserting Blank Rows

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

Monday, July 25, 2005

USING ADD-INS TWO

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.

Thursday, July 21, 2005

Using Add-ins for Excel

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

Tuesday, July 19, 2005

Using Validation with Vlookup

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

Monday, July 18, 2005

Correcting the N/A error in VLookup

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

Thursday, July 14, 2005

VLookUp in estimating

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


Wednesday, July 13, 2005

VBLOOKUP FOR EXCEL

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

Conceptual Estimating

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.

Tuesday, July 12, 2005

Todays first post

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