Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
too many conditions! what to do?
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) -- Regards, Tom Ogilvy "jhroosen " wrote in message ... i am trying to create one formula that handles eight different cases (the differing values of C below). on top of that, I'm pulling data from a spreadsheet that contains #N/A's, which requires further specifying of conditions. all in all, i would end up with 22 conditions within a single formula. is there a way to make this work without having to alter the data source? excel help tells me that i can include a 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 industry type $Pt:$Pt = range of cells containing production-type-information on all firms $I:$I = range of cells containing industry-type-information on all firms $Ipt:$Ipt = range of cells containing 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 (isna ($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 (isna ($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 what i have done with every percent ranking) is it sufficient to simply enter the entire formula with ctrl + shilft + enter? i know i am asking for a lot. i would really appreciate your help. jan --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
If Then Conditions | Excel Programming |