Large Function

I have been playing around with all kinds of functions and arrays. It's a geek fest!

Let's start with a simple Function that can be really useful - LARGE.

The Large function searches through a range of cells and returns the largest value or the 2nd largest or 3rd largest..... whatever you specify.

In this example, Excel goes through the range in B4 through B11 and returns the highest value which is 800. If the formula had read =LARGE(B4:B11,2) then the resulting answer would be 500 as that is the 2nd highest number. Quick way to retrieve something.

And of course, Microsoft thinks of everything so the function SMALL does the exact opposite.

Using Functions Together

5 TExt functions and Date Function Combined......Whew

I am putting together an 8 hour Functions seminar for the Indianapolis CPA Society and I think I am going to restruture it a bit. Usually when function are taught, they are taught as isolated examples. However, a lot of times you really need to use multiple functions together. So, I am going to show some useful examples that people can actually use. I am jumping the gun a bit here but I just put together a useful example and thought I would share it with you.


Frequently dates don't import the way you wish and you need to do a little 'surgery' on them. In the example below, I needed to use 4 Text Functions and 1 Date function. Now, obviously the first time or two you do this, it'll take awhile but then it will be second nature......... well maybe :)

In this example, dates that were imported into Column A are not in a date format familiar to Excel.
Using 5 different functions: 4 Text functions and 1 Date function we can extract, combine and then display Column A values in a date format.










I have broken it apart so that you can identify all the different functions.
The Left function retrieves the 4 leftmost characters in the cell
The Find function searches for the period in the cell and determines that there are 4 characters to get to the period plus 1 = 5 characters
The Mid function goes to the cell and uses the 5 characters + 1 character as the starting point and then grabs the 2 next characters
The Right function grabs the last 2 characters to the rightAll the functions are housed within the Date function which then provides joins the characters together in a familiar date format.
Stayed tuned... let's see what else I can come up with.
If you use any useful combination of functions please let me know. I love to use practical examples.

View 2 Sheets in the Same File at the Same Time

Sorry- I guess you could tell that I took a few days off... I was enjoying sun and wonderful weather on the Massachusetts coast.
Here is a picture of Rockport Harbor.. absolutely beautiful..

Today's topic is Viewing 2 Excel Worksheets in the Same File at the Same Time (that's a mouthful isn't it).

Lotus allowed you to do a 3D perspective oh..so many years ago and while Excel does not have a specific command to view 2 or more sheets in the same file at the same time, it can still be done.

The easiest way to view2 or more worksheets at the same time on your computer screen is to open a second window. To do this select the View Tab and then in the Window group, click New Window. It doesn’t look like anything happens when you do this however if you look up at the title bar, you will now see a :2 after the file name to show that a second copy (window) has been made. You can then use each window to display and edit different parts of the same workbook. The files are mirror images of each other and any changes is actually appearing in both at the same time.

I recommend that you close out the window:2 first. If you don't you will end up with a file named :2 which will drive you crazy over time as you can't get rid of the :2.

That's all for today. Have a great week.

Sorry I haven't updated this in awhile but I will get better. Today, I wanted to talk about grouping sheets together as people start to think about budgets and forecasts for next year.

Grouping is interesting because Excel never talks about it and if you go into Help to read about Grouping it talks about Group and Outline which is a totally different feature. In other words, unless you tripped over it or someone told you about it- you probably have never heard of it.

Quite simply, it allows you to select sheets and anything that you type, format etc on one sheet is automatically replicated on other sheets. It is a great timesaver.

Grouping Sheets
Getting ready to create budgets for 10 departments? Don’t create a budget and then copy it 10 times – instead use grouping. Not only is it faster- it also ensures all your original formatting is retained. Other uses for grouping include printing. Perhaps you want to print Sheet 2, 5 and 6 – the easy way to print them all at once is to group the sheets and then select print. Another use for Grouping is to apply headers and footers to all your sheets at the same time.

Methods for Efficiently Updating/Moving Data Grouping Sheets
Grouping sheets allows you eliminate the need of copying cells in a worksheet to another and then reformatting. Data entered into a group of worksheets is actually placed into each of the worksheets. Any formatting is also included. This feature is particularly useful if you need to have similar worksheets set up in the same format.

To group sheets adjacent sheets:
1. Select the first sheet.
2. Press Shift and hold it down.
3. Click on the last contiguous sheet in the group.
The title bar now shows the word Group after the book name and that all the sheets in the group are active.

Anything that you type on one of the group sheets will automatically be copied simultaneously onto all of the sheets in the selected group including formatting techniques such as changing column widths. Remember to ungroup the sheets when finished so that you can individualize them.

--Use the CTRL key if you are selecting non-adjacent sheets

Deselect Tab
To deselect an individual worksheet, press CTRL + the specific tab.

Ungroup
To ungroup the sheets, select a worksheet tab that was not in the group. You can also right-click and select Ungroup.

Have fun and remember you can ungroup them- make changes and then group them again.

Range Names at Worksheet Level

Range Names at Worksheet Level

I was putting together some information on range names and realized that when I posted my information on range names (under Basic Tips) that I neglected to mention a new feature in Excel 2007.
If you create a range name using the Name Manager icon, you can specify if the range name should be at the workbook scope (this is the default) or you can specify a specific sheet for that range name to be used on. In other words, in Excel 2007, you can actually use a range name more than once as long as it is at sheet level. If you do create a range name at the sheet level, it is only recognized on that particular sheet.

Click on the Formula Ribbon and then go to Name Manager and click New...
Type in a range name and then click the drop-down arrow beside Scope if you want it to be sheet specific. As I mentioned earlier, the default is workbook level.

In all other versions of Excel, you can only use a range name once in a workbook.

Scenario Manager - What-IF Analysis

Scenario Manager - What IF

Where has the week gone? It is Wednesday already! I received some excellent news yesterday. After five and half months, CFO Resources / CPASelfstudy.com has finally been approved as a CPE sponsor in New York. Hurray!

But I do need to finish up my What-IF section by talking about Scenario Manager. This is a cool feature and is available in all versions of Excel- you just have to figure out where Microsoft hid it. In earlier versions, you have to go to Tools>Addins. In Excel 2007, look under the What-if icon on the Data ribbon.


Scenarios allows you to substitute values in a number of cells and save that condition under a unique name. By invoking different scenarios, up to 32, you can easily demonstrate to your customers and managers the impact of different events or possibilities.

• A scenario is particularly useful in creating budgets and forecasts when you have a few key variables that are subject to change.
• You can use scenarios to forecast different outcome based upon changes to these variables in your model. You can create and save these scenarios with different names and then display them.
• To compare several scenarios, you can even create a report that summarizes them on the same page- similar to a data table. The report can list the scenarios side by side or summarize them in a pivot table.
  
  Before creating a scenario you need to determine which cells will be changed. Normally these should be your input or changing cells. You can have up to 32 changing cells in a scenario.

To Use Scenarios
1. Click the Data tab on the Ribbon.
2. Click the What-if Analysis button and click Scenario Manager from the menu.
3. Click the Add button.
4. Enter a name for your scenario in the Scenario Name box.
5. Click in the Changing Cells box.
6. Hold down the Ctrl key and select the cells in your worksheet that you wish to change.
7. In the Scenario Values dialog box, enter new values.
8. Click OK.
9. To Apply a Scenario to your worksheet, select the desired Scenario and then click Show.
10. Click Close.

Careful! Adding scenarios to a workbook will change the underlying data so it is highly recommended that you save a backup of your original workbook first.
Once you have created your scenarios, you can create side-by-side Summary reports to compare your scenarios.
Take a look at it if you get a chance. Tomorrow I will try to post some screenshots and an example.

Navigating Tip for Excel

Hey- It's 4:17 Friday of Labor Day weekend... I should be elsewhere and so should you.

I'm heading out to celebrate our 16th wedding anniversary... hurrah.. at Mamacarrollans. I have been wanting to go there forever. I'll let you know how it is. And then tomorrow- off to Chicago to savor a real chicago dog and hang out at Navy Pier for awhile.

But since you are reading this, I should probably mention something about Excel.

This is actually an excerpt for a CPE course I wrote called Tools and Tips for Accountants.

? How do I navigate between sheets?
If you are one of those people with more than 6 or 7 sheets in a workbook, then you will find this tip useful.

-Right-click on the navigation bar and Excel will display a listing of all the sheets by name. Select

the sheet you want and Excel will automatically move you to that page.

Have a great Labor Day Weekend.

Spinners - What IF

Spinners

I forgot to mention that down at the bottom of the blog, is a link to a video I did on Goalseek. Check it out when you get a chance. And don't forget to feed my little fishies....

Today, I wanted to talk about spinners. If I have time I will put some screen shots out on the different spinner steps but as usual I am behind schedule :)

A spinner, or Excel refers to it as a scroll bar sometimes, allows you to click and select different numbers. A spinner allows you to generate a large number of scenarios that vary each input between its high and low value. A spinner is a button that is linked to a given cell. As you click the spinner button the value of the linked cell changes and you can see the impact of these changes. This can be really useful if someone else is going to use the worksheet and is not very knowledgeable about Excel.

To create a spinner:
If the Developer tab is not available, display it by clicking on the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

++If you are using Excel 2003, you would find the spinner button on the Form Toolbar. You can find the Form toolbar under the View menu.

1. On the Developer tab, in the Controls group, click Insert.
2.Under Form Controls, click Scroll bar .
3. Click the worksheet location where you want the upper-left corner of the scroll bar to appear.
4. Right-click and select Format Control
5. Select the values you want
6. Click OK
++ Please note that the maximum value cannot exceed 30000

For spinner control set the cell link to an adjacent cell on the form.
If the cell is locked, the spinner control will generate a warning when you use it after protecting the worksheet. Avoiding this problem is easy—with the worksheet unprotected, simply right-click on the cell associated with the spinner and choose Format Cells... Select the Protection tab and deselect the Locked checkbox. You can then protect the worksheet and use the spinner control without any problems.

Goal Seek - What If with Excel

Okay- my theme this week, in case you have not guessed, is What-If. Let's talk about Goal Seek. This is one feature that some people are familiar with simply because you were able actually find it in early versions of Excel under the Data menu unlike Solver and Scenario Manager that are add-in programs that you actually have to look for.

Goal seek is great to use if you are doing a simple and quick what-if. The reason for this is that Excel only allows you to change one cell with Goal Seek as opposed to 32 in Scenarios! Also, the cell you are changing cannot contain a formula. However even though it is a bit limited, it is still useful to know how to use it when you have something simple to determine. It certainly beats typing in a number, pressing Enter and looking at the answer and then typing in another number and seeing what that answer is... yes... some people still do that.. you know who you are. Try Goal Seek instead.

Goal Seek

Goal seek allows the user to find a specific value for a cell included in a calculation by changing one other variable in the equation. It is a wonderful tool if you are trying to work through a what-if scenario.
For example, suppose you want to obtain a home loan but can only afford $800 a month. With Goal Seek, you could determine the maximum home purchase price that you could afford quickly.

Goal Seek requires 3 parameters.
1. The cell you want to change. In the example above, this would be the loan amount.
2. The value to which you want to change the target cell.
3. The cell you want to change to achieve the target amount.

To Use Goal Seek
1. Click the Data tab on the Ribbon.
2. Click the What-if Analysis button and click Goal Seek from the menu.
3. In the Set Cell Box, type the cell reference of the cell that contains the formula you want to change.
4. In the To Value box, type in the value to which you want to change the cell.
5. In the By changing cell box, enter the cell reference of the cell that contains the value you want to adjust.
6. Click OK.
7. Click OK.

I have a screenshot below example below: I wanted to change the current monthly payment in H6 to $800 by changing D6 which was the value of the loan.