Help with this conditional IF statement

[C-Dawg]

Here's the situation...I have a set of conditions to test and I figured out
the most of the problem. Here's the deal

I added two cells together, the total of the two cells is tested under these
condtions

a. If the cell > 24 then "NO" is placed into a cell
b. If the cell >=21 and < 25 then "AA" is placed into a cell
c. If the cell >=17 and < 21 then "A" is placed into a cell
d. If the cell>= 13 and < 17 then "BB" is placed into a cell
e. If the cell >=9 and < 13 then "B" is placed into a cell
f. If the cell >=4 and < 9 then "C" is placed into a cell

This is the formula I came up and works fine:

=IF(RAW!D4>24,"NO",IF(AND(RAW!D4>=21,RAW!D4<25),"AA",IF(AND(RAW!D4>=17,RAW!D4<21),"A",IF(AND(RAW!D4>=13,RAW!D4<17),"BB",IF(AND(RAW!D4>=9,RAW!D4<13),"B",IF(AND(RAW!D4>=4,RAW!D4<9),"C"))))))

I need to add the following to the condtions (see bracketed text):

a. If the cell > 24 then "NO" is placed into a cell
b. If the cell >=21 and < 25 then "AA" is placed into a cell
c. If the cell >=17 and < 21 then "A" is placed into a cell {no individual
cell can be no more than 11}...e.g. if a1=12 and a2=7, total would be 19,
this would not satisfy the condition because a1=12 which is over the
individual cell number limit.
d. If the cell>= 13 and < 17 then "BB" is placed into a cell {no individual
cell can be no more than 8}...e.g. if a1=9 and a2=6, total would be 15, this
would not satisfy the condition because a1=9 which is over the individual
cell number limit.
e. If the cell >=9 and < 13 then "B" is placed into a cell {no individual
cell can be no more than 6}...e.g. if a1=7 and a2=5, total would be 12, this
would not satisfy the condition because a1=7 which is over the individual
cell number limit.
f. If the cell >=4 and < 9 then "C" is placed into a cell {no individual
cell can be no more than 4}...e.g. if a1=5 and a2=3, total would be 8, this
would not satisfy the condition because a1=5 which is over the individual
cell number limit.

I'd appreciate any assistance in adding the extra conditions

Thanks,

C-Dawg

[Ardus Petus]

What value ("AA", "A", ...) do you return when a cell is over the individual
cell limit?

Cheers,
--
AP

"C-Dawg" <[email protected]> a écrit dans le message de news:
[email protected]...
> Here's the situation...I have a set of conditions to test and I figured
> out
> the most of the problem. Here's the deal
>
> I added two cells together, the total of the two cells is tested under
> these
> condtions
>
> a. If the cell > 24 then "NO" is placed into a cell
> b. If the cell >=21 and < 25 then "AA" is placed into a cell
> c. If the cell >=17 and < 21 then "A" is placed into a cell
> d. If the cell>= 13 and < 17 then "BB" is placed into a cell
> e. If the cell >=9 and < 13 then "B" is placed into a cell
> f. If the cell >=4 and < 9 then "C" is placed into a cell
>
> This is the formula I came up and works fine:
>
> =IF(RAW!D4>24,"NO",IF(AND(RAW!D4>=21,RAW!D4<25),"AA",IF(AND(RAW!D4>=17,RAW!D4<21),"A",IF(AND(RAW!D4>=13,RAW!D4<17),"BB",IF(AND(RAW!D4>=9,RAW!D4<13),"B",IF(AND(RAW!D4>=4,RAW!D4<9),"C"))))))
>
> I need to add the following to the condtions (see bracketed text):
>
> a. If the cell > 24 then "NO" is placed into a cell
> b. If the cell >=21 and < 25 then "AA" is placed into a cell
> c. If the cell >=17 and < 21 then "A" is placed into a cell {no individual
> cell can be no more than 11}...e.g. if a1=12 and a2=7, total would be 19,
> this would not satisfy the condition because a1=12 which is over the
> individual cell number limit.
> d. If the cell>= 13 and < 17 then "BB" is placed into a cell {no
> individual
> cell can be no more than 8}...e.g. if a1=9 and a2=6, total would be 15,
> this
> would not satisfy the condition because a1=9 which is over the individual
> cell number limit.
> e. If the cell >=9 and < 13 then "B" is placed into a cell {no individual
> cell can be no more than 6}...e.g. if a1=7 and a2=5, total would be 12,
> this
> would not satisfy the condition because a1=7 which is over the individual
> cell number limit.
> f. If the cell >=4 and < 9 then "C" is placed into a cell {no individual
> cell can be no more than 4}...e.g. if a1=5 and a2=3, total would be 8,
> this
> would not satisfy the condition because a1=5 which is over the individual
> cell number limit.
>
> I'd appreciate any assistance in adding the extra conditions
>
> Thanks,
>
> C-Dawg

[C-Dawg]

Sorry..forgot to include that...If the individual cell limit threshold is
broken then a "NO" is placed into the cell

"Ardus Petus" wrote:

> What value ("AA", "A", ...) do you return when a cell is over the individual
> cell limit?
>
> Cheers,
> --
> AP
>
> "C-Dawg" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > Here's the situation...I have a set of conditions to test and I figured
> > out
> > the most of the problem. Here's the deal
> >
> > I added two cells together, the total of the two cells is tested under
> > these
> > condtions
> >
> > a. If the cell > 24 then "NO" is placed into a cell
> > b. If the cell >=21 and < 25 then "AA" is placed into a cell
> > c. If the cell >=17 and < 21 then "A" is placed into a cell
> > d. If the cell>= 13 and < 17 then "BB" is placed into a cell
> > e. If the cell >=9 and < 13 then "B" is placed into a cell
> > f. If the cell >=4 and < 9 then "C" is placed into a cell
> >
> > This is the formula I came up and works fine:
> >
> > =IF(RAW!D4>24,"NO",IF(AND(RAW!D4>=21,RAW!D4<25),"AA",IF(AND(RAW!D4>=17,RAW!D4<21),"A",IF(AND(RAW!D4>=13,RAW!D4<17),"BB",IF(AND(RAW!D4>=9,RAW!D4<13),"B",IF(AND(RAW!D4>=4,RAW!D4<9),"C"))))))
> >
> > I need to add the following to the condtions (see bracketed text):
> >
> > a. If the cell > 24 then "NO" is placed into a cell
> > b. If the cell >=21 and < 25 then "AA" is placed into a cell
> > c. If the cell >=17 and < 21 then "A" is placed into a cell {no individual
> > cell can be no more than 11}...e.g. if a1=12 and a2=7, total would be 19,
> > this would not satisfy the condition because a1=12 which is over the
> > individual cell number limit.
> > d. If the cell>= 13 and < 17 then "BB" is placed into a cell {no
> > individual
> > cell can be no more than 8}...e.g. if a1=9 and a2=6, total would be 15,
> > this
> > would not satisfy the condition because a1=9 which is over the individual
> > cell number limit.
> > e. If the cell >=9 and < 13 then "B" is placed into a cell {no individual
> > cell can be no more than 6}...e.g. if a1=7 and a2=5, total would be 12,
> > this
> > would not satisfy the condition because a1=7 which is over the individual
> > cell number limit.
> > f. If the cell >=4 and < 9 then "C" is placed into a cell {no individual
> > cell can be no more than 4}...e.g. if a1=5 and a2=3, total would be 8,
> > this
> > would not satisfy the condition because a1=5 which is over the individual
> > cell number limit.
> >
> > I'd appreciate any assistance in adding the extra conditions
> >
> > Thanks,
> >
> > C-Dawg
>
>
>

[Bernard Liengme]

This seems to do it
=IF(A3>24,"NO",IF(A3>=21,"AA",IF(A3>=17,"A",IF(A3>=13,"BB",IF(A3>=9,"B",IF(A3>=4,"C"))))))&"
"&IF(A3>=21,"",IF(AND(A3>=17,OR(A1>11,A2>11)),"VOID1",IF(AND(A3<17,A3>=13,OR(A1>8,A2>8)),"VOID2",IF(AND(A3<13,A3>=9,OR(A1>6,A2>6)),"VOID3",IF(AND(A3<9,A3>=4,OR(A1>4,A2>4)),"VOID4","")))))

Replace A3 by RAW!D4 and A1 and A2 by correct references
I used VOID 1 , VOID 2,.. for debugging purposes; could just be VOID
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"C-Dawg" <[email protected]> wrote in message
news:[email protected]...
> Here's the situation...I have a set of conditions to test and I figured
> out
> the most of the problem. Here's the deal
>
> I added two cells together, the total of the two cells is tested under
> these
> condtions
>
> a. If the cell > 24 then "NO" is placed into a cell
> b. If the cell >=21 and < 25 then "AA" is placed into a cell
> c. If the cell >=17 and < 21 then "A" is placed into a cell
> d. If the cell>= 13 and < 17 then "BB" is placed into a cell
> e. If the cell >=9 and < 13 then "B" is placed into a cell
> f. If the cell >=4 and < 9 then "C" is placed into a cell
>
> This is the formula I came up and works fine:
>
> =IF(RAW!D4>24,"NO",IF(AND(RAW!D4>=21,RAW!D4<25),"AA",IF(AND(RAW!D4>=17,RAW!D4<21),"A",IF(AND(RAW!D4>=13,RAW!D4<17),"BB",IF(AND(RAW!D4>=9,RAW!D4<13),"B",IF(AND(RAW!D4>=4,RAW!D4<9),"C"))))))
>
> I need to add the following to the condtions (see bracketed text):
>
> a. If the cell > 24 then "NO" is placed into a cell
> b. If the cell >=21 and < 25 then "AA" is placed into a cell
> c. If the cell >=17 and < 21 then "A" is placed into a cell {no individual
> cell can be no more than 11}...e.g. if a1=12 and a2=7, total would be 19,
> this would not satisfy the condition because a1=12 which is over the
> individual cell number limit.
> d. If the cell>= 13 and < 17 then "BB" is placed into a cell {no
> individual
> cell can be no more than 8}...e.g. if a1=9 and a2=6, total would be 15,
> this
> would not satisfy the condition because a1=9 which is over the individual
> cell number limit.
> e. If the cell >=9 and < 13 then "B" is placed into a cell {no individual
> cell can be no more than 6}...e.g. if a1=7 and a2=5, total would be 12,
> this
> would not satisfy the condition because a1=7 which is over the individual
> cell number limit.
> f. If the cell >=4 and < 9 then "C" is placed into a cell {no individual
> cell can be no more than 4}...e.g. if a1=5 and a2=3, total would be 8,
> this
> would not satisfy the condition because a1=5 which is over the individual
> cell number limit.
>
> I'd appreciate any assistance in adding the extra conditions
>
> Thanks,
>
> C-Dawg