Another Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,BJ8,BN8,BO8,BQ8,BR8<=48),"F",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8="R"),"R",IF(AND(Y8,AA8,AB8,AC8,AL8,AW8,BJ8,BN8,BO8,BQ8,BR8>=85),"HD",IF(AND(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.
 
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(Y8:BR8,">=75")=11,"D",IF(COUNTIF(Y8:BR8,">=65")=11,"C",IF(COUNTIF(Y8:BR8,49)=11,"NGP",IF(COUNTIF(Y8:BR8,"<=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 ?
 
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
 
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

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
 
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(COUNTIF(Y!A1:K1,">=75")=11,"D",IF(COUNTIF(Y!A1:K1,">=65")=11,"C",IF(COUNTIF(Y!A1:K1,49)=11,"NGP",IF(COUNTIF(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
 
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.
 
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
 
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.

---
 
Back
Top