Daily posts of Excel tips…and other stuff
Archive for the ‘Excel Experts E-Letter’ Category.
Created by David Hager, Bob Umlas and Laurent Longre
The problem - to create an array containing only the unique items from an expanding column list. In other words, if items are typed down column A, what is the formula that will return the unique items? The following example further illustrates the problem.
In this case, the [...]
Created by David Hager
This array formula returns the number of unique items in a worksheet range.
=SUM(1/COUNTIF(Rng,Rng))
However, if Rng contains blank cells, this formula returns an error. In this case, use this modified version of the formula.
=SUM(COUNTIF(Rng,Rng)/IF(NOT(COUNTIF(Rng,Rng)),1,COUNTIF(Rng,Rng))^2)
This array formula returns the Nth largest unique value in a column range.
=LARGE(IF(MATCH(Rng,Rng,0)=ROW(Rng)-MIN(ROW(Rng))+1,Rng,”"),N)
To apply data validation to a column which [...]
By Myrna Larson and David Hager
Presented below are 3 UDF's (SumProduct3D, SumIf3D, CountIf3D) that
provide a useful method of returning a variety of information from 3D
ranges. Each of these functions use a 3D range argument (written as per
the usual Excel protocol) as a string. This string is processed by the
Parse3DRange function, which returns sheet positions and [...]
Created by Laurent Longre
The problem - to make a 3D worksheet array formula. What this means is to
create an array representing a z-range (a range across worksheets) that
evaluates in the formula bar as an array. The 3D range used in Excel, i.e.
Sheet1:Sheet4!A2:B5
does not behave that way. I suspect that nearly everyone on the EEE list
has [...]
POWER FORMULA TECHNIQUES
Created by David Hager
The problem - to make a 3D formula that adjusts in a z-relative manner
when it is filled across worksheets. Two separate solutions to this problem
that use a similar methodology are shown below.
Making a 3D Running Total -
This example uses information entered in column A, with the 3D Running Total
formula in [...]
Created by David Hager
To make a conditional format based on the value in the previous
worksheet, create the following defined name formulas.
GlobRef as:
=INDIRECT("rc",FALSE)
which gives the value from the cell it is used in.
PrevShtValue as:
=INDIRECT(INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&"!"&ADDRESS(ROW(),
COLUMN()))
which gives the value from the cell of the same address in the previous
sheet.
Then, combine these in yet another defined name formula.
GTPSV [...]
WORKSHEET FORMULA TIPS
Excel allows you to make 3D formulas based on the following syntax:
Sheet1:Sheet4!A2:B5
However, the functions that can actually use that syntax are limited
(although not as limited as the Excel documentation would lead you to
believe). I put together the following list of functions that represent
most, but probably not all, of those that are 3D-enabled:
AVERAGE, AVERAGEA, [...]
TOP EXCEL WEB SITES
For the best in 3D add-in functionality, check out:
http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm
http://xcell05.free.fr/
Also, check out the freeware file Make Excel 3D in the CompuServe Excel
forum library for a comprehensive set of 3D custom functions.
Editor's Note: I've never used MOREFUNC.XLL, but I've heard a lot of good things about it. Other than the Analysis ToolPack ToolPak, [...]
