Named Range question

  • Thread starter Thread starter SGT Buckeye
  • Start date Start date
S

SGT Buckeye

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.
 
By default range names are refenced as absolute cell references, so when you
copy a formula w/a named range you get the same range referenced each time it
was copied. So that's where the problem lies.

In reading your post I'm not sure why you wouldn't use just a regular
relative cell address as named ranges are usually used as unique identifiers.
 
You could have a column for each criterion, with a "Y" or "N"
response. Then you can just use COUNTIF to add the Y's to give you an
overall "score" - is this the kind of thing you mean?

=COUNTIF(B2:M2,"Y")

If there is a grading scale for some of the criteria, then you could
use SUM instead, and allocate numbers for the Yes/No answers and for
the scales.

Hope this helps.

Pete
 
How about some examples and detail?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -
Click to expand...

This is the formula that I would like to enter:

=IF(ISBLANK($A2),"",IF($T2<>"GO","NP",IF($U2<>"GO","NP",IF($AA2>0,"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.
 
This is the formula that I would like to enter:

=IF(ISBLANK($A2),"",IF($T2<>"GO","NP",IF($U2<>"GO","NP",IF($AA2>0,"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 -
Click to expand...

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

=IF(ISBLANK($A23),"",IF(OR($T23<>"GO",
$U23<>"GO"),"NP",IF($AA23>0,"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","WP","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<>"31B",$X23="UNQ"),"NP"

Again, any help is appreciated.
 
I think that I got it to work with the following formula:

=IF(ISBLANK($A23),"",IF(OR($T23<>"GO",
$U23<>"GO"),"NP",IF($AA23>0,"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","WP","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<>"31B",$X23="UNQ"),"NP"

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

- Show quoted text -
Click to expand...

I think Ifigured this one out too using this formula:

=IF(ISBLANK($A5),"",IF(OR($T5<>"GO",
$U5<>"GO"),"NP",IF($AA5>0,"NP",IF($R5="UNQ","NP",IF(AND($R5="31B",
$W5="UNQ",OR($R5<>"31B",
$X5="UNQ")),"NP",IF($Q5="NP","NP",IF($Q5="WP","WP","P")))))))
 
I think Ifigured this one out too using this formula:

=IF(ISBLANK($A5),"",IF(OR($T5<>"GO",
$U5<>"GO"),"NP",IF($AA5>0,"NP",IF($R5="UNQ","NP",IF(AND($R5="31B",
$W5="UNQ",OR($R5<>"31B",
$X5="UNQ")),"NP",IF($Q5="NP","NP",IF($Q5="WP","WP","P")))))))- Hidequoted text -

- Show quoted text
Click to expand...

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.
 
Pls TOP post.
Without looking in detail try something like nesting your ifs
=if(r5="31b", if(w5="unq",1,2))

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I think Ifigured this one out too using this formula:

=IF(ISBLANK($A5),"",IF(OR($T5<>"GO",
$U5<>"GO"),"NP",IF($AA5>0,"NP",IF($R5="UNQ","NP",IF(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
Click to expand...

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.
 
I added a few more conditions and came up with the following formula
which works. Hope this helps somebody else trying to nest a bunch of
different statements:

=IF(ISBLANK($A2),"",IF(OR($T2<>"GO",
$V2<>"GO"),"NP",IF($AF2>0,"NP",IF($R2="UNQ","NP",IF(OR(AND($R2="31B",
$Z2>24),AND($R2="31B",$X2="UNQ"),AND($R2<>"31B",
$AC2>24),AND($R2<>"31B",
$AA2="UNQ")),"NP",IF($Q2="NP","NP",IF($Q2="WP","WP","P")))))))






Pls TOP post.
 
Back
Top