Get documents about "Formulas
Shared by: HC11121205445
-
Stats
- views:
- 37
- posted:
- 12/11/2011
- language:
- pages:
- 53
Document Sample
Formulas
NOTE: This page is no longer updated. Most of the topics here are now covered on
other pages, or have pages of their own. However, I will leave this page intact and
available. See the Topics page for a complete list of topics covered on my web site.
Array Formulas
Many of the formulas described here are Array Formulas, which are a special type of formula
in Excel. If you are not familiar with Array Formulas
Array To Column
Sometimes it is useful to convert an MxN array into a single column of data, for example for charting (a
data series must be a single row or column).
Averaging Values In A Range
You can use Excel's built in =AVERAGE function to average a range of values. By using it
with other functions, you can extend its functionality.
For the formulas given below, assume that our data is in the range A1:A60.
Averaging Values Between Two Numbers
Use the array formula
=AVERAGE(IF((A1:A60>=Low)*(A1:A60<=High),A1:A60))
Where Low and High are the values between which you want to average.
Nasir Mustafa MBA(HRM)
Averaging The Highest N Numbers In A Range
To average the N largest numbers in a range, use the array formula
=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))
Change "1:10" to "1:N" where N is the number of values to average.
Averaging The Lowest N Numbers In A Range
To average the N smallest numbers in a range, use the array formula
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
=AVERAGE(SMALL(A1:A60,ROW(INDIRECT("1:10"))))
Change "1:10" to "1:N" where N is the number of values to average.
In all of the formulas above, you can use =SUM instead of =AVERAGE to sum, rather
than average, the numbers.
Counting Values Between Two Numbers
If you need to count the values in a range that are between two numbers, for example between
5 and 10, use the following array formula:
=SUM((A1:A10>=5)*(A1:A10<=10))
To sum the same numbers, use the following array formula:
=SUM((A1:A10>=5)*(A1:A10<=10)*A1:A10)
Counting Characters In A String
The following formula will count the number of "B"s, both upper and lower case, in the string in B1.
=LEN(B1)-LEN(SUBSTITUTE(SUBSTITUTE(B1,"B",""),"b",""))
Date And Time Formulas
A variety of formulas useful when working with dates and times are described on
the Date and time page.
Other Date Related Procedures are described on the following pages.
Nasir Mustafa MBA(HRM)
Adding Months And Years
The DATEDIF Function
Date Intervals
Dates And Times
Date And Time Entry
Holidays
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Julian Dates
Duplicate And Unique Values In A Range
The task of finding duplicate or unique values in a range of data requires some complicated
formulas. These procedures are described in Duplicates.
Dynamic Ranges
You can define a name to refer to a range whose size varies depending on its contents. For example, you
may want a range name that refers only to the portion of a list of numbers that are not blank. such as only
the first N non-blank cells in A2:A20. Define a name called MyRange, and set the Refers To property to:
=OFFSET(Sheet1!$A$2,0,0,COUNTA($A$2:$A$20),1)
Be sure to use absolute cell references in the formula. Also see then Named Ranges page for more
information about dynamic ranges.
Finding The Used Part Of A Range
Suppose we've got a range of data called DataRange2, defined as H7:I25, and that
cells H7:I17 actually contain values. The rest are blank. We can find various properties
of the range, as follows:
To find the range that contains data, use the following array formula:
=ADDRESS(ROW(DataRange2),COLUMN(DataRange2),4)&":"&
ADDRESS(MAX((DataRange2<>"")*ROW(DataRange2)),COLUMN(DataRange2)+
COLUMNS(DataRange2)-1,4)
This will return the range H7:I17. If you need the worksheet name in the returned range,
Nasir Mustafa MBA(HRM)
use the following array formula:
=ADDRESS(ROW(DataRange2),COLUMN(DataRange2),4,,"MySheet")&":"&
ADDRESS(MAX((DataRange2<>"")*ROW(DataRange2)),COLUMN(DataRange2)+
COLUMNS(DataRange2)-1,4)
This will return MySheet!H7:I17.
To find the number of rows that contain data, use the following array formula:
=(MAX((DataRange2<>"")*ROW(DataRange2)))-ROW(DataRange2)+1
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
This will return the number 11, indicating that the first 11 rows of DataRange2 contain data.
To find the last entry in the first column of DataRange2, use the following array formula:
=INDIRECT(ADDRESS(MAX((DataRange2<>"")*ROW(DataRange2)),
COLUMN(DataRange2),4))
To find the last entry in the second column of DataRange2, use the following array formula:
=INDIRECT(ADDRESS(MAX((DataRange2<>"")*ROW(DataRange2)),
COLUMN(DataRange2)+1,4))
First And Last Names
Suppose you've got a range of data consisting of people's first and last names.
There are several formulas that will break the names apart into first and last names
separately.
Suppose cell A2 contains the name "John A Smith".
To return the last name, use
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-
LEN(SUBSTITUTE(A2," ","")))))
To return the first name, including the middle name (if present), use
=LEFT(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-
LEN(SUBSTITUTE(A2," ",""))))-1)
To return the first name, without the middle name (if present), use
=LEFT(B2,FIND(" ",B2,1))
We can extend these ideas to the following. Suppose A1 contains the
Nasir Mustafa MBA(HRM)
string "First Second Third Last".
Returning First Word In A String
=LEFT(A1,FIND(" ",A1,1))
This will return the word "First".
Returning Last Word In A String
=RIGHT(A1,LEN(A1)-MAX(ROW(INDIRECT("1:"&LEN(A1)))
*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")))
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
This formula in as array formula.
(This formula comes from Laurent Longre). This will return the word "Last"
Returning All But First Word In A String
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))
This will return the words "Second Third Last"
Returning Any Word Or Words In A String
The following two array formulas come compliments of Laurent Longre. To return any single word from a
single-spaced string of words, use the following array formula:
=MID(A10,SMALL(IF(MID(" "&A10,ROW(INDIRECT
("1:"&LEN(A10)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A10)+1))),
B10),SUM(SMALL(IF(MID(" "&A10&" ",ROW(INDIRECT
("1:"&LEN(A10)+2)),1)=" ",ROW(INDIRECT("1:"&LEN(A10)+2))),
B10+{0,1})*{-1,1})-1)
Where A10 is the cell containing the text, and B10 is the number of the word you want to get.
This formula can be extended to get any set of words in the string. To get the words from M for N words
(e.g., the 5th word for 3, or the 5th, 6th, and 7th words), use the following array formula:
=MID(A10,SMALL(IF(MID(" "&A10,ROW(INDIRECT
("1:"&LEN(A10)+1)),1)=" ",ROW(INDIRECT("1:"&LEN(A10)+1))),
B10),SUM(SMALL(IF(MID(" "&A10&" ",ROW(INDIRECT
("1:"&LEN(A10)+2)),1)=" ",ROW(INDIRECT("1:"&LEN(A10)+2))),
B10+C10*{0,1})*{-1,1})-1)
Where A10 is the cell containg the text, B10 is the number of the word to get, and C10 is the number of
words, starting at B10, to get.
Note that in the above array formulas, the {0,1} and {-1,1} are enclosed in array braces (curly brackets
{} ) not parentheses.
Nasir Mustafa MBA(HRM)
Grades
A frequent question is how to assign a letter grade to a numeric value. This is simple. First create a define
name called "Grades" which refers to the array:
={0,"F";60,"D";70,"C";80,"B";90,"A"}
Then, use VLOOKUP to convert the number to the grade:
=VLOOKUP(A1,Grades,2)
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
where A1 is the cell contains the numeric value. You can add entries to the Grades array for other grades
like C- and C+. Just make sure the numeric values in the array are in increasing order.
High And Low Values
You can use Excel's Circular Reference tool to have a cell that contains the highest ever reached value.
For example, suppose you have a worksheet used to track team scores. You can set up a cell that will
contain the highest score ever reached, even if that score is deleted from the list. Suppose the score are
in A1:A10. First, go to the Tools->Options dialog, click on the Calculation tab, and check the Interations
check box. Then, enter the following formula in cell B1:
=MAX(A1:A10,B1)
Cell B1 will contian the highest value that has ever been present in A1:A10, even if that value is deleted
from the range. Use the =MIN function to get the lowest ever value.
Another method to do this, without using circular references, is provided by Laurent Longre, and uses the
CALL function to access the Excel4 macro function library.
Left Lookups
The easiest way do table lookups is with the =VLOOKUP function. However, =VLOOKUP requires
that the value returned be to the right of the value you're looking up. For example, if you're
looking up a value in column B, you cannot retrieve values in column A. If you need to
retrieve a value in a column to the left of the column containing the lookup value, use
either of the following formulas:
=INDIRECT(ADDRESS(ROW(Rng)+MATCH(C1,Rng,0)-1,COLUMN(Rng)-ColsToLeft)) Or
=INDIRECT(ADDRESS(ROW(Rng)+MATCH(C1,Rng,0)-1,COLUMN(A:A) ))
Where Rng is the range containing the lookup values, and ColsToLeft is the number of columns
to the left of Rng that the retrieval values are. In the second syntax, replace "A:A" with the
column containing the retrieval data. In both examples, C1 is the value you want to look up.
Nasir Mustafa MBA(HRM)
Minimum And Maximum Values In A Range
Of course you can use the =MIN and =MAX functions to return the minimum and maximum
values of a range. Suppose we've got a range of numeric values called NumRange.
NumRange may contain duplicate values. The formulas below use the following example:
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Address Of First Minimum In A Range
To return the address of the cell containing the first (or only) instance of the minimum of a list,
use the following array formula:
=ADDRESS(MIN(IF(NumRange=MIN(NumRange),ROW(NumRange))),COLUMN(NumRange),4)
This function returns B2, the address of the first '1' in the range.
Address Of The Last Minimum In A Range
To return the address of the cell containing the last (or only) instance of the minimum of a list,
use the following array formula:
=ADDRESS(MAX(IF(NumRange=MIN(NumRange),ROW(NumRange)*(NumRange<>""))),
COLUMN(NumRange),4)
This function returns B4, the address of the last '1' in the range.
Address Of First Maximum In A Range
To return the address of the cell containing the first instance of the maximum of a list,
use the following array formula:
=ADDRESS(MIN(IF(NumRange=MAX(NumRange),ROW(NumRange))),COLUMN(NumRange),4)
This function returns B1, the address of the first '5' in the range.
Address Of The Last Maximum In A Range
Nasir Mustafa MBA(HRM)
To return the address of the cell containing the last instance of the maximum of a list,
use the following array formula:
=ADDRESS(MAX(IF(NumRange=MAX(NumRange),ROW(NumRange)*(NumRange<>""))),
COLUMN(NumRange),4)
This function returns B5, the address of the last '5' in the range.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Most Common String In A Range
The following array formula will return the most frequently used entry in a range:
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))
Where Rng is the range containing the data.
Ranking Numbers
Often, it is useful to be able to return the N highest or lowest values from a range of data.
Suppose we have a range of numeric data called RankRng. Create a range next to
RankRng (starting in the same row, with the same number of rows) called TopRng.
Also, create a named cell called TopN, and enter into it the number of values you want to
return (e.g., 5 for the top 5 values in RankRng). Enter the following formula in the first cell in
TopRng, and use Fill Down to fill out the range:
=IF(ROW()-ROW(TopRng)+1>TopN,"",LARGE(RankRng,ROW()-ROW(TopRng)+1))
To return the TopN smallest values of RankRng, use
=IF(ROW()-ROW(TopRng)+1>TopN,"",SMALL(RankRng,ROW()-ROW(TopRng)+1))
The list of numbers returned by these functions will automatically change as you change the
contents of RankRng or TopN.
Summing Every Nth Value
You can easily sum (or average) every Nth cell in a column range. For example, suppose you want to sum
every 3rd cell.
Suppose your data is in A1:A20, and N = 3 is in D1. The following array formula will sum the values in
Nasir Mustafa MBA(HRM)
A3, A6, A9, etc.
=SUM(IF(MOD(ROW($A$1:$A$20),$D$1)=0,$A$1:$A$20,0))
If you want to sum the values in A1, A4, A7, etc., use the following array formula:
=SUM(IF(MOD(ROW($A$1:$A$20)-1,$D$1)=0,$A$1:$A$20,0))
If your data ranges does not begin in row 1, the formulas are slightly more complicated. Suppose our data
is in B3:B22, and N = 3 is in D1. To sum the values in rows 5, 8, 11, etc, use the following array
formula:
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
=SUM(IF(MOD(ROW($B$3:$B$22)-ROW($B$3)+1,$D$1)=0,$B$3:B$22,0))
If you want to sum the values in rows 3, 6, 9, etc, use the following array formula:
=SUM(IF(MOD(ROW($B$3:$B$22)-ROW($B$3),$D$1)=0,$B$3:B$22,0))
Miscellaneous
Sheet Name
Suppose our active sheet is named "MySheet" in the file C:\Files\MyBook.Xls.
To return the full sheet name (including the file path) to a cell, use
=CELL("filename",A1)
Note that the argument to the =CELL function is the word "filename" in quotes, not your
actual filename.
This will return "C:\Files\[MyBook.xls]MySheet"
To return the sheet name, without the path, use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
This will return "MySheet"
File Name
Suppose our active sheet is named "MySheet" in the file C:\Files\MyBook.Xls.
To return the file name without the path, use
Nasir Mustafa MBA(HRM)
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
This will return "MyBook.xls"
To return the file name with the path, use either
=LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))) Or
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
=SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",
CELL("filename",A1))),"[",""),"]","")
The first syntax will return "C:\Files\[MyBook.xls]"
The second syntax will return "C:\Files\MyBook.xls"
In all of the examples above, the A1 argument to the =CELL function forces Excel to get the sheet name
from the sheet containing the formula. Without it, and Excel calculates the =CELL function when another
sheet is active, the cell would contain the name of the active sheet, not the sheet actually containing the
formula.
Introduction
Array formulas are a powerful tool in Excel. An array formula is a formula that works with an array, or
series, of data values rather than a single data value. There are two flavors of array formulas: first, there
are those formulas that work with an array or series of data and aggregate it, typically using SUM,
AVERAGE, or COUNT, to return a single value to a single cell. In this type of array formula, the result, while
calculated from arrays, is a single value. We will examine this type of array formula first. The second flavor
of array formulas is a formula that returns a result in to two or more cells. These types of array formulas
return an array of values as their result.
Single Value Result Array Formulas
For example, in its simple form, the formula =ROW(A1:A10) returns the number 1, which is the row
number of the first cell in the range A1:A10. However, if this is entered as an array formula, it will return an
array or series of numbers, each of which is the row number of a cell in the range A1:A10. That is, instead
of returning the single value 1, it returns the array of numbers {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}.
(In standard notation, arrays are written enclosed in curly braces { }.) When using array formulas, you
typically use a container function such as SUM or COUNT to aggregate the array to a single number result.
Expanding on the example above, the formula =SUM(ROW(A1:A10)) entered normally will return a value
of 1. This because in its normal mode, ROW(A1:A10) returns a single number, 1, and then SUM just sums
that single number. However, if the formula is entered as an array formula, ROW(A1:A10) returns the
array of row numbers and then SUM adds up the elements of the array, giving a result of 55 ( = 1 + 2 +
Nasir Mustafa MBA(HRM)
3 + ... + 10).
ENTERING AN ARRAY FORMULA: To enter a formula as an array formula, type
the formula in the cell and press the CTRL SHIFT and ENTER keys at the same
time rather then just ENTER. You must do this the first time you enter the formula
and whenever you edit the formula later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type in the braces -- Excel will
display them automatically. If you neglect to enter the formula with CTRL SHIFT
ENTER, the formula may return a #VALUE error or return an incorrect result.
All formulas on this page are array formulas and thus must be entered with CTRL SHIFT ENTER. You can
download a workbook with the data and formulas on this page here.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Creating Array Formulas Using Arrays Of Data
The IF function can be used in an array formula to test the result of multiple cell tests at one time. For
example, you may want to compute the average of the values in A1:A5 but exclude numbers that are less
than or equal to zero. For this, you would use an array formula with an IF function to test the cell values
and an AVERAGE function to aggregate the result. The following formula does exactly this:
=AVERAGE(IF(A1:A5>0,A1:A5,FALSE))
This formula works by testing each cell in A1:A5 to > 0. This returns an array of Boolean values such as
{TRUE, TRUE, FALSE, FALSE, TRUE}.
A BOOLEAN VALUE is a data type that contains either the value TRUE or the
value FALSE. When converted to numbers in an arithmetic operation, TRUE is
equivalent to 1 and FALSE is equivalent to 0. Most arithmetic functions like SUM
and AVERAGE ignore Boolean values, so those values must be converted to
numeric values before passing them to SUM or AVERAGE.
The IF function tests each of these results individually, and returns the corresponding value from A1:A5 if
True or the value FALSE if false. Fully expanded, the formula would look like the following:
=AVERAGE(IF({TRUE,TRUE,FALSE,FALSE,TRUE},{A1,A2,A3,A4,A5},
{FALSE,FALSE,FALSE,FALSE,FALSE})
Note that the single FALSE value at the end of the original formula is expanded to an array of the
appropriate size to match the array from the A1:A5 range in the formula. In array formulas, all arrays must
be the same size. Excel will expand single elements to arrays as necessary, but will not resize arrays with
more than one element to another size. If the arrays are not of the same size, you will get a #VALUE or in
some cases a #N/A error.
When the IF function evaluates, the following intermediate array is formed: {A1, A2, FALSE, FALSE,
A5}. This is a substitution of the TRUE elements with the values from A1:A5 and the FALSE elements by
FALSE. Since the AVERAGE function is designed within Excel to ignore Boolean values (TRUE or FALSE
values), it will average only elements A1, A2, and A5 ignoring the TRUE and FALSE values. Note that the
Nasir Mustafa MBA(HRM)
FALSE value is not converted to a zero. It is ignored completely by the AVERAGE function.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Array formulas are ideal for counting or summing cells based on multiple
criteria.
Consider the table of data shown to the right. It lists the number of products (column C) in different
categories (column A) sold by various salesman (column B). To calculate the number of Fax machines
sold by Brown, we can use the following array formula:
=SUM((A2:A10="Fax")*(B2:B10="Brown")*(C2:C10))
This function builds three arrays. The first array is a series of TRUE or FALSE values which are the results
of comparing A2:A10 to the word "Fax". (Remember, Excel will expand the single "Fax" element to an
array of items all of which are "Fax".) The second array is also a series of TRUE or FALSE values, the
result of comparing B2:B10 to "Brown". (The single "Brown" element in the formula is expanded to an
array of the appropriate size.) The third array is comprised of the number of units sold from the range
C2:C10. These three arrays are multiplied together. When you multiply two arrays, the result is itself an
array, each element of which is the product of the corresponding elements of the two arrays being
multiplied. For example, {1, 2, 3} times {4, 5, 6} is {1*4, 2*5, 3*6} = {4, 10, 18}. When
TRUE and FALSE values are used in any arithmetic operation, they are given the values 1 and 0,
respectively. Thus in the formula above, Excel expands the formula into the three arrays:
(A2:A10="Fax") {TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE}
(B2:B10="Brown") {TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE,
FALSE}
(C2:C10) {1, 10, 20, 30, 40, 50, 60, 70, 80}
When these array are multiplied, treating TRUE equal to 1 and FALSE equal to 0, we get the array
{1, 0, 0, 0, 0, 0, 60, 0, 0}
Nasir Mustafa MBA(HRM)
which are the quantities of Brown's two Fax sales. The SUM function simply adds up the elements of the
array and return a result of 61, the number of Fax machines sold by Brown.
You may have noticed that the logic of the formula tests Product equals "Fax" AND Salesman equals
"Brown", but nowhere do we use the AND function. Here, we use multiplication to act as a logical AND
function. Multiplication follows the same rules as the AND operator. It will return TRUE (or 1) only when
both of the parameters are TRUE (or <> 0). If either or both parameters are FALSE (or 0), the result is
FALSE (or 0).
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Logical Operations With Array Formulas
In addition to the logical AND operation using multiplication shown above, other logical operations can be
performed arithmetically.
A logical OR operation can be accomplished with addition. For example,
=SUM(IF(((A2:A10="Fax")+(B2:B10="Jones"))>0,1,0))
will count the number of sales (not the number of units sold) in which the product was a Fax OR the
salesman was Jones (or both). Addition acts as an OR because the result it TRUE (or <> 0) if either one or
both of the elements are TRUE (<> 0). It is FALSE ( = 0) only when both elements are FALSE (or 0). This
formula adds two arrays: the results of the comparisons A2:A10 to "Fax", and the results of the
comparisons B2:B10 to "Jones". Each of these arrays is an array of TRUE and FALSE values, each
element being the result of comparing one cell to "Fax" or "Jones". It then adds these two arrays. When
you add two arrays, the result is itself an array, each element of which is the sum of the corresponding
element of the original arrays. For example, {1, 2, 3} + {4, 5, 6} = {1+4, 2+5, 3+6} = {5,
7, 9}. For each element in the sum array (A2:A10="Fax")+(B2:B10="Jones"), if that element is
greater than 0, IF returns 1, otherwise it returns 0. Finally, SUM just adds up the array.
An "exclusive or" or XOR operation is a comparison that returns TRUE when exactly one of the two
elements is TRUE. XOR is FALSE if both elements are TRUE or if both elements are FALSE.
Arithmetically, we can use the MOD operator to simulate an XOR operation. For example, to count the
number of sales in which the product was a Fax XOR the salesman was Jones (excluding Faxes sold by
Jones), we can use the following formula:
=SUM(IF(MOD((A2:A10="Fax")+(B2:B10="Jones"),2),1,0))
A "negative and" or NAND operation is a comparison that returns TRUE when neither or exactly one of
the elements is TRUE, but returns FALSE if both elements are TRUE. For example, we can count the
number of sales except those in which Jones sold a Fax with the formula:
=SUM(IF((A2:A10="Fax")+(B2:B10="Jones")<>2,1,0))
Creating Sequences And Loops For Array Formulas
Nasir Mustafa MBA(HRM)
When you are constructing some types of array formulas, you need to create a sequence of numbers for a
function to process as an array. As an example, consider an array formula that will compute the average of
the Nth largest elements in a range. To do this, we will use the LARGE function to get the largest numbers,
and then pass those numbers as an array to AVERAGE to compute the average. Normally, the LARGE
function takes as parameters a range to process and a number indicating which largest value to return (1 =
largest, 2 = second largest, etc.,). But LARGE does work with arrays for its second parameter. You might
be tempted to type in the array in the formula yourself: =LARGE(A1:A10,{1,2,3}). While this will
indeed work, it is tedious.
Instead, you can use the ROW function to return a sequence of numbers. When used in an array formula,
the function ROW(m:n) will return an array of integers from m to n. Therefore, we can use ROW to create
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
the array to pass to LARGE. This changes our array formula to =LARGE(A1:A10,ROW(1:3)). This
brings us closer to a good formula, but two things remain.
First, if you insert a row between rows 1 through 3, Excel will change the row reference 1:3, and therefore
the formula will average the wrong numbers. Second, the formula is locked into the three largest values.
We can make it more flexible by making the number of elements to average a cell reference that can be
easily changed. For example, we can specify that cell C1 contains the size of the array to pass to LARGE.
This is accomplished with the INDIRECT function. (Click here for more information about INDIRECT.) The
INDIRECT function converts a string representing a cell reference into an actual cell reference. The sub-
formula ROW(INDIRECT("1:"&C1)) will return an array of numbers between 1 and the value in cell C1.
Now, coming together the formula to average the N largest values in A1:A10 becomes:
=AVERAGE(LARGE(A1:A10,ROW(INDIRECT("1:"&C1))))
Formulas That Return Arrays
The other type of array formula is one that returns an array of numbers as its result. These sort of array
formulas are entered into multiple cells that are then treated as a group. For example, consider the formula
=ROW(A1:A10). If this is entered into one cell, either as a normal formula or as an array formula, the
result will be 1 in that single cell. If, however, you array enter it into a range of cells each cell will contain
one element of the array. To do this, you first must select the range of cells in to which the array should be
written, say C1:C10, type the formula =ROW(A1:A10), and then press CTRL SHIFT ENTER. The
elements of the array {1, 2, ...., 10} will be written to the range of cells, with one element of the
array in each cell. When you array enter a formula into an array of cells, Excel prevents you from modifying
a single cell with that array range. You may select the entire range, edit the formula, and array-enter it
again with CTRL SHIFT ENTER, but you cannot change a single element of the array.
Some of the built-in Excel functions return an array of values. These formulas must be entered into an
array of cells. For example, the MINVERSE function returns the inverse of a matrix with an equal number of
rows and columns. Since the inverse of a matrix is itself a matrix, the MINVERSE function must be entered
into a range of cells with the same number of rows and columns as the matrix to be inverted. Therefore, if
your matrix is in cells A1:B2 (two rows and two columns), you must select a range the same size, type the
formula =MINVERSE(A1:B2) and press CTRL SHIFT ENTER rather than just ENTER. This enters the
formula as an array formula into all the selected cells. If you were to use the MINVERSE function in a single
Nasir Mustafa MBA(HRM)
cell, only the upper left corner value of the inverted matrix would be returned.
Other Useful Array Functions
Array formulas can do a wide variety of tasks. A few miscellaneous array formulas are shown below:
Sum Ignoring Errors
Normally, if there is an error in a cell, the SUM function will return that error. The following formula will
ignore the error values.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
=SUM(IF(ISERROR(A1:A10),0,A1:A10))
Average Ignoring Errors
This formula will ignore errors when averaging range.
=AVERAGE(IF(ISERROR(A1:A10),FALSE,IF(A1:A10="",FALSE,A1:A10)))
Average Ignoring Zeros
This formula will ignore zero values in an AVERAGE function.
=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))
Sum Of Absolute Values
You can sum a range of number treating them all as positive using the ABS function.
=SUM(ABS(A1:A10))
Sum Of Integer Portion Only
This formula will sum only the integer portion of the numbers in A1:A10. The fractional portion is
discarded.
=SUM(TRUNC(A1:A5))
Longest Text In Cells
This formula will return the contents of the cell with the longest amount of text in it.
=OFFSET(A1,MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),0)-1,0,1,1)
Array Formulas Versus The Data Functions
There is considerable overlap between what you can accomplish with array formulas and what you can do
with the so called D-Functions (DSUM, DCOUNT, and so on). Broadly speaking, the D-Functions are faster
than their array formula counterparts. If you have a large and complex workbook with many array formulas,
you may see a significant improvement in calculation time if you convert your array formulas to D-
Functions. The primary differences between the D-Functions and array formulas are as follows:
D-Functions are typically faster than array formulas, all else being equal
The selection criteria in a D-Function must reside in cells. Array formulas can include the selection
criteria directly in the formula
D-Functions can return only a single value to a single cell, while array formulas can return arrays to
Nasir Mustafa MBA(HRM)
many cells
Summary
Array formulas are a very powerful tool in Excel, allowing you to do things that are not possible with regular
formulas. Although they may seem complicated at first, you'll find that with a little practice they are quite
logical.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Introduction
There may be circumstances in which you need to extract a vector from a matrix or to convert a matrix to a
vector. For our purposes here, we will define a Matrix to be a two dimensional array with at least two rows
and at least two columns. We will define a Vector to be a one dimensional array that is either 1 row or 1
column of the Matrix. As an example, we will use the following matrix in the range C10:F12:
This matrix is made up of three row vectors each with four elements and four column vectors each with
three elements. The entire matrix has the defined name Matrix.
Returning The Matrix To A Column Vector
The first formula we will look at converts the entire matrix to a single column. The order of elements in the
resulting column is by row. The values are taken from each row, moving across then down. Enter the
following formula into cell C13 and then fill down for 12 rows, or as many cells as are in Matrix.
=OFFSET(Matrix,TRUNC((ROW()-ROW($C$13))/COLUMNS(Matrix)),MOD(ROW()-
ROW($C$13),COLUMNS(Matrix)),1,1)
This will transform the array Matrix to a single column of values. Change the reference to cell $C$13 to
the first cell in which you entered the formula.
The next formula is similar to the formula above, except that the elements of Matrix are returned column
by column, moving down one column and then across to the right to the next column. Enter the following
formula in cell D13 and fill down for 12 rows, or as many cells as are in Matrix.
=OFFSET(Matrix,MOD(ROW()-ROW($D$13),ROWS(Matrix)),TRUNC((ROW()-
ROW($D$13))/ROWS(Matrix)),1,1)
Nasir Mustafa MBA(HRM)
An abbreviated sample of the column vectors returned from Matrix is shown below:
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Returning The Matrix To A Row Vector
You can also use a formula to convert the Matrix to a Row vector. The following formula will transform
Matrix into a single row, moving by rows -- left to right on one row and then moving down to the next row.
Enter this formula into cell G16 and fill across to the right 12 columns, or as many columns as there are
cells in Matrix.
=OFFSET(Matrix,TRUNC((COLUMN()-COLUMN($G$16))/COLUMNS(Matrix)),MOD((COLUMN()-
COLUMN($G$16)),COLUMNS(Matrix)),1,1)
Change the reference to $G$16 to the first cell in which you enter the formula.
A similar formula can be used to transform Matrix into a row, ordered by columns. The order of the
elements is down one column and then moving to the right to the next column. Enter the following formula
in cell G17 and fill to the right for as many columns as there are cells in Matrix.
=OFFSET(Matrix,MOD((COLUMN()-COLUMN($G$17)),ROWS(Matrix)),TRUNC((COLUMN()-
COLUMN($G$17))/(ROWS(Matrix))),1,1)
An abbreviated image of the row vectors from Matrix is shown below:
Extracting One Row The Matrix
You can use a formula to return one row from the Matrix. Name a cell WhatRow and array-enter the
following formula into as many columns as there are in Matrix. Note that since this is an array formula
that returns an array of values, you must select the cells in which the results are to be returned, type the
formula, and then press CTRL SHIFT ENTER rather than just ENTER. If you do this properly, Excel will
display the formula in the Formula Bar enclosed in curly braces { }. See the Array Formulas Page for
more information about array formulas.
=OFFSET(Matrix,WhatRow-1,0,1,COLUMNS(Matrix))
Nasir Mustafa MBA(HRM)
Change the value of the WhatRow cell to indicate which row to return from Matrix. This value should be
1-based: 1 is the first row, 2 is the second row, and so on.
Extracting One Column Of The Matrix
A similar formula can be used to return one column of Matrix. Name a cell WhatColumn, and then array-
enter the following formula into a range of cells with as many rows as there are in Matrix.
=OFFSET(Matrix,0,WhatColumn-1,ROWS(Matrix),1)
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
You can change the value of the cell WhatColumn to return a specified column of Matrix. This is a 1-
based value -- 1 is the first (left-most) column, 2 is the second column, and so on.
Dynamic Charting
You can use the vector formulas to create a dynamic chart that updates its values based on a choice of a
row in a matrix of raw data. For example, suppose in cells B6:B9 you have persons' names. Assign the
name People to this range. Then, in cells C6:H9, enter some numbers. Assign the name AllScores to
this range. Then name a cell Person and add a list-type validation to this cell using the range People as
the list source. Next, name the range B16:G16 PersonScores, and in that range array-enter the formula:
=OFFSET(AllScores,MATCH(Person,People,0)-1,0,COLUMNS(AllScores))
Finally, create a chart that pulls its data from the range AllScores. Now, as you change the person name
in the cell Person, the chart will automatically update the values to that person's score. A complete
example of dynamic charting is shown in the downloadable workbook.
Dates And Times In Excel
Introduction
Almost all projects and applications in Excel use dates and times in some way. A project
may need to determine the number of days between two dates, or determine the date
that is so many days in the future. Timesheet projects need to determine the elapsed
time between and start and end times, or determine how many hours are overtime.
Nasir Mustafa MBA(HRM)
Before you can take advantage of some of the more power date and time formulas, you
have to understand how Excel stores dates and times. Regardless of how you have
formatted a cell to display a date or time, Excel always internally stores dates And times
the same way.
How Excel Stores Dates And Times
Excel stores dates and times as a number representing the number of days since 1900-
Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a
serial date, or serial date-time.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Dates
The integer portion of the number, ddddd, represents the number of days since 1900-
Jan-0. For example, the date 19-Jan-2000 is stored as 36,544, since 36,544 days have
passed since 1900-Jan-0. The number 1 represents 1900-Jan-1. It should be noted
that the number 0 does not represent 1899-Dec-31. It does not. If you use the MONTH
function with the date 0, it will return January, not December. Moreover, the YEAR
function will return 1900, not 1899.
Actually, this number is one greater than the actual number of days. This is because
Excel behaves as if the date 1900-Feb-29 existed. It did not. The year 1900 was not a
leap year (the year 2000 is a leap year). In Excel, the day after 1900-Feb-28 is 1900-
Feb-29. In reality, the day after 1900-Feb-28 was 1900-Mar-1 . This is not a "bug".
Indeed, it is by design. Excel works this way because it was truly a bug in Lotus 123.
When Excel was introduced, 123 has nearly the entire market for spreadsheet software.
Microsoft decided to continue Lotus' bug, in order to fully compatible. Users who
switched from 123 to Excel would not have to make any changes to their data. As long
as all your dates later than 1900-Mar-1, this should be of no concern.
Times
The fractional portion of the number, tttttt, represents the fractional portion of a 24
hour day. For example, 6:00 AM is stored as 0.25, or 25% of a 24 hour day. Similarly,
6PM is stored at 0.75, or 75% percent of a 24 hour day.
As you can see, any date and time can be stored as the sum of the date and the time.
For example, 3PM on 29-Jan-2000 is stored internally as 36544.625. When you enter a
time without a value, such as entering 15:00 into a cell, the date portion is a zero. The
zero indicates that there is no date associated with the time. You should remember that
entering just a time does not automatically put in the current date.
The Two Date Systems Of Excel
In the section above, it was said that the date portion of a serial date-time represents the
number of days since 1900-Jan-0. This is true, but Excel can also treat the date portion
of a serial date as the number of days since 1904-Jan-0. This mode is, called the 1904-
Nasir Mustafa MBA(HRM)
mode or 1904-system, is used for compatibility with the Macintosh system. The date
mode applies to all dates within a workbook. You cannot mix and match modes within a
workbook. Unless absolutely required, you should never use the 1904-system. If you
change systems after you've entered some dates in a workbook, these dates will appear
to be off 4 years (because the serial dates haven't changed, only their display
representation has). Moreover, linking workbooks with different date systems will cause
problems.
Entering Two Digit Years In Excel
When you enter a date with only two digits in the year, you need to know how Excel
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
treats the year. Excel uses 29/30 as the "cutoff point" between 1900 and 2000. For
example, entering a year between 30 and 99 causes Excel to treat the year as 1930 -
1999. Entering a year between 00 and 29 causes Excel to treat the year as 2000 -
2029. Here are some examples:
1/1/00 = 2000-Jan-1
1/1/01 = 2001-Jan-1
1/1/29 = 2029-Jan-1
1/1/30 = 1930-Jan-1
1/1/31 = 1931-Jan-1
1/1/99 = 1999-Jan-1
The 29/30 cutoff is new to Excel97 and Excel2000. Excel95 uses 19/20 as the cutoff
year. If you have any doubts about how Excel will treat 2-digit years, use the full 4-digit
year. In Windows98 and Windows2000, you can specify the cutoff year, from the
Regional Setting control in the Windows Control Panel.
Note that the DATE worksheet function does not follow these rules. When you enter a
number less than 1900 for the year parameter of the DATE function, DATE will add 1900
to that number. For example, =DATE(10,1,5) returns the date 5-Jan-1910, because
DATE simply adds 10 to 1900 to compute the year. Similarly, the formula
=DATE(150,1,5) returns the date 2050-Jan-5, because DATE adds 150 to the year
1900. DATE does not follow the same "cut off" rules that cells do.
Introduction
Very often, Excel is used to manage lists of data, such as employee names or phone lists. In such
circumstances, duplicates may exist in the list and need to be identified. This page contains a number of
Nasir Mustafa MBA(HRM)
formulas that can be used to work with duplicate items in a list of data. All the formulas on this page are
array formulas.
DEFINITION: Array Formula
An array formula is a formula that works with arrays or series of data rather than
single data values. When you enter an array formula, type the formula in the cell
and then press CTRL SHIFT ENTER rather than just ENTER when you first
enter the formula and when you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces { }. Array formulas are discussed in
detail on the Array Formulas page.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Testing A List For Duplicate Items
The formula below will display the words "Duplicates" or "No Duplicates" indicating whether there are
duplicates elements in the list A2:A11.
=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplicates","No Duplicates")
An alternative formula, one that will work with blank cells in the range, is shown below. Note that the entire
formula should be entered in Excel on one line.
=IF(MAX(COUNTIF(INDIRECT("A2:A"&(MAX((A2:A11<>"")*ROW(A2:A11)))),
INDIRECT("A2:A"&(MAX((A2:A11<>"")*ROW(A2:A11))))))>1,"Duplicates","No
Duplicates")
Highlighting Duplicate Entries
You can use Excel's Conditional Formatting tool to highlight duplicate entries in a list. All of the examples
in this section assume that the data to be tested and highlighted is in the range B2:B11. You should
change the cell references to the appropriate values on your worksheet.
This first example will highlight duplicate rows in the range B2:B11. Select the cells that you wish to test
and format, B2:B11 in this example. Then, open the Conditional Formatting dialog from the Format menu,
change Cell Value Is to Formula Is, enter the formula below, and choose a font or background format to
Nasir Mustafa MBA(HRM)
apply to cells that are duplicates.
=COUNTIF($B$2:$B$11,B2)>1
The formula above, when used in Conditional Formatting, will highlight all duplicates. That is, if the value
'abc' occurs twice in the list, both instances of 'abc' will be highlighted. This is shown in the image to the
left, in which all occurrences of 'a' and 'g' are higlighted.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
You can use the following formula in Conditional Formatting to highlight only the first occurrence of an
entry in the list. For example, the first occurrence of 'abc' will be highlighted, but the second and
subsequent occurrences of 'abc' will not be highlighted.
=IF(COUNTIF($B$2:$B$11,B2)=1,FALSE,COUNTIF($B$2:B2,B2)=1)
This is shown at the left where only the first occurrences of the duplicate items 'a', 'e', and 'g' are
highlighted. The second and subsequent occurrences of these values are not highlighted.
You can also do the reverse of this with Conditional Formatting. Using the formula below in Conditional
Formatting will highlight only the second and subsequent occurrences of a value. The first occurrence of
the value will not be highlighted.
Nasir Mustafa MBA(HRM)
=IF(COUNTIF($B$2:$B$11,B2)=1,FALSE,NOT(COUNTIF($B$2:B2,B2)=1))
This is shown at the left where only the second occurrences of 'a', 'b', 'c' and 'f' are highlighted. The first
occurrences of these items are not highlighted.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Another formula for Conditional Formatting will highlight only the last occurrence of a duplicate element in
a list (or the element itself if it occurs only once).
=IF(COUNTIF($B$2:$B$11,B2)=1,TRUE,COUNTIF($B$2:B2,B2)=COUNTIF($B$2:$B$11,B2))
As you can see only the last occurrences of elements 'a', 'b', 'c', and 'f' are highlighted. Element 'd' is
highlighted because it occurs only once. The occurrences of 'a', 'b', 'c' and 'f' that occurs before the last
occurrence are not highlighted.
We can round out our discussion of highlighting duplicate rows with two additional formula related to
distinct items in a list.
The following can be used in Conditional Formatting to highlight elements that occur only once in the
range B2:B11.
=COUNTIF($B$2:$B$11,B2)=1
This image illustrates the formula. Elements 'b', 'c', and 'e' are highlighted because they occur only once in
Nasir Mustafa MBA(HRM)
the list. Items 'a', 'd' and 'f' are not highlighted because they occur more than one time in the list.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Finally, the following formula can be used in Conditional Formatting to highlight the distinct values in
B2:B11. If an element occurs once, it is highlighted. If it occurs more then once, then only the first
occurrence is highlighted.
=COUNTIF($B$2:B2,B2)=1
As you can see, only the first or only occurrences of the elements are highlighted. If an element is
duplicated, as is 'b', the duplicate elements are not highlighted.
Functions For Duplicates
All of the formulas described above for Conditional Formatting can also be used in worksheet cells. They
are all array formulas, so you must select the range for the results, type in the formula, and press CTRL
SHIFT ENTER. The results of each formula will be a series of True or False values. The True results
correspond to those cells that are highlighted in Conditional Formatting and the False results correspond to
those cells that are not highlighted by Conditional Formatting.
Counting Distinct Entries In A Range
The following formulas will return the number of distinct items in the range B2:B11. Remember, all of these
are array formulas.
The following formula is the longest but most flexible. It will properly count a list that contains a mix of
numbers, text strings, and blank cells.
=SUM(IF(FREQUENCY(IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""),
IF(LEN(B2:B11)>0,MATCH(B2:B11,B2:B11,0),""))>0,1))
If your data does not have any blank entries, you can use the simpler formula below.
Nasir Mustafa MBA(HRM)
=SUM(1/COUNTIF(B2:B11,B2:B11))
If your data has only numeric values or blank cells (no string text entries), you can use the following
formula:
=SUM(N(FREQUENCY(B2:B11,B2:B11)>0))
Additional Information can be found on the following pages:
Preventing Duplicates In Data Entry
Deleting Rows
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Extracting Unique Entries In A List
Introduction
There are a variety of methods and formulas that can be used with lists of data. The examples that use two
lists assume you have two named ranges, List1 and List2, each of which is one column wide and any
number of rows tall. List1 and List2 must contain the same number of rows, although the need not be the
same rows. For example, List1 = A1:A10 and List2 = K101:K110 is legal because the number of rows is
the same even though they are different rows..
Listing Distinct Elements In A List
You can use a simple formula to extract the distinct elements in a list. Suppose your list begins in cell C11.
In some cell, enter
=IF(COUNTIF($C$11:C11,C11)=1,C11,"")
Nasir Mustafa MBA(HRM)
and then fill this formula down for as many rows as the number of rows in your data list. This formula will
list the distinct items in the list beginning in cell C11. In the image to the left, the original data is shown in
red and the results of the formula are shown in blue.
In the data shown in the image, the results are in a column adjacent to the original data. This is for
illustration only. The result data may be anywhere on the worksheet, or, for that matter, on another
worksheet or even in a separate workbook. The only restriction is that you must fill the formula down for at
least as many rows as there are in the data list.
See No Blanks for a formula to remove the blank cells in the result list to have all the distinct entries
appear at the top of the result list.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Extracting Elements Common To Two Lists
This formula assumes that the list that will contain the elements common to both lists is a range named
Common and that this range has the same number of rows as List1 and List2. This is an array formula
that must be array entered into a range of cells (see the Array Formulas page for more information about
array formulas). Select the range Common and type (or paste) the following formula into the first cell, then
press CTRL SHIFT ENTER rather than just ENTER. This is necessary since the formula returns an
array of values.
=IF(NOT(ISERROR(MATCH(List1,List2,0))),List1,"")
The result of this formula is an array of the values that exist in both List1 and List2. The positions of
elements in the resulting list will be the same as the positions in List1. For example, if List1 has 'abc' in
its 3rd row and List2 has 'abc' in the 8th row, 'abc' will appear in the 3rd row, not the 8th row, of the result
list. If an element in List1 does not exist in List2, the element in Common of the unmatched item in
List1 will be empty.
Nasir Mustafa MBA(HRM)
The image to the left illustrates several aspects discussed previously. First, we have three named ranges,
List1, List2, and Common. Second, all three ranges as the same size (10 rows in this case) but are all
different sets of rows. Finally, the position of the elements in the Common range match the positions of
elements in List1, not List2.
Extracting Elements From One List Not On Another List
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
You can also use a formula to extract elements that exist in one list but not in another. Again, it is assumed
that you have two named ranges, List1 and List2 of the same size. Create a new named range called
In1Not2 the same size as List1. The following formula is an array formula that must be entered into an
array of cells. Select the In1Not2 range, type in the formula and press CTRL SHIFT ENTER.
=IF(ISERROR(MATCH(List1,List2,0)),List1,"")
This formula will return the elements in List1 that do not
appear in List2. Select the cells in which you want the results to appear (this should be the same size
and List1) type the formula and then press CTRL SHIFT ENTER.
The order of the elements in the result list correspond to the position of that element in List1.
Conditional Formatting Lists
You can use Excel's Conditional Formatting tool to highlight cells in a second list that appear or do not
appear in a master list. Excel does not allow you to reference other sheets in a Conditional Formatting
formula, so you must use defined named. Name your master list Master and name your second list,
whose elements are to be conditionally formatted, Second. Open the Conditional Formatting dialog from
the Format menu. In that dialog, change Cell Value Is to Formula Is. To highlight elements in the Second
Nasir Mustafa MBA(HRM)
list that appear in the Master list, use the formula
=COUNTIF(Master,OFFSET(Second,ROW()-ROW(Second),0))>0
To highlight cells that appear in Second but not in Master, use Conditional Formatting as above but use
the following formula:
=COUNTIF(Master,OFFSET(Second,ROW()-ROW(Second),0))=0
Introduction To Defined Names
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
A Defined Name is a text descriptor that you can use to describe the meaning of or content of a cell, a
range of cells, a constant, or a formula. Once the Defined Name is established, you can use that Name
wherever you would ordinarily use the actual cell address or other content. This makes the formulas much
easier to understand and maintain. For example, the formula
=G12*K15
isn't very meaningful unless you happen to know the meaning of cells G12 and K15. However, if you
assign Defined Names to G12 and K15, you can write your formula similar to the following:
=Income*TaxRate
Clearly, using the Defined Names makes the formula much easier to understand and maintain.
Creating A Defined Name
There are two ways of creating a Defined Name (not including doing so with VBA code). The first method
is to select the cell or range of cells to which you want to assign a Name. Then, click in the Name Box and
type the Name. The Name Box is the white rectangle to the left of the "A" column heading and above the
"1" row heading. If you type in the Name Box a Name that is already defined, Excel will display the range
referenced by that Name. It will not overwrite and change the existing Defined Name. The second way to
create a Name is to select the cell or range of cells to which you want to assign a Name, go to the Insert
menu, choose Name, then Define. This will display the Defined Names dialog box. There, type in the
Name you want to use. In that dialog, you can also change the cell(s) to which an existing Name refers.
A Defined Name must begin with a letter or an underscore ( _ ) and consist of only letters, numbers, or
underscores. Spaces are not permitted in a Defined Name. Moreover, a Defined Name may not be the
same as a valid cell reference. For example, the name AB11 is invalid because AB11 is a valid cell
reference. Names are not case sensitive.
CAUTION: If you are working on a workbook that will be used in both Excel 2003 and Excel 2007, be
aware that many Names that are valid in Excel 2003 are invalid in Excel 2007. This is due to the
increased number of columns in Excel 2007. For example, in Excel 2003, the Name ABC1 is valid
because it is not a cell reference. However, in Excel 2007, ABC1 is, in fact, a valid cell reference, so the
Name is invalid.
Nasir Mustafa MBA(HRM)
Name Contents
A Defined Name is not limited to referencing a cell or range of cells. A Name can refer to a constant, either
a numerical value or a text value. It can also refer to a formula. For example, you could have a Defined
Name of MaxRows with a value of 25, and then use that name anywhere you would otherwise use that
number. Using a Name has the advantage that if you use it in multiple locations and later need to change
the value, you need change it only in the definition and the new value will be used in all other locations.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
When you use a Define Name in a formula or cell, you don't include parentheses as you would with a
function. For example, if you have a Name defined as MyName with a value of Chip Pearson, you would
enter in the cells =MyName, not =MyName(). Using the parentheses would cause a #REF error.
A Defined Name may also contain a formula. For example, you can define a Name named TheSum and
assign the formula =SUM($A$1:$A$100) to the Name. Then, you can use =TheSum anywhere you want
to get the sum of A1:A100. As noted before, you do not use parentheses in the reference to the name.
=TheSum() will cause a #VALUE error. You can use any formula you want in a Name, but you cannot
pass parameters to the formula defined by the Name.
Defined Name Formulas And Array Formulas
If you use a formula in a Defined Name, that formula is evaluated as if it were not an array formula. There
is no way to force a formula in a Defined Name to be evaluated as an array formula.
Global-Scope And Sheet-Scope Names
Typically, a Defined Name has global scope. This means that the Name may be defined on any one
worksheet and then referenced on any worksheet in the workbook. For example, if you have a cell, say A1
on Sheet1, that contains the last printed date, and you name that cell LastPrintDate, you can use the
Name LastPrintDate on any worksheet and it will always refer back to A1 on Sheet1.
It is possible, though, to have Names that are scoped to only a single worksheet, and any or all of the
worksheets may have a sheet scoped names with the same name. When that Name is used on a
worksheet, the value of the Name on the worksheet that is using it is evaluated. If you have several sheet
scoped names, on different worksheets, those names need not refer to the same cell reference. That is,
the name SheetLastPrinted on Sheet1 may refer to A1 on Sheet1, and the name
SheetLastPrinted on Sheet2 can refer to K10 on Sheet2. When the name SheetLastPrinted is
used on Sheet1, the value is taken from A1 on Sheet1. If the Name is used on Sheet2, the value is
taken from K10 on Sheet2.
Nasir Mustafa MBA(HRM)
To create sheet scoped Names, select the cell to be named, open the Defined Name Dialog (Insert menu,
Name, then Define), and enter the name as Sheet1!TheName (where Sheet1 is the name of the sheet
on which the Name should be scoped. The presences of the sheet name causes Excel to make the name
sheet scoped. Repeat this process on all worksheets that need to use the sheet scoped name. When
defining the Names, enclose the sheet name within apostrophes if the sheet name contains spaces or
other non-numeric characters. E.g., 'Sheet One'!TheName.
Defined Names And Conditional Formatting And Data Validation
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
One of the very useful features of Defined Names is to circumvent some restrictions of Conditional
Formatting and Data Validation. In Condition Formatting, if you use the Formula Is method, all cells in the
formula must be on the same worksheet as the cell to which Conditional Formatting is applied. For
example, if you are applying Conditional Formatting to cell A1 on Sheet1, you will not be able to use a
formula like =Sheet2!A10>100, because the formula refers to a cell on another sheet. You can get
around this limitaiton by assigning a Name to the cell on the other sheet -- e.g., assign the name TestVal
to Sheet2!A10 -- and then use the defined name in the Conditional Formatting dialog: =TestVal>10.
This same method can be used for the values list in a List type Data Validation constraint. If your list of
valid values is in the range Sheet2!A1:A10, you cannot reference that range as the source of the
validation list if the cell being validated is on a different worksheet. However, if you assign a Defined Name
to that range, e.g., ValidList, you can use =ValidList as the values list for Data Validation.
Widening The Defined Name Box
In Excel 2003 and earlier, the defined name box cannot be resized, and it truncates the display of names
at about 16 characters. So, if you have two long names like SomeLongDefinedNameOne and
SomeLongDefinedNameTwo, you cannot tell which is which in the Name Box dropdown. However, using
a bit of code, you can expand the width of the drop down list. This code will not widen the name box itself,
as it appears on the formula bar, but widens the drop down list. The code to do this is shown below.
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long
Sub WidenNameBoxDrop2()
Dim Res As Long
Nasir Mustafa MBA(HRM)
Const CB_SETDROPPEDWIDTH = 352
Const cWidth = 400 '< Change To Desire Width in Pixels
Res = SendMessage( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString), _
CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Put all of the code shown above in some code module, and then in your Auto_Open procedure or in the
Workbook_Open procedure call the WidenNameBoxDrop2 procedure. For example,
Sub Auto_Open()
WidenNameBoxDrop2
End Sub
In Excel 2007 and later, the Name Box is sizable, so the code given above in not necessary.
Applying Names To Existing Formulas
You can apply Names to existing formulas. If your formula uses regular cell references and you later
assign Names to those cells, you can automatically update the formula to use the Defined Names. For
example, suppose you have the formula =A5+D5. If you later assign the Name CellOne to A5 and
CellTwo to D5, you can select the cell with the formula, go to the Insert menu, choose Name, and then
Apply. Choose the appropriate names in the list, or simply select them all, and click OK. Excel will alter the
formula to =CellOne+CellTwo.
Working With Names In Visual Basic
You can use VBA code to work with defined names. To add a name, use
ThisWorkbook.Names.Add Name:="SomeName", _
RefersTo:=Worksheets("Sheet2").Range("A1:A10")
This will assign the Name SomeName to the range A1:A10 on Sheet2. If you add a Name that already
exists, the Name is automatically deleted and recreated with the new reference. It is not necessary to
delete the Name before recreating it.
By default, names created manually or with VBA code are visible -- they will appear in the Name Box drop
down and in the Names dialog. You can, however, make the name hidden so that it isn't visible to the user.
A hidden Name can be used in any manner just like a normal, visible Name. To make a name hidden, you
Nasir Mustafa MBA(HRM)
set the Visible parameter to False. For example,
ThisWorkbook.Names.Add Name:="SomeName2", _
RefersTo:=Worksheets("Sheet2").Range("B1:B10"), _
Visible:=False
The name SomeName2 can be used exactly as a visible name, but will not appear to the user. The only
way to hide a Defined Name is via VBA code. There is nothing in the Excel user interface that allows you
to hide a Name.
To delete a name using VBA code, use code like the following:
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
ThisWorkbook.Names("TheName").Delete
Using Defined Names in VBA code is different than using them in worksheet cells. You must get the value
of the Name using the RefersTo or RefersToRange properties. If a Name doesn't refer to a cell or
range of cells (e.g., it refers to a constant), an attempt to use RefersToRange will fail. To get the value of
a Name that contains a constant, use the RefersTo property.
V = ThisWorkbook.Names("TheName").RefersToRange
If the name refers to a range of 2 or more cells, you can assign it to a Range type variable. For example,
Dim R As Range
Set R = ThisWorkbook.Names("BigName").RefersToRange
Debug.Print R.Address
If the name refers to a constant, you'll need to strip out some characters to get the actual value. For
example, if the name MyName refers to the text constant Chip Pearson, the RefersTo property will
return the text ="Chip Pearson". You need to strip out the leading equals sign and the enclosing
quotes. You can do this with code like the following:
Dim S As String
S = ThisWorkbook.Names("MyName").RefersTo
S = Mid(S, 3, Len(S) - 3)
Debug.Print S
If the name refers to a numeric constant, there will be a leading equal sign but no quotes. Thus, you'll need
to strip off the leading equals sign. The code below illustrates this.
Dim S As String
S = ThisWorkbook.Names("MaxPages").RefersTo
S = Mid(S, 2)
Debug.Print S
We can put all this together into a function that will return what the given name refers to, be it a range, text
constant, or numeric constant.
Function GetNameRefersTo(TheName As String) As String
Dim S As String
Nasir Mustafa MBA(HRM)
Dim HasRef As Boolean
Dim R As Range
Dim NM As Name
Set NM = ThisWorkbook.Names(TheName)
On Error Resume Next
Set R = NM.RefersToRange
If Err.Number = 0 Then
HasRef = True
Else
HasRef = False
End If
If HasRef = True Then
S = R.Text
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Else
S = NM.RefersTo
If StrComp(Mid(S, 2, 1), Chr(34), vbBinaryCompare) = 0 Then
' text constant
S = Mid(S, 3, Len(S) - 3)
Else
' numeric contant
S = Mid(S, 2)
End If
End If
GetNameRefersTo = S
End Function
The CALL Function
This page was written by Laurent Longre, and is included on my site at his request and with his
kind permission. I have made some minor changes in formatting and spelling.
NOTE: The CALL function has been disabled in Excel 2000, because it represented a
very serious security risk. This page refers to Excel 97 only, not Excel 2000 or Excel
2002.
The CALL function
The following formulas are based on the CALL function. CALL, inherited from Excel 3/4, allows
you to use DLL functions directly in worksheets.
Syntax :
=CALL(dll_name,function_name,type_string,arg1,...,argN)
Where
dll_name is the name of the DLL that contains the function. This name must contain a full
path if the DLL file is not located in the Windows folder, the System folder or a folder named in
Nasir Mustafa MBA(HRM)
the PATH environment string.
function_name is name of the function.
type_string is a text string specifying the data type of the returned value and the data
types of all arguments to the DLL. The first letter of type_string specifies the return value.
arg1, ... argN are the arguments of the function. Their types must correspond to the type
string. Up to 27 arguments may be specified.
The type_string consists in a set of letters indicating the type of each argument. The first
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
letter corresponds to the returned value. Here are the letters used in the following examples:
Code Description Transfered by... C type
B 8-byte floating-point number (IEEE) Value double
C Zero (null) terminated string (max. length = 255 Reference char *
characters)
F Zero (null) terminated string (max. length = 255 Reference (modify in char *
characters) place)
J 4 bytes wide signed integer Value long int
P Excel's OPER data structure Reference OPER *
R Excel's XLOPER data structure Reference XLOPER *
CALL has also a second (complementary) syntax. For a more detailed description of this
syntax and of the type string, see the "CALL Worksheet Function" topic in the Excel Help file.
Example:
=CALL("C:\Temp\MyDll","MyFunc","BJJB",25,3,98.65)
This formula calls the function "MyFunc" contained in C:\Temp\Mydll.dll. It returns a
floating point number (B), and receives three arguments: two long integers ("JJ" = 25 and 3)
and one floating point number ("B" = 98.65).
Using XLM functions with CALL
Combining CALL and Excel4
The CALL function, combined with Excel's C API "Excel4" function, allows you to use a large
part of the "old" XLM (Excel 4) functions directly in worksheets.
The Excel4 function, contained in the file ...\Office\Xlcall32.dll, is a callback
function which exposes all XLM functions and macros to stand-alone add-in DLLs (XLLs).
C prototype :
Nasir Mustafa MBA(HRM)
int cdecl Excel4(int xlfn, LPXLOPER operRes, int count,...);
Where
xlfn is a number indicating which Excel function or macro to call.
operRes is pointer to an XLOPER structure which receives the result of the function / macro
count is the number of arguments
The arguments following count must be pointers to XLOPER structures ("LPXLOPER").
Excel4 returns a number which indicates wether the call has successed or not. We won't use
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
this value in the following formulas.
Excel4 can be used directly in worksheets, according to this syntax:
=CALL("Xlcall32","Excel4",type_string,xlfn,,count,Arg1,...,ArgN)
The type_string argument must begin with "2JRJ" and (in most cases) end with a "#".
Example :
The formula
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,18,A1)
returns the name of the font, as text, of the cell A1.
Some explanations about this formula :
"2JR" means that the returned value is the second argument of Excel4 (the "operRes"
described above). When the type_string begins with some number "N", CALL modifies the
Nth argument "in place". It allocates memory for the returned value, and frees this memory
automatically. That's why we omit the 5th argument of CALL (which is the second argument of
Excel4); CALL reserves memory for the result in this argument, and Excel4 fills it with the
result of the called function.
"JRR" The returned value and each argument must be declared in the type_string as
pointers to XLOPER ("R") or OPER("P") structures. An XLOPER can contain any valid Excel type
(range reference, number, string, error code, boolean or "missing argument").
"#" at the end of the string indicates that Excel4 is allowed to call all "class 2" functions, or
any worksheet function and any XLM function that returns a value but performs no action
(thus, not a macro-function). The use of this letter should be normally reserved to the
REGISTER function, but it works also with CALL, though this feature is not documented.
A second example:
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,18,A1)
Nasir Mustafa MBA(HRM)
"2JRJRR#" indicates that the function returns the result of the function number 185 (first "J"),
this result is stored in the second ("2") argument (first "R", the missing argument following the
185), which is modified in place. The called function has 2 arguments (second "J"),
respectively 18 and A1, both declared as LPXLOPERs (ending "RR"). It's an XLM function
("#").
The function number 185 corresponds to the XLM function GET.CELL. In an XLM macro-
sheet, the above formula would be simply :
=GET.CELL(18,A1)
Where the 18 is the function code to return the name of the font in cell A1.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
The main XLM information functions which can be used in this way are the following:
Function name Number Type XLM Syntax
string
GET.CELL 185 "2JRJRR#" GET.CELL(type_num, reference)
GET.DOCUMENT 188 "2JRJRR#" GET.DOCUMENT(type_num,
name_text)
GET.WINDOW 187 "2JRJRR#" GET.WINDOW(type_num,
window_text)
GET.WORKBOOK 268 "2JRJRR#" GET.WORKBOOK(type_num,
name_text)
GET.WORKSPACE 186 "2JRJR#" GET.WORKSPACE(type_num)
Examples :
Using XLM functions in worksheets avoids having to write VBA (or even XLM) user defined
functions for getting any information about a cell, a sheet or Excel's environment. XLM
functions invoked directly with CALL and Excel4 are faster than custom VBA functions.
Here are some examples:
Warning : don't build the following formulas in the Function Wizard; instead, type them directly
in the cells or in the formula bar.
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,18,A1)
returns the name of the font used in cell A1 (calls the GET.CELL function)
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,A1)
returns the color index of the first character in A1 (GET.CELL)
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,41,A1)
returns the formula in the cell A1 (GET.CELL)
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,53,A1)
returns the contents of A1 as it is currently displayed, as text, including any additional numbers
or symbols resulting from the cell's formatting (GET.CELL)
Nasir Mustafa MBA(HRM)
=CALL("Xlcall32","Excel4","2JRJRR#",268,,2,4,
CALL("Xlcall32","Excel4","2JRJRR#",185,,2,66,INDIRECT("A1")))
returns the number of sheets in the workbook (GET.WORKBOOK and GET.CELL)
=CALL("Xlcall32","Excel4","2JRJR#",188,,1,50)
returns the total number of pages that would be printed based on current settings
(GET.DOCUMENT)
The first two formulas above are not immediately recalculated (because changing a font or a
color in a cell doesn't cause any calculation at all). Formulas 3 and 4 are recalculated when the
value in A1 changes. The 5th formula recalculates only when you delete a sheet in the
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
workbook (inserting a sheet doesn't cause a recalculation).
"Static" Rand() :
This formula returns a "static" (not volatile) random value in the range [0,1). It calls the
standard worksheet function RAND(), but as an XLM function. If you want to update the
returned random value(s), just press Ctrl-Alt-F9.
=CALL("Xlcall32","Excel4","2JRJ",63)
The following returns a random integer in the range [1,100] (still static):
=INT(CALL("Xlcall32","Excel4","2JRJ",63)*100)+1
Circular references... without circular references :
DLL functions called with a number sign (#) in the type string can deal with the contents of the
cell in which they are entered, whereas normal worksheet formulas and VBA functions can't do
it without using Circular References:
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,5,INDIRECT("RC",0))
(equivalent to the XLM formula =GET.CELL(5,INDIRECT("A1"))
The "RC" refers, in R1C1 reference style, to the cell which the formula is entered.
This feature allows to use formulae like the following, which "stores" the highest value entered
in the cell A1. You don't need to enable circular references in the Options menu.
=MAX(A1,CALL("Xlcall32","Excel4","2JRJRR#",185,,2,5,INDIRECT("RC",0)))
Using GET.CELL in array formulae :
The GET.CELL XLM function, which retrieves several informations about cells (font, size, etc.),
can be used in array formulas. For instance, you can use it to count the number of cells in a
range whith text colored in red.
The syntax of these array formulae is relatively "tortured", because GET.CELL is supposed to
Nasir Mustafa MBA(HRM)
work only with single cells, not ranges [... I just say is supposed to be use].
The following formulae are based on GET.CELL(24,CellRef), which returns the color index
of the first character in CellRef (index of red = 3). In our particular syntax, this function
corresponds to:
=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,CellRef)
Number of cells with text colored in Red, in an 1-column range ("Rg"):
{=SUM(N(CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,
TRANSPOSE(OFFSET(Rg,ROW(INDIRECT("1:"&ROWS(Rg)))-1,,1)))=3))}
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Number of cells with text colored in Red, in an 1-row range ("Rg"):
{=SUM(N(CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,
TRANSPOSE(OFFSET(Rg,,COLUMN(Rg)-COLUMN(INDEX(Rg,,1)),,1)))=3))}
Number of cells with text colored in Red, in any range ("Rg")
{=SUM(N(CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,
TRANSPOSE(OFFSET(Rg,ROW(INDIRECT("1:"&ROWS(Rg)))-1,
COLUMN(Rg)-COLUMN(INDEX(Rg,,1)),1,1)))=3))}
A shorter, though slightly slower (but still much faster than a custom VBA function), version of
this formula follows:
{=SUM(N(CALL("Xlcall32", "Excel4", "2JRJRR#",
185,,2,24,TRANSPOSE(INDIRECT(ADDRESS(ROW(Rg),COLUMN(Rg),4))))=3))}
When you change the color of a cell, the result of the formula is not updated automatically. You
must wait for the next calculation in the sheet, or simply press F9.
The first argument of GET.CELL ("type_num") specifies the expected information. In the above
formulas, '24' corresponds to the information "font color of the first character in the cell". If you
replace 24 with 18 and 3 with "Arial", these formulae will count the number of cells whose font
is set to Arial.
� Copyright, 1998, Laurent Longre.
Introduction
Almost every worksheet contains at least one table of data, typically a set of rows and columns. Very
frequently, you will need to return a row or column of values from the table the row or column position in
the table, or you may need to return a value from the table based upon a match of values in the row
headers and column headers. For example, you may need to return the 5th row of a table, or you may
need to return the row where the ID number is 1234.
Nasir Mustafa MBA(HRM)
The simplest types of lookups are performed with the VLOOKUP or HLOOKUP functions. The functions are
well documented in the Help file and are not discussed in detail on this page. It is assumed that you are
familiar with VLOOKUP and HLOOKUP. For more complicated lookups in tables, we will use formulas based
on the OFFSET, MATCH, and INDEX functions. While the Help file describes these functions individually, it
does not describe how these functions can be combined to create more powerful and flexible lookup
formulas. That is the goal of this page. At the core of most of the formulas on this page is the OFFSET
function. You should be familiar with this function before proceeding with this page.
Most of the formulas on this page are array formulas. Array formulas are described in detail on the Array
Formulas page on this web site. You should be at ease with array formulas in order to modify the lookup
formulas presented on this page. With few exceptions, the formulas on this page use only a single range
reference, a Defined Name that refers to the data table against which the lookup is performed. Using a
single reference may make the formulas longer, but it also makes them considerably more flexible. To use
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
the formulas on your own worksheets, you need only modify a single name. This convenience makes up
for, in my opinion, the longer formula length. Of course, if you are not using a defined name, simply replace
the name in the formula with the appropriate range reference.
If the formulas on this page do not return the expected result when you use them on your own worksheets,
the first thing to check is to ensure that the formula is entered as an array formula. If you are unsure
whether a formula needs to be array entered, go ahead and enter it as an array formula; that is completely
safe.
ENTERING AN ARRAY FORMULA: When you enter a formula as an array formula,
you must press CTRL SHIFT ENTER rather than just ENTER when you first enter
the formula and whenever you edit it later. If you do this properly, Excel will display
the formula in the formula bar enclosed in curly braces, { }. You do not type in the
curly braces, { }; Excel will display them automatically.
In the interest of brevity and clarity, the formulas on this page do not have any error checking and
handling. For example, there is nothing to prevent you from attempting to return the 6th row of a table that
has only 4 rows. If a parameter in a function call is invalid, you will most likely get a #N/A error. You may
want to add some error checks when you use these formulas in your own worksheets.
As is the case with many types of formulas in Excel, there are several different ways to accomplish the
same thing. Many of the formulas on this page could be written with a combination of the INDEX and
MATCH functions instead of the OFFSET function. OFFSET is neither better nor worse than INDEX/MATCH.
For consistency, I have chosen to use OFFSET for nearly all the tasks at hand. Other sources may use
other methods. I encourage you to learn a variety of ways to accompish a task.
Example Data
The example formulas in the first section of this page, those formulas for returning rows and columns of a
table, use the following data table.
Nasir Mustafa MBA(HRM)
This table contains two named ranges that are used in the formulas. The name Table refers to the entire
table, cells B2:G7, which includes the row labels and column labels. The name InnerTable refers only
the the actual data, cells C3:G7, which does not include the row labels and the column labels. For
illustration, the values of the row labels (abby, beth, etc.) and the column labels (apples, oranges, etc) are
in alphabetical order. This is for illustration only. The formulas do not require that the values be in any
particular order.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Returning A Row Or Column From A Table
You can use an array formula to return a single row or column from a table. The formulas in this section
need to be array entered (press CTRL SHIFT ENTER rather than just ENTER) into a number of cells equal
to the size of the row or column of the table. The example table contains 6 columns (including the row
header); thus, you would select a range that is 6 columns wide and 1 row tall, enter the formula and press
CTRL SHIFT ENTER.
The first formulas return a single row, based on position, from Table or InnerTable.
=OFFSET(Table,E13-1,0,1,COLUMNS(Table))
In this formula, cell E13 contains the row to return. The row is 1-based (the title row is 1, the first row of
data is 2, etc). The OFFSET function uses 0-based rows and columns, so we subtract 1 from the row
number before passing it into the OFFSET function. If cell E13 contains the number 5, the formula returns
the following values:
The following formula returns a row from the InnerTable range. It return only the data values, not the
row header.
=OFFSET(InnerTable,E18-1,0,1,COLUMNS(InnerTable))
In this formula, cell E18 contains the 1-based row of InnerTable to return. Thus, if cell E18 contains 5,
the formula returns the following values.
By changing the values that are passed to the OFFSET function, we can return a column from either the
Table or InnerTable range, either by using a column offset or the value of a column label. The following
formula will return a column from the Table range.
Nasir Mustafa MBA(HRM)
=OFFSET(Table,0,E22-1,ROWS(Table),1)
If cell E22 contains the value 3, the third column of Table is returned, as shown below:
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Since this formula returns a column of data from Table, it should be array entered into to a range that is
one column wide and has the same number of rows and the Table range.
You can also return a column from Table that corresponds to a matching column label. The following
formula will return the column from Table whose column label is equal to the value in cell E39.
=OFFSET(Table,0,MATCH(E39,OFFSET(Table,0,0,1,COLUMNS(Table)),0)-
1,ROWS(Table),1)
If cell E39 contains the value plums, the following values are returned.
Calculations On Rows Or Columns Of A Table
Because the formulas described above return arrays of values, either a row or column of the
InnerTable, you can use those formulas with functions that accept arrays. Indeed, you can use the row
and column functions in any function or formula where you would normally provide a range of cells, such
as in the SUM, MIN, MAX, or AVERAGE functions, among others. For example, the following formula will
return the SUM of the row whose row label is equal to the value in cell E48.
=SUM(OFFSET(InnerTable,MATCH(E48,OFFSET(Table,0,0,ROWS(Table),1),0)-
2,0,1,COLUMNS(InnerTable)))
If cell E48 contains the value callie, this formula will return the value 560. You can get the maximum or
minimum of the row by changing SUM to MAX or MIN. These formula do not need to be entered as array
formulas, although it is harmless to do so.
A very similar formula can be used to return the sum, minimum, or maximum of a column in the table. The
Nasir Mustafa MBA(HRM)
following formula will return the sum of the values in the column of Table where the column label is equal
to the value in cell E52.
=SUM(OFFSET(InnerTable,0,MATCH(E52,OFFSET(Table,0,0,1,COLUMNS(Table)),0)-
2,ROWS(InnerTable),1))
If cell E52 contains oranges, the formula will return 535. As before, you can change SUM to MIN or MAX to
return the minimum or maximum of the column. Again, these formulas need not be array entered.
Last Value In A Row Or Column
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
You can use a formula to return the last cell in a row or column, where the row or column is select either by
its position in the table or by a match of a value with the row or column label.
The following formula will return the last (right-most) value in a row of Table, where cell E56 contains the
1-based row position:
=OFFSET(Table,E56-1,COLUMNS(Table)-1,1,1)
If E56 contains 4, the result is 122, the last value in the 4th column of Table (including the column labels).
You also select the row to use by matching a row label. If cell E59 contains the value callie, the following
formula will return 122, the right-most value in the row whose row label is callie.
=OFFSET(Table,MATCH(E59,OFFSET(Table,0,0,ROWS(Table),1),0)-1,COLUMNS(Table)-
1,1,1)
The following formulas will return the last (bottom-most) value of a column, selected by either its position in
Table (cell E62) or by a match of a column label (in cell E65).
=OFFSET(Table,E62-1,COLUMNS(Table)-1,1,1)
=OFFSET(Table,ROWS(Table)-1,MATCH(E65,OFFSET(Table,0,0,1,COLUMNS(Table)),0)-1)
Double Lookups
A double lookup is a formula that returns a value from a table based on a match of values in both the rows
and columns. Refering to the example data shown above, you may want to return the value corresponding
to the dora row and the plums column. If cell E74 contains the value to match on the rows (e.g., dora) and
cell E75 contains the value to match on the columns (e.g., plums), the following formula will return the
appropriate value from the Table range:
=OFFSET(Table,MATCH(E74,OFFSET(Table,0,0,ROWS(Table),1),0)-1,
MATCH(E75,OFFSET(Table,0,0,1,COLUMNS(Table)),0)-1)
Nasir Mustafa MBA(HRM)
Left Lookups
While the VLOOKUP function is very useful, it has a significant limitation. That is that you can only return a
value to the right of the lookup column. For example, you can look in column B for a value and then return
the corresponding value from column D. However, the reverse is not true. You cannot look up a value in
column D and return the corresponding value from column B. This is where a Left Lookup formula is
useful. For example, suppose you have the following table, and a defined name of LLTable that refers to
the actual data (colored in red).
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
The following formula will look for a value in the Value column and return the corresponding value in the
Type column.
=OFFSET(LLTable,MATCH(F67,OFFSET(LLTable,0,1,ROWS(LLTable),1),0)-1,0,1,1)
In this formula, cell F67 contains the value to be searched for in the Value column. Thus, if F67 contains
44, the formula will return dd.
Upper Lookups
The HLOOKUP function is the "transpose" of the VLOOKUP function. As VLOOKUP scans down a column for
a match and then moves to the right to return a value, HLOOKUP scans across a row for a match and then
moves down to return a value. HLOOKUP cannot move upwards to return a value. For example, you can
search row 5 to find a match and then return the corresponding value from row 8, but the reverse is not
possible. You cannot scan row 8 and return a value from row 5. Just as the Left Lookup formula overcame
the limitation of VLOOKUP, an Upper Lookup formula can overcome the limitation of HLOOKUP. Consider
the following table:
In this table, the range displayed in red has the name ULTable. The followng formula will allow you to look
in the Value row for a value equal to cell J82 and return the corresponding value from the Type row.
=OFFSET(ULTable,0, MATCH(J82, OFFSET(ULTable,ROWS(ULTable)-
1,0,1,COLUMNS(ULTable)),0)-1,1,1)
Nasir Mustafa MBA(HRM)
For example, if J82 contains 33, the formula will return cc.
Arbitrary Lookups
Another limitation of the VLOOKUP function is that if there are duplicate matches in the lookup column, the
first occurrence of the matching value is used. For example, consider the following table of data:
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
With a simple VLOOKUP function for the value Beth, the value 22 will be returned, since 22 corresponds to
the first occurrence of the value Beth. It may be necessary, however, to return the value corresponding to
the second or third occurrence of Beth. If the table of values (colored in red, excluding the Name and
Score column labels) is named ALTable, the following formula will return the value form the Score column
corresponding the the Nth occurrence of the value in cell F90, where the number N is in cell F91. For
example, if F90 contains the value Beth and cell F91 contains the value 3 (indicating to find the 3rd
occurrence of Beth), the formula will return the value 88.
=INDEX(ALTable,SMALL(IF(OFFSET(ALTable,0,0,ROWS(ALTable),1)=F90,
ROW(OFFSET(ALTable,0,0,ROWS(ALTable),1))-ROW(OFFSET(ALTable,0,0,1,1))+1,
ROW(OFFSET(ALTable,ROWS(ALTable)-1,0,1,1))+1),F91),2)
A special case of the arbitrary lookup formula above is to return the value corresponding to the last
occurrence in the list. For example, if cell F94 contains the value Beth, the following formula will return the
value 88, which corresponds to the last occurrence of the value Beth.
=INDEX(ALTable,SMALL(IF(OFFSET(ALTable,0,0,ROWS(ALTable),1)=F94,
ROW(OFFSET(ALTable,0,0,ROWS(ALTable),1))-ROW( OFFSET(ALTable,0,0,1,1) )+1,
ROW(OFFSET(ALTable,ROWS(ALTable)-
1,0,1,1))+1),COUNTIF(OFFSET(ALTable,0,0,ROWS(ALTable),1),F94)),2)
Closest Match Lookups
The MATCH function is an important tool when working with lists of data. If you are searching for an exact
Nasir Mustafa MBA(HRM)
match in a range of cells, the values may be in any order. However, if you are attempting to find a closest
match, the values must be in sorted order. Using the INDEX and MATCH functions, you can write a formula
that will return the number in a list that is closest to a specified value. We will look at three related Closest
Match formula. These three formulas are based on the example data shown below. All three formulas are
array formulas and must be properly entered. This list of values has the defined name of CMTable.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
The following array formula will return the smallest number in the list CMTable that greater than or equal to
the value in cell E105.
=INDEX(CMTable,MATCH(MIN(IF(CMTable-E105>=0,CMTable,FALSE)),IF(CMTable-
E105>=0,CMTable,FALSE),0))
Thus is E105 has the value 5, the formula will return 5.1, which is the smallest number in the list that is
greater than or equal to 5.
The second Closest Match formula will return the largest number in a list that is less than or equal to a
specified number. In the following formula, cell E108 contains the test value.
=INDEX(CMTable,MATCH(MAX(IF(CMTable-E108<=0,CMTable,FALSE)),IF(CMTable-
E108<=0,CMTable,FALSE),0))
Thus, if cell E108 has the value 8, the formula will return 7.4, which is the largest number in the range that
is less than or equal to 8.
The third and final Closest Match formula will return the value in a list that is closest to a specified value.
The returned value might be less than the test value or it might be greater than the test value.
=INDEX(CMTable,MATCH(MIN(ABS(CMTable-E111)),ABS(CMTable-E111),0),1)
Thus, if cell E111 contains the value 5, the formula will return 5.1, since 5.1 is closer to 5 than any other
value in the list.
Nasir Mustafa MBA(HRM)
Introduction
Excel is often used to keep track of data that needs to be ranked. This might be anything from sports
scores to sales data. Excel provides a worksheet function named RANK that you can use to do rudimentary
ranking, but this function is rather limited. However, we can use some worksheet formulas to provide a
much deeper and meaningful anlaysis of data using the RANK function.
Simple Ranking
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
The simplest use of ranking is to get the rank of one or more values in a list. The rank of a number in a list
is the position at which the value would be placed if the data list were sorted. The RANK function does
exactly this and it supports ranking unsorted data, assigning ranks in either ascending order or descending
order. Descending order is the default for the RANK function. In descending order, the higher score is given
the numerically lower rank. For example, if you have bowling scores of {150, 125, 180, 175}, the
value 180, the highest value in the list, is given a rank of 1 and the value 125, the lowest value in the list,
is given the rank of 4. Even though in common language usage, the highest score is said to be the
"highest ranked" score, the actual numeric value of the rank is the lowest. (E.g., "the highest ranked
bowling score was 180", may sound correct, but the rank of 180 is actually the lowest rank value, 1). When
you rank in ascending order, the lower values are given the numerically lower rank. Golf scoring is an
example in which you would use ascending ranking. The lower the score the better, so lower values are
assigned lower numerical rankings. With the scores {72, 88, 75, 82}, the value 72, the lower score in
the list, is given a rank of 1 and 88, the highest score in the list, is given a rank of 4.
The RANK function is essentailly the inverse of the LARGE (and SMALL) function. While RANK returns the
rank of a value in a list, LARGE returns a value based on its rank. If cells B4:B8 contain the values
{5,2,4,3,1}, the formula =LARGE($B$4:$B$8,RANK(B4,$B$4:$B$8,0)) in cells C4:C8 returns the
original data in the original order, confirming the relationship between RANK and LARGE. A similar formula
can be created with the SMALL function: =SMALL($B$4:$B$8,RANK(B4,$B$4:$B$8,1)).
Unique Ranks In Descending Order
One of the features (or failures, depending on your perspective) of the RANK function is that it returns the
same rank value for items of equal value. For example, with the data {33, 22, 44, 22, 11} the RANK
function returns a rank of 1 to the value 44, the value 2 for 33, the value 3 for both occurrences of 22, and
then a rank of 5 for 11. No value is ranked as 4. You can use a formula to prevent this duplication of rank
value. For example, the following formula entered in cell B1 and filled down to B5 will return unique ranks
of the values in A1:A5
=RANK(A1,$A$1:$A$5,0)+COUNTIF($A$1:A1,A1)-1
To see how this formula works, enter the values 33, 22, 44, 22, 11 in cells A1:A5 and enter the
formula =RANK(A1,$A$1:$A$5,0)+COUNTIF($A$1:A1,A1) in cell B1. Fill the formula down from B1
through B5. This will return the values 2, 3, 1, 4, 5 to cells B1:B5. Examine the formulas in cells B2
Nasir Mustafa MBA(HRM)
and B4, the cells that are adjacent to the duplicated value 22. In B2, the RANK returns 3 (as it should), and
the COUNTIF function is COUNTIF($A$1:A2,A2) which returns 1. Subtract 1 from the result of COUNTIF
and you'll get 0, which when added to the value 3 from RANK gives us 3, the correct rank of the first
occurrence of 22. Now, look at the formula in cell B4, which is adjacent to the second occurrence of the
value 22. As before, the RANK function returns 3, but the COUNTIF function is now
COUNTIF($A$1:A4,A4) which returns 2, the number of values 22 in range A1:A4. Subtract 1 from that
result and add that to the result of RANK, and you'll get 4. This formula will work with any number of
duplicated values. The reason that it works is that for any value V that occurs N times in a list, with a rank
of R, there are N rank values R, and the next N-1 occurrences of V have the rank R. This means that N-1
rank values are omitted and the next rank value is R-N. The COUNTIF()-1 piece of the formula adds back
N-1, and R+N-1 is the rank of the Nth occurrence of the value V. For as many occurrences N of V exist in
the list, the RANK function omits N-1 rank value. The COUNTIF()-1 simply adds those missing rank
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
values back into the ranking. In the following screen shot, the original data values to be ranked are shown
in blue, the intermediate calculated results in black, and the final results shown in .
Unique Ranks In Ascending Order
The logic for unique ascending ranks is a bit different than that for descending unique ranks. With your
data in the range A1:A5, enter the following formula in cell B1 and fill down to cell B5.
=COUNT($A$1:$A$5)-(RANK(A1,$A$1:$A$5)+COUNTIF($A$1:A1,A1)-1)+1
The formula works in the following manner. The total number of elements minus the descending rank of a
value is that value's ascending rank as if the rankings were 0-based rather than 1-based. For example, the
unique descending rank of the value 33 is 2. Subtracting this from the total number of elements, 5, returns
the zero based rank of 3. Adding 1 brings the rank to a 1-based system and therefore 33 gets the rank of
4. It is important to note that even though this formula calculates the ascending ranks, it uses the
descending format of the RANK function. In the screen show below, the data to be ranked is shown in blue,
the intermediate calculations in black, and the final results in red.
Nasir Mustafa MBA(HRM)
Descending Player Rankings
While the formulas illustrated above are useful for the direct ranking of data, it is very often the case that
you want to return a ranked list of the players or other entities that have ranked scores or values. For
example, a ranking of bowling scores is useful really only when you can retrieve the players in scored rank.
The actual scores are less useful than are the player names themselves. This section will create a list of
players according to the ranked scores, in descending rank order.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
In the following example, the player names are in B4:B13 and each player's score is in C4:C13. We use
the following formula to calculate the unique descending rankings of the players' scores. This is the same
formula descibed earlier in this article.
=RANK(C4,$C$4:$C$13,0)+COUNTIF($C$4:C4,C4)-1
Enter this formula in cell E4 and fill down to E13. This gives us the unique rankings of the player's scores
that we will use to retrieve the player names in scored order. Enter the following formula in cell G4 and fill
down to G13. This will list the names of the players, listed in B4:B13 in the order of the unique ranks in
E4:E13.
=OFFSET(B$4,MATCH(SMALL(E$4:E$13,ROW()-ROW(G$4)+1),E$4:E$13,0)-1,0)
In the screen image below, the input data is shown in blue, the unique rankings in black, and the final
results in .
Ascending Player Rankings
The previous section display players ordered by a descending unique rank. The same players may be
ordered by ascending rank order. With the player names in cells B18:B27 and their scores in cells
C18:C27, enter the following formula in E18:E27
Nasir Mustafa MBA(HRM)
=COUNT($C$18:$C$27)-(RANK(C18,$C$18:$C$27,0)+COUNTIF($C$18:C18,C18))+2
This formula will create unique player ranks in ascending order. These ranks will be used to create the
order list of players. In cell G18, enter the following formula and fill it down to cell G27.
=OFFSET(B$18,MATCH(SMALL(E$18:E$27,ROW()-ROW(G$18)+1),E$18:E$27,0)-1,0)
This will list the players in ascending rank order. The screenshot below illustrates this.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
Double Ranking And Tie Breaking
In the examples above, we had tied scores, two players having the same score. In those scenarios, there
was no way to break a tie. The tied elements would appear in the result list according to their position in
the input list. This section of the article describes how to use multiple tables to resolve ties. We first have a
primary table that contains the players name and their scores. Next, we have a secondary table that is
used to resolve ties. If two player have the same score in the primary table, those players' scores in the
secondary table are used to break the tie. (If those same player have a tied score in the secondary table,
the results are returned in the order in which they appear in the secondary list.
The core of working with the two lists and breaking the ties in to create a third table, called the Composite
table. This table lists all of the player and calcualtes a composite rank that is calculated from the player's
rank in the primary table and her rank in the secondardy table. Assuming you have the primary table with
player names and scores in the range B6:C15, called Table1, the secondary table, also wih player
names and scores, in cells E6:F7, named Table2, and the composite table in cell H6:J16, with Player
Names in column H, the composite score (to be calcuated later) on column I, and the Composite Rank
(also discussed later) in column J, you can list the players by score in descending rank in column L and
the players by score in acsendning rank in column N. The basic layout of the tables is shown below. The
rest of this section will discuss how write the formula and what those formulas actually do.
The formulas on this page are Array Formula, so you must press CTRL SHIFT ENTER rather than ENTER
when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display
Nasir Mustafa MBA(HRM)
the formulas enclosed in curly braces { }.
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
In the cells of column I in Table1 enter the following formula:
=(C6/MAX(ABS($C$6:$C$15)))+(F6/(10^(MAX(LEN(C$6:C$15)+1))))
This formula calculates the Composite Score. This score is calculated with the following formula:
=(C6/MAX(ABS($C$6:$C$15)))+(F6/(10^(MAX(LEN(C$6:C$15)+1))))
For each row in the Composite Score, this calcualtes the data value divided the maximum data element.
This number, N is subject to constraint 0 <= N <= 1. Added to this is is the value of the secondary (tie
breaking) rank divided by (10^(MAX(LEN($C$6:$C$15)+1). This calculated value is 10 to power of Z
where Z is the longtest (in terms of character length, not numeric value) of the numbers in cells C6:C15. If
the longest number has six characters (e.g., 123.456), the formula (10^(MAX(LEN($C$6:$C$15)+1)
reutrns 10^7. When we divide that into the cell value from the score from the secondary table, we get a
fractional number that is 0's until the number of digits surpass the digit to right of the decimal point from the
score value from Table2. Finally, to break ties between scores in Table2, we divide the current Row
number by ((ROW()/(10^MAX(LEN($F$6:$F$15)+1)))). This ensure that the least significant protion
of the number is scaled past the end of the decimal number to which it is added.
One we have composite score, which combines the scores in the primary and second tables to generate a
unique, properly ordered key, we must rank the composite scores. We do this with the same formula we
used earlier in this to determine unique ranks. In cell J6, enter the following formula and then fill down to
J15.
=RANK(I6,$I$6:$I$15,0)+COUNTIF($I$6:I6,I6)-1
This is just the unique rank of the composite scores. Finally, after all this, we can use the composite ranks
to return the names of the players in either Descending or Ascending order. To return the list in
Descending order, enter the formula in cell L6 and fill down to L15.
=OFFSET(B$6,MATCH(SMALL(J$6:J$15,ROW()-ROW(J$6)+1),J$6:J$15,0)-1,0)
To return the names in Ascending order, enter the following formula in cell N6 and fill down to N15,
=OFFSET(B$6,MATCH(LARGE(J$6:J$15,ROW()-ROW(N$6)+1),J$6:J$15,0)-1,0)
As a practical matter, you don't need to include the player names in any table other than the primary tab.
As longs as everything is in the same rows, you can simply the display to the following:
Nasir Mustafa MBA(HRM)
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
You can an example workbook with the formulas described on this page.
nbsp; Eliminating Blank Cells From Lists
You can use a formula to return only the non-blank
cells from a range. The following function will return
all the cell values in a range named BlanksRange
that are not empty.
Create a range name, with the same number of rows
as BlanksRange called NoBlanksRange. The
range NoBlanksRange must have the same number of rows as BlanksRange but it need
not be in the same row numbers. Enter the following Array Formula in the first cell of
NoBlanksRange, and then use Fill Down to fill out the range:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
The first N rows of NoBlanksRange will contain the N non-blank cells of BlanksRange.
Note that this is an array formula, so you must press Ctrl+Shift+Enter rather than just
Enter when you first enter the formula and whenever you edit it later. If you do this
properly, Excel will display the formula enclosed in curly braces { }.
Note that if you do not use named ranges and enter the actual cell references, you must
use absolute cell references (e.g., $B$1) rather than relative cell references (e.g., B1).
Example
As you can see here, the values in BlanksRange
are shifted up, as the blank entries are removed.
Nasir Mustafa MBA(HRM)
Function NoBlanks(DataRange As Range) As Variant()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NoBlanks
' This function returns an array that consists of the non-blank values
' in DataRange. The function must be array-entered into the complete range
' of worksheet cells that are to receive the result. For example, if
' you want the results in B1:B10, select that range, type
' =NOBLANKS(A1:A10)
' in B1 and press CTRL+SHIFT+ENTER rather than just enter.
' This will cause the function to fill B1:B10 with the N non-blank
' entries in A1:A10, and place vbNullStrings in N+1 to 10.
' The input DataRange must have exactly 1 row or 1 column. You
' can't enter a two-dimensional array. The formula must be
' entered into a single column or singe row. You cannot
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
' enter the formula in a two dimensional array. If the formula
' is entered into a two-dimensional range, or if DataRange is a
' two dimensional range, the function will return #REF errors.
' The size of the array is the greater of the number of cells
' into which it was entered and the number of cells in the input
' DataRange.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
Dim N2 As Long
Dim Rng As Range
Dim MaxCells As Long
Dim Result() As Variant
Dim R As Long
Dim C As Long
If (DataRange.Rows.Count > 1) And _
(DataRange.Columns.Count > 1) Then
'''''''''''''''''''''''''''''''''''''''''''''''''
' If DataRange is a two-dimensional array, fill
' it with #REF errors. We work with only
' single dimensional ranges.
'''''''''''''''''''''''''''''''''''''''''''''''''
ReDim Result(1 To DataRange.Rows.Count, 1 To DataRange.Columns.Count)
For R = 1 To UBound(Result, 1)
For C = 1 To UBound(Result, 2)
Result(R, C) = CVErr(xlErrRef)
Next C
Next R
NoBlanks = Result
Exit Function
End If
If (Application.Caller.Rows.Count > 1) And _
(Application.Caller.Columns.Count > 1) Then
'''''''''''''''''''''''''''''''''''''''''''''''''
' If Application.Caller is a two-dimensional array, fill
' it with #REF errors. We work with only
' single dimensional ranges.
'''''''''''''''''''''''''''''''''''''''''''''''''
ReDim Result(1 To Application.Caller.Rows.Count, 1 To
Application.Caller.Columns.Count)
For R = 1 To UBound(Result, 1)
For C = 1 To UBound(Result, 2)
Result(R, C) = CVErr(xlErrRef)
Next C
Next R
Nasir Mustafa MBA(HRM)
NoBlanks = Result
Exit Function
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Get the greater of Application.Caller.Cells.Count
' and DataRange.Cells.Count. This is the size
' of the array we'll return. Sizing it to the
' maximum value prevents #N/A error from appearing
' in cells past the end of the array, because
' the array will always fill out the cells
' into which it was entered.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
MaxCells = Application.WorksheetFunction.Max( _
Application.Caller.Cells.Count, DataRange.Cells.Count)
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
''''''''''''''''''''''''''''''''''''''''''''
' Resize the array to the proper size.
''''''''''''''''''''''''''''''''''''''''''''
ReDim Result(1 To MaxCells, 1 To 1)
''''''''''''''''''''''''''''''''''''''''''''
' Loop through DataRange and place non-blank
' cells at the front of the array.
''''''''''''''''''''''''''''''''''''''''''''
For Each Rng In DataRange.Cells
If Rng.Value <> vbNullString Then
N = N + 1
Result(N, 1) = Rng.Value
End If
Next Rng
''''''''''''''''''''''''''''''''''''''''''''
' Fill the remaining array elements with
' vbNullStrings so they don't appear
' as 0 on the worksheet.
''''''''''''''''''''''''''''''''''''''''''''
For N2 = N + 1 To MaxCells
Result(N2, 1) = vbNullString
Next N2
'''''''''''''''''''''''''''''''''''''''''''
' If the formula was entered into a single
' row across several columns, Transpose the
' result array.
'''''''''''''''''''''''''''''''''''''''''''
If Application.Caller.Rows.Count = 1 Then
NoBlanks = Application.Transpose(Result)
Else
NoBlanks = Result
End If
End Function
Nasir Mustafa MBA(HRM)
For students different free books are available in http://www.ik20.com/ thanks for visiting website, this site is updated regularly and
daily different useful software and books are uploaded. Please mail us if you like these services at datareceive@hotmail.com
