Another Error
 

I have this formula that keeps producing a "HD" in the destination cell H8
even though it reads this way...
=IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8 =""),"",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8, BQ8,BR8=49),"NGP",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ 8,BN8,BO8,BQ8,BR8<=48),"F",IF(AND(Y8,AA8,AB8,AC8,A L8,AW8,BJ8,BN8,BO8,BQ8,BR8="R"),"R",IF(AND(Y8,AA8, AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8=85),"HD",IF(A ND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8=75) ,"D",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8 ,BR8=65),"C")))))))

So could someone help me to understand this formula or what I might be doing
wrong.

Another Error
 

Hazarding a plunge here ..

Assuming the 35 interceding cells within Y8:BR8, ie cells other than the 11
cells specified in your discontiguous range, for eg: Z8, AD8:AK8, AM8:AV8,
etc are just blank cells

Then possibly .. this might yield what you're after, in H8:
=IF(COUNTA(Y8:BR8)=0,"",IF(COUNTIF(Y8:BR8,"R")=11, "R",IF(COUNTIF(Y8:BR8,"=85")=11,"HD",IF(COUNTIF(Y 8:BR8,"=75")=11,"D",IF(COUNTIF(Y8:BR8,"=65")=11, "C",IF(COUNTIF(Y8:BR8,49)=11,"NGP",IF(COUNTIF(Y8:B R8,"<=48")=11,"F","Undefined")))))))

H8 will return "Undefined" if your discontiguous range doesn't satisfy** any
of the 7 conditions specified. I dug these 7 conditions out of your posted
formula <g, and re-arranged it carefully to be evaluated in the correct
sequence, from left to right within the IF construct.

**For example: if all 11 cells in your discontiguous range contain numbers
between 49 to <65, what then is the desired return ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote:
I have this formula that keeps producing a "HD" in the destination cell H8
even though it reads this way...
=IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8 =""),"",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8, BQ8,BR8=49),"NGP",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ 8,BN8,BO8,BQ8,BR8<=48),"F",IF(AND(Y8,AA8,AB8,AC8,A L8,AW8,BJ8,BN8,BO8,BQ8,BR8="R"),"R",IF(AND(Y8,AA8, AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8=85),"HD",IF(A ND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8=75) ,"D",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8 ,BR8=65),"C")))))))

So could someone help me to understand this formula or what I might be doing
wrong.

Another Error
 

Thanks Max for your time, really appreciated.
I should have briefly explained what I'm doing, sorry. This database thing
is an assessment tracker for grading students, it has a bucket load of stuff
involved within it's framework...and I might add that I've had a lot of help
from people like yourself andothers from this discussion group. So as you can
see I'm NOT Excel savvy.

Firstly, the other cells you asked about do contain other numbers/letters
etc that are entered in at variuos times.
Secondly, numbers in the discontiguous range are also entered in at
different times during the students course. This particular range reflects a
co-assessment of several subjects. So H8 being where the result of my desired
return should show when the right conditions are entered into the
discontiguous range either HD or C or D or P or F or NGP etc.

Hope all that makes sense Max
Cheers
David

"Max" wrote:

Hazarding a plunge here ..

Assuming the 35 interceding cells within Y8:BR8, ie cells other than the 11
cells specified in your discontiguous range, for eg: Z8, AD8:AK8, AM8:AV8,
etc are just blank cells

Then possibly .. this might yield what you're after, in H8:
=IF(COUNTA(Y8:BR8)=0,"",IF(COUNTIF(Y8:BR8,"R")=11, "R",IF(COUNTIF(Y8:BR8,"=85")=11,"HD",IF(COUNTIF(Y 8:BR8,"=75")=11,"D",IF(COUNTIF(Y8:BR8,"=65")=11, "C",IF(COUNTIF(Y8:BR8,49)=11,"NGP",IF(COUNTIF(Y8:B R8,"<=48")=11,"F","Undefined")))))))

H8 will return "Undefined" if your discontiguous range doesn't satisfy** any
of the 7 conditions specified. I dug these 7 conditions out of your posted
formula <g, and re-arranged it carefully to be evaluated in the correct
sequence, from left to right within the IF construct.

**For example: if all 11 cells in your discontiguous range contain numbers
between 49 to <65, what then is the desired return ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote:
I have this formula that keeps producing a "HD" in the destination cell H8
even though it reads this way...
=IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8 =""),"",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8, BQ8,BR8=49),"NGP",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ 8,BN8,BO8,BQ8,BR8<=48),"F",IF(AND(Y8,AA8,AB8,AC8,A L8,AW8,BJ8,BN8,BO8,BQ8,BR8="R"),"R",IF(AND(Y8,AA8, AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8=85),"HD",IF(A ND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8=75) ,"D",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8 ,BR8=65),"C")))))))

So could someone help me to understand this formula or what I might be doing
wrong.

Another Error
 

One work-around the restrictions faced here would be to simply link the 11
discontiguous cols into another "helper" sheet where these could be placed
together in a continuous range, then apply the suggested formula in H8 to
point to these cols in the helper sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote in message
...
Thanks Max for your time, really appreciated.
I should have briefly explained what I'm doing, sorry. This database thing
is an assessment tracker for grading students, it has a bucket load of
stuff
involved within it's framework...and I might add that I've had a lot of
help
from people like yourself and others from this discussion group. So as you
can
see I'm NOT Excel savvy.

Firstly, the other cells you asked about do contain other numbers/letters
etc that are entered in at various times.
Secondly, numbers in the discontiguous range are also entered in at
different times during the students course. This particular range reflects
a
co-assessment of several subjects. So H8 being where the result of my
desired
return should show when the right conditions are entered into the
discontiguous range either HD or C or D or P or F or NGP etc.

Hope all that makes sense Max
Cheers
David

Another Error
 

Max

Is there any other way of doing this? Any thoughts on exactly why H8 is not
reflecting what my formula is asking? And (sorry to ask this but...) how do I
go about doing what you have suggested below? This is starting to become
complicated...I think....

Cheers
David

"Max" wrote:

One work-around the restrictions faced here would be to simply link the 11
discontiguous cols into another "helper" sheet where these could be placed
together in a continuous range, then apply the suggested formula in H8 to
point to these cols in the helper sheet.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote in message
...
Thanks Max for your time, really appreciated.
I should have briefly explained what I'm doing, sorry. This database thing
is an assessment tracker for grading students, it has a bucket load of
stuff
involved within it's framework...and I might add that I've had a lot of
help
from people like yourself and others from this discussion group. So as you
can
see I'm NOT Excel savvy.

Firstly, the other cells you asked about do contain other numbers/letters
etc that are entered in at various times.
Secondly, numbers in the discontiguous range are also entered in at
different times during the students course. This particular range reflects
a
co-assessment of several subjects. So H8 being where the result of my
desired
return should show when the right conditions are entered into the
discontiguous range either HD or C or D or P or F or NGP etc.

Hope all that makes sense Max
Cheers
David

Another Error
 

Try this on a spare copy of your file ..

Rename the source sheet (with the discontiguous range) as: X

Name a new sheet as: Y

In Y,

Put in A1: =X!Y8

Put in B1: =X!AA8
Copy B1 to D1

Put in E1: =X!AL8
Put in F1: =X!AW8
Put in G1: =X!BJ8

Put in H1: =X!BN8
Copy H1 to I1

Put in J1: =X!BQ8
Copy J1 to K1


Then in X,

we apply the formula in H8 to point to the continuous range A1:K1 in Y

Put in H8:
=IF(COUNTIF(Y!A1:K1,0)=11,"",IF(COUNTIF(Y!A1:K1,"R ")=11,"R",IF(COUNTIF(Y!A1:K1,"=85")=11,"HD",IF(CO UNTIF(Y!A1:K1,"=75")=11,"D",IF(COUNTIF(Y!A1:K1," =65")=11,"C",IF(COUNTIF(Y!A1:K1,49)=11,"NGP",IF(CO UNTIF(Y!A1:K1,"<=48")=11,"F","Undefined")))))))

Note that there's a slight change required for the 1st IF check, viz:
IF(COUNTIF(Y!A1:K1,0)=11,"", ...

instead of:
=IF(COUNTA(Y!A1:K1)=0,"", ...

The above change is required since the link formulas in Y!A1:K1 will return
zeros for blank source cells in X
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote in message
...
Max

Is there any other way of doing this? Any thoughts on exactly why H8 is
not
reflecting what my formula is asking? And (sorry to ask this but...) how
do I
go about doing what you have suggested below? This is starting to become
complicated...I think....

Cheers
David

Another Error
 

Yes I have followed your instructions and sorry Max but H8 in sheet X only
displays "Undefined" no matter what values I type in into
Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8.

"Max" wrote:

Try this on a spare copy of your file ..

Rename the source sheet (with the discontiguous range) as: X

Name a new sheet as: Y

In Y,

Put in A1: =X!Y8

Put in B1: =X!AA8
Copy B1 to D1

Put in E1: =X!AL8
Put in F1: =X!AW8
Put in G1: =X!BJ8

Put in H1: =X!BN8
Copy H1 to I1

Put in J1: =X!BQ8
Copy J1 to K1


Then in X,

we apply the formula in H8 to point to the continuous range A1:K1 in Y

Put in H8:
=IF(COUNTIF(Y!A1:K1,0)=11,"",IF(COUNTIF(Y!A1:K1,"R ")=11,"R",IF(COUNTIF(Y!A1:K1,"=85")=11,"HD",IF(CO UNTIF(Y!A1:K1,"=75")=11,"D",IF(COUNTIF(Y!A1:K1," =65")=11,"C",IF(COUNTIF(Y!A1:K1,49)=11,"NGP",IF(CO UNTIF(Y!A1:K1,"<=48")=11,"F","Undefined")))))))

Note that there's a slight change required for the 1st IF check, viz:
IF(COUNTIF(Y!A1:K1,0)=11,"", ...

instead of:
=IF(COUNTA(Y!A1:K1)=0,"", ...

The above change is required since the link formulas in Y!A1:K1 will return
zeros for blank source cells in X
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote in message
...
Max

Is there any other way of doing this? Any thoughts on exactly why H8 is
not
reflecting what my formula is asking? And (sorry to ask this but...) how
do I
go about doing what you have suggested below? This is starting to become
complicated...I think....

Cheers
David

Another Error
 

It should work ok. Pl try again.

Type say, the number 49 into all 11 source cells in X, viz:
Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8.
and H8 should return: NGP

Enter the letter: R in all 11 cells,
H8 should return: R, and so on

Also, ensure that calc mode is not set accidentally to Manual. Click Tools
Options Calculation tab
Ensure Automatic is checked
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DavidB" wrote in message
...
Yes I have followed your instructions and sorry Max but H8 in sheet X only
displays "Undefined" no matter what values I type in into
Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8.

Another Error
 

Pl note that the correct final result is read-off in H8 only when all 11
source cells are completely filled with numbers (or letter "R") or when they
are completely cleared.

This is consistent with your original post's intents as expressed eg:
AND(Y8,AA8,AB8,AC8,AL8 ... =49)

Ignore H8's intermediate returns as the 11 source cells are progressively
filled. Read off the H8 result only when the source is completely filled /
cleared.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---