AND function, OR function with own value

[Guest]

hi,
I'm asked to mark "fail" if there is a score under 70, or mark "pass". I
have to use function OR.

and I'm also asked to mark "good" if the total score is over 85. I have to
use function AND.

I was wondering how it is possible, because in those functions there is no
your own named value if the value is true or false. All it displays in those
cases is "true" or "false".
Is my instructor wrong when asking these questions?
please help , thanks

 

[Bryan Hessey]

Try,

=if(score>85,"pass Good",if(score>=70,"pass","fail"))

should serve your purpose.

--

 

[edcosoft]

Your instructior ws not wrong and I don';t think you have a concept of
returning "pass", "fail" and "good" instead of "true" or "false"in
Function formulas. If I take your question literally you must have
one formula that marks grades "fail" or "pass" using the Function OR to
determine that, and in the same formula mark grades "good" if they
total over 85 and use the Function AND to solve that. I presumed
there are 3 grades, in cells A2, A3 and A4. The one formula meeting
all the criteria and returning the correct answer is:

=(IF(AND(SUM(A2:A4)>85,A2>70,A3>70,A4>70),"good",IF(OR(A2<70,A3<70,A4<70),"fail","pass").

ed

 

[Bryan Hessey]

Well spotted ed, obviously you read the question, however your formula
uses >70 when testing for 'good' and =70 fails this test, A2>=70
appears to be needed.

One further point, the question indicates there is more than one score
to consider, which means a minimum of two scores, and for the 'good'
test both (or all) must be not < 70. I do not see how they could not
total > 85 (but obviously maths is not my strong point)
Does this not make 'pass' an impossible answer?

--

 

[edcosoft]

Sorry, you're right, should be >=70.

Is that a question Bryan? "and for the "good" test both (or all) must
be not < 70" That's where the order of the tests is necessary. The
first test is for "good" where all must be >70 AND total >85 ( He
couldn't be both "good" and "fail" where one <70 and total >85). The
secod test is if ANY are <70 "fail" which is where the OR comes in.
And, therefor, "good" only applies if the first 2 tests fail, which
means none are <70 but they don't total 85 (how could that be?).
ed

 

[Bryan Hessey]

Hi,

the question is, if 'Good' is "-all scores must be 70 or greater-", and
there must be multiple scores, how could they total LESS than 85 ?
ie, minimum for 'Good' is 70+70, I cannot make that less than 85.

'Pass' is all scores 70 or greater that did not total 85, seemingly
impossible.

I wonder if 'Good' was supposed to be an AVERAGE score of 85 or better
and no score < 70? at least this would make some sense of the
question.

Cheers

 

[edcosoft]

Yes, Bryan, it's impossible to have anything left over to mark "pass".
But, if the formula is written to mark "pass" regardless of what the
total is, you couldn't have a "good" grade because there wouldn't be
any possiblity of anything escaping "pass" that could be "good".

I don't think the actual problem was presented to us, just the OP's
condensation of many possible variations. Instead of anwering them all,
I just picked the litteral one and maybe he'd tell us what the actual
problem was. ed