On Nov 14, 10:59 am, SGT Buckeye wrote:
On Nov 14, 9:02 am, SGT Buckeye wrote:





On Nov 13, 11:00 pm, SGT Buckeye wrote:

On Nov 13, 5:13 pm, "Don Guillett" wrote:

How about some examples and detail?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"SGT Buckeye" wrote in message

oups.com...

I have a worksheet that has 51 rows (one row per Soldier) including a
header row. The columns contain different variables which will
determine whether each Soldier is eligible for promotion. EX: did
Soldier qualify with primary weapon, pas the APFT, meet height/weight
standards, etc. My problem is there are more than 7 nested functions
that are necessary to complete the formula. So I decided to use named
ranges and broke the formula down into two pieces and input a new
formula similar to this:

=if(namedFormula1, namedFormula1, namedFormula2)

This worked great for the first cell (AC2) but it did not work for
each subsequent cell when I dragged the formula down. How can I make
this work? I have it working in Excel 2007 because I can nest more
functions however most of the people who will use this are still using
Excel 2003. I appreciate any help. Thanks.- Hide quoted text -

- Show quoted text -

This is the formula that I would like to enter:

=IF(ISBLANK($A2),"",IF($T2<"GO","NP",IF($U2<"GO" ,"NP",IF($AA20,"NP",IF($***R2="UNQ","NP",IF(AND($ R2="31B",
$W2="UNQ"),"NP",IF(AND($R2<"31B",
$X2="UNQ"),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP", "P")))))))))

"NP" stands for not promotable, "WP" stands for promotable with a
waiver, "P" stands for promotable. The T column is for APFT, U column
is for height/weight, AA column is for drill attendance, R column is
military occupational specialty (MOS), W column is pistol
qualification, X column is for rifle qualification, Q column is for
time in service and time in grade. I hope this helps you help me.- Hide quoted text -

- Show quoted text -

I think that I got it to work with the following formula:

=IF(ISBLANK($A23),"",IF(OR($T23<"GO",
$U23<"GO"),"NP",IF($AA230,"NP",IF($R23="UNQ","NP ",IF(AND($R23="31B",
$W23="UNQ"),"NP",IF(AND($R23<"31B",
$X23="UNQ"),"NP",IF($Q23="NP","NP",IF($Q23="WP","W P","P"))))))))

I may need to add an additional criteria and can make it work if I can
figure out the proper sntax for a statement that uses if(and(or. I
would like to use this type of statement for this:

IF(AND($R23="31B",$W23="UNQ"),"NP",IF(AND($R23<"3 1B",$X23="UNQ"),"NP"

Again, any help is appreciated.- Hide quoted text -

- Show quoted text -

I think Ifigured this one out too using this formula:

=IF(ISBLANK($A5),"",IF(OR($T5<"GO",
$U5<"GO"),"NP",IF($AA50,"NP",IF($R5="UNQ","NP",I F(AND($R5="31B",
$W5="UNQ",OR($R5<"31B",
$X5="UNQ")),"NP",IF($Q5="NP","NP",IF($Q5="WP","WP" ,"P")))))))- Hide quoted text -

- Show quoted text

I was wrong. The above formula does NOT work as I hoped it would. In
plain English, what I hope to acomplish is this: if cell R5=31B and
cell W5=UNQ then it should return a value of NP but if cell R5<31B it
should evaluate R5<31B and X5=UNQ to return a value of NP. I hope
this helps and you can assist me.