#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 Ctrl-Shift-Enter.
__________________
Cheers, Paul Edstein [Fmr 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 [Fmr 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 [Fmr 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)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#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"})) )
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#11
|
|||
|
|||
If I have understood you correct.
|
#12
|
|||
|
|||
Thank you very much!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Counting Grades on Excel Spreadsheet | Lear2016 | Excel | 1 | 01-04-2016 12:58 PM |
How to manipulate the entire image? | markg2 | Drawing and Graphics | 4 | 04-17-2014 04:52 AM |
Software to create weighted hierarchy chart? | RichardD | Office | 1 | 02-10-2012 07:56 PM |
help me to manipulate excel data | ibor | Excel | 2 | 02-01-2011 03:14 AM |
How to manipulate Hyperlinks | mecaton2 | Word | 0 | 10-04-2010 06:18 AM |