Microsoft Office Forums Complex IF(AND statement to manipulate weighted grades - better way?
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-16-2016, 05:46 AM
 ddeaton Windows 7 64bit Office 2010 64bit Novice Join Date: Jun 2016 Posts: 6
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
06-16-2016, 06:28 AM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 20,465

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
[MS MVP - Word]
#3
06-16-2016, 06:37 AM
 ddeaton Windows 7 64bit Office 2010 64bit Novice Join Date: Jun 2016 Posts: 6

Quote:
 Originally Posted by macropod 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.
The AND makes sure there is a student listed in D3, if not it leaves a blank, and it checks that a single cell is above 69.5, if true it returns the appropriate letter grade. This works perfectly but now I need to add a range of cells in the middle K3:W3 only to make sure that IF they contain a value, it must also be above 69.5 or it returns an "F", if it does not contain a value then it is ignored and moves to the next cell. the long, crazy AND statements do exactly that but it will not ignore blank cells, it treats them as less than 69.5 and returns an "F"

TIA
#4
06-16-2016, 07:24 AM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 20,465

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
06-16-2016, 07:35 AM
 ddeaton Windows 7 64bit Office 2010 64bit Novice Join Date: Jun 2016 Posts: 6

Quote:
 Originally Posted by macropod 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.

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
06-16-2016, 02:46 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 20,465

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
07-11-2016, 08:55 AM
 ddeaton Windows 7 64bit Office 2010 64bit Novice Join Date: Jun 2016 Posts: 6
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
Attached Files
 2016-04-09AMT-SPR-001_TEMPLATE_b.xlsx (86.1 KB, 2 views)
#8
07-11-2016, 09:58 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,431

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)
Attached Files
 Copy of 2016-04-09AMT-SPR-001_TEMPLATE_b.xlsx (89.2 KB, 2 views)
#9
07-11-2016, 10:37 AM
 ddeaton Windows 7 64bit Office 2010 64bit Novice Join Date: Jun 2016 Posts: 6

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.

David
Attached Files
 2016-04-09SPR-001_TEMPLATE_c.xlsx (86.2 KB, 3 views)
#10
07-11-2016, 11:28 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,431

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
07-11-2016, 11:29 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,068

If I have understood you correct.
Attached Files
 2016-04-09SPR-2.xlsx (86.5 KB, 1 views)
#12
07-11-2016, 02:03 PM
 ddeaton Windows 7 64bit Office 2010 64bit Novice Join Date: Jun 2016 Posts: 6

Thank you very much!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Lear2016 Excel 1 01-04-2016 12:58 PM markg2 Drawing and Graphics 4 04-17-2014 04:52 AM RichardD Office 1 02-10-2012 07:56 PM ibor Excel 2 02-01-2011 03:14 AM mecaton2 Word 0 10-04-2010 06:18 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:51 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top