Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-16-2016, 05:46 AM
ddeaton ddeaton is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Novice
Complex IF(AND statement to manipulate weighted grades - better way?
 
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
  #2  
Old 06-16-2016, 06:28 AM
macropod's Avatar
macropod macropod is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,465
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #3  
Old 06-16-2016, 06:37 AM
ddeaton ddeaton is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Novice
Complex IF(AND statement to manipulate weighted grades - better way?
 
Join Date: Jun 2016
Posts: 6
ddeaton is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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
Reply With Quote
  #4  
Old 06-16-2016, 07:24 AM
macropod's Avatar
macropod macropod is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,465
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #5  
Old 06-16-2016, 07:35 AM
ddeaton ddeaton is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Novice
Complex IF(AND statement to manipulate weighted grades - better way?
 
Join Date: Jun 2016
Posts: 6
ddeaton is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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
Reply With Quote
  #6  
Old 06-16-2016, 02:46 PM
macropod's Avatar
macropod macropod is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,465
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #7  
Old 07-11-2016, 08:55 AM
ddeaton ddeaton is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Novice
Complex IF(AND statement to manipulate weighted grades - better way?
 
Join Date: Jun 2016
Posts: 6
ddeaton is on a distinguished road
Default 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
File Type: xlsx 2016-04-09AMT-SPR-001_TEMPLATE_b.xlsx (86.1 KB, 2 views)
Reply With Quote
  #8  
Old 07-11-2016, 09:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,431
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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)
Attached Files
File Type: xlsx Copy of 2016-04-09AMT-SPR-001_TEMPLATE_b.xlsx (89.2 KB, 2 views)
Reply With Quote
  #9  
Old 07-11-2016, 10:37 AM
ddeaton ddeaton is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Novice
Complex IF(AND statement to manipulate weighted grades - better way?
 
Join Date: Jun 2016
Posts: 6
ddeaton is on a distinguished road
Default

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
Attached Files
File Type: xlsx 2016-04-09SPR-001_TEMPLATE_c.xlsx (86.2 KB, 3 views)
Reply With Quote
  #10  
Old 07-11-2016, 11:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,431
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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"})) )
Reply With Quote
  #11  
Old 07-11-2016, 11:29 AM
xor xor is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 10 Complex IF(AND statement to manipulate weighted grades - better way? Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,068
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

If I have understood you correct.
Attached Files
File Type: xlsx 2016-04-09SPR-2.xlsx (86.5 KB, 1 views)
Reply With Quote
  #12  
Old 07-11-2016, 02:03 PM
ddeaton ddeaton is offline Complex IF(AND statement to manipulate weighted grades - better way? Windows 7 64bit Complex IF(AND statement to manipulate weighted grades - better way? Office 2010 64bit
Novice
Complex IF(AND statement to manipulate weighted grades - better way?
 
Join Date: Jun 2016
Posts: 6
ddeaton is on a distinguished road
Default

Thank you very much!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Complex IF(AND statement to manipulate weighted grades - better way? Counting Grades on Excel Spreadsheet Lear2016 Excel 1 01-04-2016 12:58 PM
Complex IF(AND statement to manipulate weighted grades - better way? How to manipulate the entire image? markg2 Drawing and Graphics 4 04-17-2014 04:52 AM
Complex IF(AND statement to manipulate weighted grades - better way? 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

Other Forums: Access Forums - Senior Forums

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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft