Excel Page 10

Home Browsers MsOffice 97 & 2000 Site Search Windows

MsOffice Index 1 | MsOffice Index 1I | Microsoft PowerPoint 97 | Microsoft Bug of the Month | Excel Page 9 |   
[Updated 06/13/06]


Excel's 7 Nested Function Limit

It's quite common for users to encounter the 7 nested Functions in Excel. For those not sure, a nested function is where we use the result of one function as the argument for another.

If your functions are all IF Functions, then see Lookup Method. This by far the most efficient and flexible way to get around the limit on IF functions.

Ok, so let's jump in with an example of where you may use this other method which also gets around the limit.

Let's say you have a column of data (Column "A"). In this column there are a total of 70 different numeric cells. That is, 1 to 70 in any order.

Based on the content of each cell, we need to perform a Tax calculation by multiplying a number by a defined constant named "Tax" based on 8 different criteria

CRITERIA

IF A1 = 1 to 7 then 7*Tax
IF A1 = 8 to 10 then 10*Tax
IF A1 = 11 to 20 then 20*Tax
IF A1 = 21 to 30 then 30*Tax
IF A1 = 31 to 40 then 40*Tax
IF A1 = 41 to 50 then 50*Tax
IF A1 = 51 to 60 then 60*Tax
IF A1 = 61 to 70 then 70*Tax

What we need to do is make all the above criteria into 2 Named Formulas

Here is how, assuming we want the results in Column "B" relative to the row in Column "A".

1) Select B1 and go to Insert| Name| Define.
2) Now in the "Names in Workbook" box enter: Criteria1
3) In the "Refers to" box enter:
=IF(AND(D8| 0,D8<8),7*Tax,IF(AND(D8| 7,D8<11),10*Tax,IF(AND(D8| 10,D8<21),20*Tax,IF(AND(D8| 20,D8<31),30*Tax))))
4) Now click Add.
2) Now in the "Names in Workbook" box enter: Criteria2
3) In the "Refers to" box enter:
=iF(AND(A1| 30,A1<41),40*Tax,IF(AND(A1| 40,A1<51),50*Tax,IF(AND(A1| 50,A1<61),60*Tax,IF(AND(A1| 60,A1<71),70*Tax))))
4) Now click Add

Now we need to add the defined constant named "Tax"

1) Now in the "Names in Workbook" box enter: Tax
2) In the "Refers to" box enter: =0.15
3) Finally click Add and then OK.

Now add numbers 1 to 70 on the Worksheet starting from A1. Use the Fill Handle (while holding down Ctrl) of A1 after entering number 1 into A1 and reselecting it.

All that is left now is to make use of our 2 named formulas that total 8 different criteria. So, in B1 simply Enter: =IF(Criteria1,Criteria1,Criteria2) Then copy down.


Excel Named Constants

Let's look at Named Constants. These are simply values that are given a meaningful name. For example, let's say you have a Workbook that has many formulas using the percentage of 20% and this represents some sort of 'special tax'. You can replace all those occurrences of 20% with a Named Constant like "SpecialTax". Here is how you do this.

1) Go to Insert| Name| Define and in the "Names in workbook" box type: SpecialTax
2) Now click in the "Refers to" box and type: =0.2 then click Add.

Now you can replace formulas like: =SUM(Yr2005Figures)*20% with: =SUM(Yr2005Figures)*SpecialTax

If you already have many Excel formulas using the 20% in their calculations, you can either use Edit | Replace or 
Insert | Name | Apply to replace them with ease.

The best thing about Named Constants is that any constant can be changed to another value globally via 
Insert | Name | Define.


Excel Named Formula

Now we have seen how easy Named Ranges and Excel Named Constants are, we can look at a little known ability of Names where they can used to create custom formulas. For example, let's suppose we frequently use a formula like: =SUM(A1:A100)-SUM(B1:B100) and this resides in A101 and is copied across many columns on row 101. It would be better in this case to create a custom formula that does this in each cell on row 101. Here is how;

1) Select cell A101 (this is vital).
2) Go to Insert>Name>Define and in the "Names in workbook" box type: SalesLessCosts
3) Now click in the "Refers to" box and type: =SUM(A1:A100)-SUM(B1:B100) then click Add.

Now you can replace the formula in cell A101 with: =SalesLessCosts. You can also copy this across row 101 and it will change its relative references just as the formula =SUM(A1:A100)-SUM(B1:B100) would. The reason it does this is all down to the fact we selected A101 before going to Insert>Name>Define and used relative references in =SUM(A1:A100)-SUM(B1:B100) when we added it to the "Refers to" box.
You could force full Absolution simply by using: =SUM($A$1:$A$100)-SUM($B$1:$B$100) or relative row absolute column like: =SUM($A1:$A100)-SUM($B1:$B100) or, of course any mix of absolute/relative row/column you desire. This can be a bit confusing at first, but some trial and error along with being aware of the selected cell when going to Insert>Name>Define will help a lot!

Use the Named Formula on Another Worksheet
Let's say you wanted to have these results only on another sheet to where the $A$1:$A$100 and $B$1:$B$100 ranges are. For a standard formula you would use a formula like: =SUM(Sheet1!$A$1:$A$100)-SUM(Sheet1!$B$1:$B$100). However, we cannot use: =Sheet1!SalesLessCosts as Excel will assume you mean the Workbook name and change it accordingly. Without it, Excel will sum the *relative* cells on the same Worksheet as you Enter: =SalesLessCosts
What we need to do is precede the formula name in the "Names in workbook" box of Insert Name dialog with: 'Sheet1'! The single apostrophes are not required when the sheet name has no space, but are when a space character is used as part of the Worksheet name, i.e. "Sheet 1". With this in mind, it pays to use them anyway as it won't matter.

1) Select cell A101 (again, this is vital).
2) Go to Insert>Name>Define and in the "Names in workbook" box replace: SalesLessCosts with 'Sheet1'!SalesLessCosts and click Add.

Now, in the required cell simply Enter: ='Sheet1'!SalesLessCosts


Use a function to count cells between two values in Excel

Are you ready to teach Excel a new trick? Mary Ann Richardson explains how to count the number of cells whose values fall between two particular numbers in a certain range automatically.

Excel has no built-in function that can count the number of cells whose values fall between two numbers in a range. Rather than writing a new formula every time you want to make that calculation, you can build your own function that you can use whenever you wish.

Let's say you've entered a list with the number of years your employees have been at the company in the range H1:H400. You want to know how many of them have been at the company between 10 and 20 years. You can build a function that finds the answer by following these steps:

  1. Press [Alt]F11.
  2. Enter the following code at the prompt:
    Function COUNTBETWEEN(rng, num1, num2)
    COUNTBETWEEN = Application.CountIf(rng, "<=" &
    num2) - Application.CountIf(rng, "<" & num1)
    End Function
  3. Press [Alt]F11.

To use the function, enter the following formula in a blank cell:

   =COUNTBETWEEN(H1:H400,10,20)

The function will return the number of employees whose tenure is greater than or equal to 10 (num1) and less than or equal to 20 (num2). You can subsequently use this function on any other range of data.


Fill Handle

The Fill Handle in Excel is possibly one of Excels most under utilized features. For those of you that don't know of it, it's the small black square in the bottom right of the active cell.

In it's simplest form it will increment any series of numbers. For example, if you type the number 1 in any cell and then the number 2 in a cell that adjoins it, you can use the Fill Handle to increment up to any number desired. To do this you simply select you two cells (starting from the one with the number 1) and then hover your mouse pointer over the Fill Handle (until it changes to a small black cross), left click and drag in the direction you want the incremented numbers to show. You can also do the same by entering any starting number in any cell, selecting the cell, holding down the Ctrl key and then dragging down with the Fill Handle. If you do not hold down the Ctrl key Excel will simply copy the same number.

The best bit about this feature is that we can alter the amount we increment by simply typing any two numbers we want eg 5 and 10 would result in 5, 10, 15, 20, 25 etc. If you wanted a blank cell between each number simply use the method below:

  1. Type 5 in cell A1

  2. Leave A2 blank

  3. Type 10 in cell A3

  4. Leave A4 blank

  5. Select cells A1:A4

  6. Drag down using the Fill Handle

The exact same principle applies to dates, after all dates in Excel are only numbers (Serial Values).

The other thing that you can do with the Fill Handle is drag it up or to the left to clear the contents of selected cell(s). You can even insert or delete rows or columns by holding down the SHIFT key while dragging the fill handle.

Lets assume you have a column of data in cells A1:A500 and you place a formula into cell B1. Normally you would copy and paste the formula down to row 500, but instead of this try double clicking the Fill Handle. The whole thing is done for you and will stop at the first blank cell in column A! The same thing happens if you put two different numbers in cells B1 and B2, select both cells then double click the Fill Handle.

The other little known feature is Excels pop-up Fill menu you get when you right click on the Fill Handle and drag. Try this.

  1. Type any number in any cell

  2. Select the cell

  3. Right click on the Fill Handle

  4. Drag down and then release

  5. Select Fill Series

By using this method you eliminate the need for the secondary number. Now repeat steps 1 to 4 and for step 5 select Series instead of Fill Series. The option here are:

As you can see this option allows many choices and is very useful for incrementing dates! The best way by far to familiarize yourself with this feature is to jump straight in and have a go.

You may also have noticed when we right clicked the Fill Handle and dragged we had many other options available on the Pop-up menu. The Fill day, Fill months etc will only be available if the cell(s) contain a date. But you can also Copy cells, Fill Values and Fill formats. This can be particularly useful, but there is another Pop-up menu that is better for this.

  1. Type any formula into any cell

  2. Right click on the cell border (not the Fill Handle)

  3. Drag down then release.

As you will see you now have 10 options to choose from. Some of these are short-cut methods of the Paste Special feature. If you hold down the Alt key while dragging you can change sheets by hovering over the sheet name tab! I find the Copy here as values only particularly useful when converting formulas to permanent values. Try this example:

  1. Type any number of formulas into any adjoining cells

  2. Select all these cells and right click on the border.

  3. Now drag down just one row

  4. With the right mouse button still held down drag back up one row to where you started

  5. Now release and choose Copy here as values only

This method is far quicker and easier than using Edit | Paste Special-Values!
To find out what the other options do, just jump right in and try them.