i am trying to create one formula that handles eight different case
(the differing values of C below). on top of that, I'm pulling dat
from a spreadsheet that contains #N/A's, which requires furthe
specifying of conditions. all in all, i would end up with 22 condition
within a single formula. is there a way to make this work without havin
to alter the data source? excel help tells me that i can include
maximum of seven nested formulas with in another.

I have the following set up:

C= “gmin”, “gmax”,”imin”, “imax”, “ptmin”, “ptmax”, “iptmin”, “iptmax”
X = refernce cell
$X:$X = range of cells across which X is ranked
Pt = reference cell containing information on production type
I = reference cell containing information on industry type
Ipt = reference cell containing information on production and industr
type
$Pt:$Pt = range of cells containing production-type-information on al
firms
$I:$I = range of cells containing industry-type-information on al
firms
$Ipt:$Ipt = range of cells containin
production-industry-type-information on all firms

theoretically, the formula should look like this:

{= if ( C = “gmin”; 5 – 4 * percentrank (if ( isna ($X:$X); “”;$X:$X)
X);
if ( C = “gmax”; percentrank (if (isna ($X:$X); “”;$X:$X); X) * 4 + 1;
if ( C = “imin”; 5 – 4 * percentrank (if ($I:$I = I; if (isna ($X:$X)
“”;($X:$X); X);
if ( C = “imax”; percentrank (if ($I:$I = I; if (isna ($X:$X)
“”;($X:$X); X)*4 + 1);
if ( C = “ptmin”; 5 – 4 * percentrank (if ($Pt:$Pt = Pt; if (isn
($X:$X); “”;($X:$X); X);
if ( C = “ptmax”; percentrank (if ($Pt:$Pt = Pt; if (isna ($X:$X)
“”;($X:$X); X)*4 + 1);
if ( C = “iptmin”; 5 – 4 * percentrank (if ($Ipt:$Ipt = Ipt; if (isn
($X:$X); “”;($X:$X); X);
if ( C = “iptmax”; percentrank (if ($Ipt:$Ipt = Ipt; if (isna ($X:$X)
“”;($X:$X); X)*4 + 1)))))))))}

also. when including an array formula within a formula, (which is wha
i have done with every percent ranking) is it sufficient to simpl
enter the entire formula with ctrl + shilft + enter?

i know i am asking for a lot. i would really appreciate your help.

ja

--
Message posted from http://www.ExcelForum.com