#1




Complex IF(AND statement to manipulate weighted grades  better way?
I have been using the following statement to evaluate a calculated grade. This statement will check to see if there is student listed, if not will remain blank but if it is true will determine a letter grade based on first that a particular grade is above 69.5, then it will determine the letter grade. Works great, but now I need it to also look at an additional range of cells (say K3:W3) to determine that each of those is ALSO above 69.5 before it returns a passing grade otherwise it will be an "F". I managed to get it to work by individually checking each cell with additional *AND statements BUT it would not ignore a blank cell, it treated it like a less than 69.5 score. Additionally this makes for a VERY long formula and I was wondering if there was a better, easier way. I am just now starting to use minimal VBA and cannot find a better solution as of yet. Any suggestions would be appreciated.
=IF(D3="","",IF(AND(AF3>=59.5)*AG3<69.5,"F",IF(AG3 <79.5,"C",IF(AG3<89.5,"B","A")))) This is what works except for the blank cells: =IF(D3="","",IF(AND(K3>=69.5)*AND(L3>=69.5)*AND(M3 >=69.5)*AND(O3>=69.5)*AND(P3>=69.5)*AND(Q3>=69.5)* AND(R3>=69.5)*AND(S3>=69.5)*AND(T3>=69.5)*AND(U3>= 69.5)*AND(V3>=69.5)*AND(W3>=69.5)*AND(AF3>=59.5)*A G3<69.5,"F",IF(AG3<79.5,"C",IF(AG3<89.5,"B","A"))) ) I suppose I could also add a check to see if they are <> "" but that is another 13 evaluations.... Thanks for looking. David 
#2




Your AND functions are doing nothing meaningful in the formulae you posted, so you may as well delete them and use just:
=IF(D3="","",IF((AF3>=59.5)*AG3<69.5,"F",IF(AG3<79 .5,"C",IF(AG3<89.5,"B","A")))) for the first formula, for example. As for the larger problem, it appears amenable to an array formula such as: =IF((SUM(IF(K3:AF3>=69.5,1,0))=COUNT(K3:AF3))*AG3< 69.5,"F",IF(AG3 <79.5,"C",IF(AG3<89.5,"B","A"))) Array formulae are input with CtrlShiftEnter.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#3




Quote:
TIA 
#4




Did you try the array formula I posted? You can add the D3 test if you wish:
=IF(D3="","",IF((SUM(IF(K3:AF3>=69.5,1,0))=COUNT(K 3:AF3))*AG3<69.5,"F",IF(AG3 <79.5,"C",IF(AG3<89.5,"B","A")))) No AND tests are necessary blanks in the K3:AF3 range will be ignored.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#5




Quote:
I did try it, it returns ##### (VALUE)  Also, I am not sure if I am reading your suggestion correctly but I need to check each cell individually to be greater than 69.5, the "SUM" function is confusing me. TIA 
#6




Perhaps, then, you could attach a workbook to a post with some representative data. You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#7




Example Workbook
This all is connected to column AI on the "grades" sheet. Trying to determine the letter grade but certain grades/grade sets must also exceed 69.5 in order to make a passing grade.
Any help would be appreciated. Thanks, David 
#8




Please see attached
The only things that you have to check is the range that needs to be controlled for values <69.5 ( just adapt it) and if the AH cell is the one to be tested to obtain the grade (change it in the LOOKUP function if need be) 
#9




Help me see what I am missing, it still doesn't do what I need.
Checking there is a student for the row by seeing if D3 has an ID number > If false, output "" > If true, check if each cell of range Q:AG is above 69.5 (ignoring blank cells), > if false, output "F" > if true, output the letter grade based on the average in AH Sounds so simple but is driving me crazy. Thank you for your patience, David 
#10




Is it so difficult to adapt my proposition to your needs?
I'll leave it up to you to add absolute/relative references if that is not asking too much... =IF(D3="","",IF(SUMPRODUCT((Q3:AG3<69,5)*(Q3:AG3>0 )),"f",LOOKUP(AH3,{69.5,79.5,89.5},{"c","b","a"})) ) 
#11




If I have understood you correct.

#12




Thank you very much!

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Counting Grades on Excel Spreadsheet  Lear2016  Excel  1  01042016 12:58 PM 
How to manipulate the entire image?  markg2  Drawing and Graphics  4  04172014 04:52 AM 
Software to create weighted hierarchy chart?  RichardD  Office  1  02102012 07:56 PM 
help me to manipulate excel data  ibor  Excel  2  02012011 03:14 AM 
How to manipulate Hyperlinks  mecaton2  Word  0  10042010 06:18 AM 