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.