Thursday, December 22, 2005

CTRL SHIFT ENTER Formulas in Excel

{=SUM((OFFSET(DB,1,$C$48-1,ROWS(data)-1,1)=$AN7)* (OFFSET(DB,1,$C$57-1,ROWS(data)-1,1)=TRUE)* (OFFSET(data,1,$C$20-1,ROWS(data)-1,1)=BH$2)* OFFSET(DB,1,$C$50-1,ROWS(data)-1,1))}

This is an example of a formula I am working with at the moment. It displays the power of a CTRL SHIFT ENTER formula.

Basically what it allows you to do is to sum specific columns (identified by the OFFSET function) of a database named DB (array of rows and columns of data) conditional on specific requirements.

In its basic form the above formula says to sum a column of data if the values in 3 other relative columns are specific values or TRUE.

This is basically a way of performing a SUMIF for more than one condition.
Let me explain.
A SUMIF formula uses the syntax Lookup_Range,Criteria,Result_Range
This is great if you want to look through a column for specific value and sum the results from an adjacent column.

But what if you want to have two, or more, criteria?

That is where formula like this one performs magnificently. In effect the * s between the parts of the formula act like & s and join the parts of the formula.
So this formula sums from the column OFFSET(DB,1,$C$50-1,ROWS(data)-1,1) where the value in the first range OFFSET(DB,1,$C$48-1,ROWS(data)-1,1) equals the value in $AN7 AND where the value in the second range OFFSET(DB,1,$C$57-1,ROWS(data)-1,1) equals TRUE AND where the value in the third column OFFSET(data,1,$C$20-1,ROWS(data)-1,1) equals the value in cell BH$2.

And to make it all work, hit CTRL SHIFT ENTER when entering or editing the formula.


Posted by: Jethro | 4:54 PM |

Friday, December 16, 2005

Using the CTRL Key

I was going to write another article about letting go the mouse. But instead I am going to point you to Jeff Dance's Excel Tricks blog where he has written a great article on using the CTRL key.

Ctrl + 1       Enter the format dialog box for most formatting
Ctrl + Shift + F       Enter the format dialog box for formatting font without having to stretch the finger for Ctrl + 1
Alt + Shift + F1       Insert New Worksheet
F7       Spell check selected text and or document.
F11       Create chart.
Ctrl + Shift +       Enter the current time
Ctrl +       Enter the current date
Shift + F3       Open the Excel formula window.
Shift + F5       Bring up search box.
Ctrl + A       Select all contents of the worksheet.
Ctrl + B       Bold highlighted selection.
Ctrl + I       Italic highlighted selection.
Ctrl + K       Insert link.
Ctrl + U       Underline highlighted selection.
Ctrl + 5       Strikethrough highlighted selection.
Ctrl + P       Bring up the print dialog box to begin printing.
Ctrl + Z       Undo last action.
Ctrl + F9       Minimize current window.
Ctrl + F10       Maximize currently selected window.
Ctrl + F6       Switch between open workbooks / windows.
Ctrl + Page up       Move between Excel work sheets in the same Excel document.
Ctrl + Page down       Move between Excel work sheets in the same Excel document.
Ctrl + Tab       Move between Two or more open Excel files.
Ctrl + Shift + !       Format number in comma format.
Ctrl + Shift + $       Format number in currency format.
Ctrl + Shift + #       Format number in date format.
Ctrl + Shift + %       Format number in percentage format.
Ctrl + Shift + ^       Format number in scientific format.
Ctrl + Shift + @       Format number in time format.
Ctrl + Arrow key       Move to next section of text.
Ctrl + Space       Select entire column.
Shift +       Space Select entire row.


Posted by: Jethro | 10:09 PM |

Wednesday, December 14, 2005

Speeding up Excel

The following document officially applies to versions of Excel prior to Office 97. However the spreadsheet creation techniques still have value today.

XL: Optimizing Worksheets for Fastest Calculation
The information in this article applies to:
• Microsoft Excel for Windows 3.0, 3.0a, 4.0, 4.0a, 4.0c, 5.0, 5.0c
• Microsoft Excel for Windows NT 5.0
• Microsoft Excel for Windows 95 7.0
• Microsoft Excel 97 for Windows

Summary
In Microsoft Excel, recalculation performance is affected by the way data and formulas are arranged on the worksheet. The following list contains tips for optimizing your worksheet to improve recalculation speed:
• Organize your worksheets vertically. Use only one or two screens of columns, but as many rows as possible. A strict vertical scheme promotes a clearer flow of calculation.
• When possible, a formula should refer only to the cells above it. As a result, your calculations should proceed strictly downward, from raw data at the top to final calculations at the bottom.
• If your formulas require a large amount of raw data, you might want to move the data to a separate worksheet and link the data to the sheet containing the formulas.
• Formulas should be as simple as possible to prevent any unnecessary calculations. If you use constants in a formula, calculate the constants before entering them into the formula, rather than having Microsoft Excel calculate them during each recalculation cycle.
• Reduce, or eliminate, the use of data tables in your spreadsheet or set data table calculation to manual.
• If you only need a few cells to be recalculated, replace the equal signs (=) of the cells you want to be recalculated. This is only an improvement if you are calculating a very small percentage of the formulas on your worksheet.
• When a certain group of formulas must be recalculated a great number of times, then it may be helpful to replace the equal sign (=) in the formulas that you do not need to recalculate with a unique string that does not appear elsewhere. The formulas without the equal signs will not be recalculated (they are no longer considered formulas). When Microsoft Excel has recalculated the formulas that still contain equal signs, search for the unique string and restore the =.
• Activate the Automatic Except Tables option. To do this, follow the appropriate procedure below for your version of Microsoft Excel:

Microsoft Excel 5.0 and Later
1. From the Tools menu, choose Options, and select the Calculation tab.
2. On the Calculation tab, select the Automatic Except Tables option.

Microsoft Excel 4.x and Earlier
1. From the Options menu, choose Calculation.
2. Select the Automatic Except For Tables option.
• Do not use the Precision As Displayed option on the Calculation tab (the Calculation Options dialog box in Microsoft Excel 4.x and earlier). This option will slow recalculation because Microsoft Excel will have to round the numbers as it recalculates.

References
"User's Guide," version 5.0, pages 166-170
"User's Guide 1," version 4.0, pages 167-174
"User's Guide," version 3.0, pages 294-300, 697-700


Posted by: Jethro | 8:37 AM |

Monday, December 05, 2005

Tips for Printing in Excel

Excel has a number of largely unused functions mostly because people don't realise they exist.

In the File | Print and File | Page Setup menus there are several useful functions.

In the Print Menu you can print the Selection, Activesheet(s) (default) or Entire Workbook.


One way to make this more useful is by setting the Rows and Columns to repeat when printing. Repeat includes the first page, so if you are only printing one page they still apply. So if you want to just print a selection of cells that are part way down the page, but still want to print the row or column headings (or both) then this next tip is very useful.

Now select your selection to be printed. Click File Print (CTRL P), choose Selection in the Print What box and click Preview. You should see your selection under the row 1 header. Once the other print settings are to your satisfaction then print to your printer.

This can be used with great results for multi-page printouts of databases, cashflows, and other large tables where the header column and/or row information is needed.


Posted by: Jethro | 12:38 PM |