too many conditions! what to do?

[jhroosen]

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

 

[Tom Ogilvy]

Basic approach:
Build a table like this:

Col A ColB
ptmin =5 - 4 * percentrank (if ($Pt:$Pt = Pt; if (isna($X:$X); "";($X:$X);
X);

put your 8 conditionas and your 8 formulas next to them.

then you can do

=vlookup(c,Table,2,False)

doing ctrl+shift+enter is sufficient.

I didn't get a firm grip on the nuances of what you were doing, but
hopefully you can adapt this approach.

If there is a second condition which would be you 8 primary choices with
secondary choices in columns you can set up an 8 by # secondary choices
table and retrieve results with

Index(table,match(c,first column of table,0),match(c1,row above table with
secondary choice labels)