Excel help needed.

[ding76uk]

My Excel knowledge is limited at best. i am trying to set up a new mark book. here is an example

What I need is that if I put "P" in P1, P2 & P3 cells it shows "PASS" in the unit grade. If they also get a "P" in the M1 cell the unit grade shows "MERIT" and if I add a "P" in the D1 cell it shows "DISTINCTION". If the cells for P1, P2 & P3 are blank or contain the letter "R" it should say refer in the unit grade. The issue is I cannot add to this formula to make it work when the R is showing in cells for P1, P2 & P3.

Here is the formula i am currently using

=IF(COUNTIF(Q33,"P")=3,IF(COUNTIF(Q3:T3,"P")=4,IF(COUNTIF(Q3:U3,"P")=5,"DISTINCTION","MERIT"),"PASS"),IF(COUNTBLANK(Q33)=0,"","REFER"))

If anyone could give me a hand on this it would be great! as I am pulling my hair out.

Thanks in advance

Carl

 

[Gr8Shot]

Before that macro create another to say if cell = r then change to empty.

 

[Gr8Shot]

Or can you not add this

=IF(COUNTIF(Q33,"r")=1,"","refer")

 

[simo]

Carl, is that a BTEC mark sheet? will watch this thread if so, I deliver a couple of units of the Construction course and have to use a poor attempt at a similar thing, not at all capable of making one of these myself sadly. Regards.

 

[ding76uk]

Carl, is that a BTEC mark sheet? will watch this thread if so, I deliver a couple of units of the Construction course and have to use a poor attempt at a similar thing, not at all capable of making one of these myself sadly. Regards.

Yeah it is. Not quite problem free yet, but am working on it. *** shot cheers for the help,! Haven't had chance to try tonight but will give it a shot in the morning.

 

[f1charlie]

Gr*Shot was almost there, this should work:

=IF(COUNTIF(Q33,"R")>0,"REFER",IF(COUNTIF(Q33,"P")=3,IF(COUNTIF(Q3:T3,"P")=4,IF(COUNTIF(Q3:U3,"P")=5,"DISTINCTION","MERIT"),"PASS"),IF(COUNTBLANK(Q33)=0,"","REFER")))

 

[joesmith]

Here you go, this should work for you:

=IF(COUNTIF(Q3:S3,"p")=3,IF(COUNTIF(Q3:S3,"P")=3,IF(COUNTIF(Q3:T3,"P")=4,IF(COUNTIF(Q3:U3,"P")=5,"DISTINCTION","MERIT"),"PASS")),"Refer")

 

[joesmith]

Or Charlie's post works, mines just slightly shorter. Just a quick note though, for some reason this forum likes to add random spaces, so in his post there is a space after p here: IF(COUNTIF(Q3:U3,"P") which messes it up, hence I put mine as code. Just thought I'd point that out if you find it doesn't work properly!

 

[f1charlie]

Much neater Joe! Although isn't IF(COUNTIF(Q33,"p")=3 at the beginning superfluous?

 

[ding76uk]

Thanks everyone. Joe that worked perfectly and is shorter than where I ended up by trying to do it myself so I learnt the process basing it on your replies.

I ended up with

=IF(COUNTIF(Q33,"P")=3,"PASS",IF(COUNTIF(Q3:T3,"P")=4,IF(COUNTIF(Q3:U3,"P")=5,"DISTINCTION","MERIT"),IF(COUNTBLANK(Q33)=0,"REFER","REFER")))

Yours is neater and smaller though Joe so going to use that.

Thanks everyone for your help! Much appreciated!

Time for some conditional formatting next. Woo and indeed Hoo.