If statements

[k11ngy]

Trying to work out formula but driving me mad

This is one I created but does not work. I also tried ctrl+shift+enter?

=IF(A6:B6="A","Pass",""),IF(AND(A6:B6="A",C6:D6="A","Merit","Pass"),IF(AND(A6:B6="A",C6:D6="A",E6:F6="A","Dist","merit")))


Its to do with grading at college and need to create a statement that caters for

Example

I am probaly doing it the hard way but just want a result of pass, merit or disitinction

Thanks for any help

Steve

[tigeravatar]

k11ngy,

I see what you're trying to do, but unfortunately, it doesn't work the way you want it to. Your formula starts with

Please Login or Register  to view this content.

So I think you're trying to say "if A6 = A and B6 = Pass, then ..."
The way to do that is use an Or statement set to false:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[k11ngy]

k11ngy,

I see what you're trying to do, but unfortunately, it doesn't work the way you want it to. Your formula starts with

Please Login or Register  to view this content.

So I think you're trying to say "if A6 = A and B6 = Pass, then ..."
The way to do that is use an Or statement set to false:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Hi and thanks for reply

What I am trying to do is for example

If all cells from say A1:d1 are all ="A" student would get a pass or "" as in they have to achieve all criteria for a PASS (Else "")

Then to achieve merit they would have to have "A" in cells a1:d1 plus "A" in say E1:h1 which would be merit and if any were missing it would only be a pass

Then for distinction they would need all of the above plus say "A" in cells i1:L1 otherwise merit

Hope I have made it more clearer (Probably not)

Thanks VM again

Would it be easier to attach spreadsheet with what I have, its only a samll sample sheet

Steve

[tigeravatar]

Steve,

Try this formula. It uses the example regions you provided. I tested it and it performs as required based on your description:

Please Login or Register  to view this content.

If its still not working for you, then yes, you would want to upload a sample sheet so I can see what's going on.

Hope this helps,
~tigeravatar

[tigeravatar]

Steve,

Here's an alternate formula that is more condense. It can be used because the criteria for each cell range is the same. It uses Countif to count the number of "A"s in a region and if it equals 4 (the number of cells in the sample region), then that section is all "A"s:

Please Login or Register  to view this content.

~tigeravatar

[k11ngy]

You guys are geniuses


Thank you so much for help

Steve

Hi and thanks for reply

What I am trying to do is for example

If all cells from say A1:d1 are all ="A" student would get a pass or "" as in they have to achieve all criteria for a PASS (Else "")

Then to achieve merit they would have to have "A" in cells a1:d1 plus "A" in say E1:h1 which would be merit and if any were missing it would only be a pass

Then for distinction they would need all of the above plus say "A" in cells i1:L1 otherwise merit

Hope I have made it more clearer (Probably not)

Thanks VM again

Would it be easier to attach spreadsheet with what I have, its only a samll sample sheet

Steve

[k11ngy]

Thank you again

Steve