Help with an IF/THEN formula in EXCEL

[Rudyard]

Hi, completely new at excel forumals and my google search keeps leading to a tut that is 1 step below what I need.

Basically I have a tally of scores and at the end a total sum of those scores. Lets say F1 is the total of someones score.

Essentially I just want the next column (G1) to tell me if someone is at FAIL,PASS,CREDIT or DISTINCTION stage.

this is my own formula that is not excel compliant!:

IF F1<50=FAIL, F2>50 but <60=PASS, F2>60 but <70=CREDIT, F2>70=DISTINCTION


If anyone could help me out or point me to a tut that covers this that would be great.

[poisondeathray]

You can use nested conditional , or lookup table

Look at some of the examples with grades (example 3 & 4)
http://office.microsoft.com/en-us/excel-help/if-HP005209118.aspx

[Rudyard]

great, a quick browse has exactly what im after, will give it a shot. Much appreciated.

[vhelp]

IF F1<50=FAIL, F2>50 but <60=PASS, F2>60 but <70=CREDIT, F2>70=DISTINCTION

basically, you have to build recusive if/then in excel. there is no block-if/then unless you go the vba route in a module, but that can be a problem if security levels are set on each computer and/or allow certain functions related to macros are turned on/off. anyway. personally, i prefer to be safe and break out complex if/then type formulas into more than one cell. anyway. you formula is too complex and has should be split into two. change the values in column F1 to see the different results.

edit: oops, i seem to have missed a few conditions. oh well..i'll leave that up to you to figure out...hint hint might need to split.

[Rudyard]

Thanks for all your help, the winning formula was:

=IF(L3>82.5,"DISTINCTION",IF(L3>69.5,"CREDIT", IF(L3>49.5,"PASS",IF(L3>40,"CHECK","FAIL"))))