Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom error bars not working | Charts and Charting in Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Problem Opening an Excel File MSO9.DLL Error | Links and Linking in Excel | |||
error "module not found" | Excel Discussion (Misc queries) | |||
#REF error | Excel Worksheet Functions |