Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / November 2006

Tip: Looking for answers? Try searching our database.

Another Error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
DavidB - 09 Nov 2006 04:56 GMT
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.
Reply to this Message
Max - 09 Nov 2006 06:19 GMT
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 ?
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.
Reply to this Message
DavidB - 09 Nov 2006 22:19 GMT
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

> Hazarding a plunge here ..
>
[quoted text clipped - 18 lines]
> > So could someone help me to understand this formula or what I might be doing
> > wrong.
Reply to this Message
Max - 10 Nov 2006 02:31 GMT
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.
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks Max for your time, really appreciated.
> I should have briefly explained what I'm doing, sorry. This database thing
[quoted text clipped - 19 lines]
> Cheers
> David
Reply to this Message
DavidB - 10 Nov 2006 02:57 GMT
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

> 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
[quoted text clipped - 23 lines]
> > Cheers
> > David
Reply to this Message
Max - 10 Nov 2006 03:59 GMT
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
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max
>
[quoted text clipped - 7 lines]
> Cheers
> David
Reply to this Message
DavidB - 10 Nov 2006 04:45 GMT
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.

> Try this on a spare copy of your file ..
>
[quoted text clipped - 45 lines]
> > Cheers
> > David
Reply to this Message
Max - 10 Nov 2006 07:57 GMT
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
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.
Reply to this Message
Max - 10 Nov 2006 23:26 GMT
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.
Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.