View Single Post
 
Old 06-16-2016, 05:46 AM
ddeaton ddeaton is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jun 2016
Posts: 6
ddeaton is on a distinguished road
Default 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
Reply With Quote