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