How do I string formulas together in Excel to display variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to display a range of quotes in one cell from a formula in that
cell...if you can make sense of that. The first part of the formula works
well and displays one of five messages but I cant add any more formulas to
the existing one without getting error messages, for example:
=IF(AND(B13>84,B13<101),"High Distinction"). This works well but I need to
add more to it in order to display Distiction, Credit, NGP, Fail...I am more
than willing to send to anyone who can help me, the Excel spreadsheet to have
a look at.
Cheers
David
 
Something like

Create a table like so In M1:Nn

0 Normal
75 Distinction
101 High Distinction
110 Credit


and use

=VLOOKUP(A1,M1:N 10,2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
WHat error messages
or give us an example of the edited formula that doesn't work

Steve
 
You defined that you want to display "High Distinction" when B13>84 and
B13<101 but didn't specify the conditions of Distinction, Credit, NGP, Fail...
We need these details to compose the appropriate formula!

Regards,
Stefi
 
Thanks Stefi for your reply, I know that I didn't make myself clear so may I
try to explain in words (hope this works).
What I am trying to do is set up a scoring system for students. The scoring
system is: 85-100 =High Distinction, 75-84 =Distinction, 65-74 = Credit,
50-64 =Pass, N =NGP. So, for example If I type in whatever score into cell A1
then I want cell A5 to output either HD, D, C, NGP. Does that make sense??
My direct email address is "(e-mail address removed)"
Cheers
David
 
Thanks Bob for your reply, I know that I didn't make myself clear so may I
try to explain in words (hope this works).
What I am trying to do is set up a scoring system for students. The scoring
system is: 85-100 =High Distinction, 75-84 =Distinction, 65-74 = Credit,
50-64 =Pass, N =NGP. So, for example If I type in whatever score into cell A1
then I want cell A5 to output either HD, D, C, NGP. Does that make sense??
My direct email address is (e-mail address removed)
Cheers
David
 
Thanks Steve for your reply, I know that I didn't make myself clear so may I
try to explain in words (hope this works).
What I am trying to do is set up a scoring system for students. The scoring
system is: 85-100 =High Distinction, 75-84 =Distinction, 65-74 = Credit,
50-64 =Pass, N =NGP. So, for example If I type in whatever score into cell
B13 then I want cell W13 to output either HD, D, C, NGP. Does that make
sense??
My direct email address is (e-mail address removed)
Cheers
David
 
Roger had it right. Paste this in a5
=IF(A1>=85,"High
Distinction",IF(A1>=75,"Distinction",IF(A1>=65,"Credit",IF(A1>=50,"Pass",IF(A1<50,"NGP")))))'all on one line in A5.
=IF(B1>=85,"High
Distinction",IF(B1>=75,"Distinction",IF(B1>=65,"Credit",IF(B1>=50,"Pass",IF(B1<>"","NGP","")))))' again all one line
Slight modification that doesn't put NGP in empty cell. use fill right and
left to correct the formula for all columns.
Lou
 
Thanks everyone for the help. The formula that you have given me works like a
charm, except the last part which I added and once again I've made a mess. So
here is what I need the formuls to do:
=IF(B14>=85,"High
Distinction",IF(B14>=75,"Distinction",IF(B14>=65,"Credit",IF(B14>=50,"Pass",IF(B14<=49,"Fail",IF(B14=N,"NGP",))))))
This last part doesn't doesn't produce an NGP when I enter N.

Cheers
David
 
Hi David

Since N is text, you need to wrap it in Quotes.
Also you need the final "" after "NGP" for the case where b14 does not
meet any of the criteria


=IF(B14>=85,"High Distinction",
IF(B14>=75,"Distinction",
IF(B14>=65,"Credit",
IF(B14>=50,"Pass",
IF(B14<=49,"Fail",I
F(B14="N","NGP",""))))))
 
Roger, thanks but I even cut and pasted your formula into the relevant cell
but it still doesn't work. What I get now when I enter an N into B14, is High
Distinction. I must be doing something wrong??? This is what I now have in
the formula bar:
=IF(B14>=85,"High
Distinction",IF(B14>=75,"Distinction",IF(B14>=65,"Credit",IF(B14>=50,"Pass",IF(B14<=49,"Fail",IF(B14="N","NGP",""))))))

Cheers
David
 
Hi David

Sorry I hadn't tested, just amended your formula.
Excel regards Text as being higher than numbers, so you need to move
that test to the beginning.
I also moved the test for empty cells to the beginning.
Tested this time, and appears to give all the correct results.

=IF(B14="","",
IF(B14="N","NGP",
IF(B14>=85,"High Distinction",
IF(B14>=75,"Distinction",
IF(B14>=65,"Credit",
IF(B14>=50,"Pass","Fail"))))))
 
Once again thank you everyone. Especially you Roger...It works!!! You guys
have made this project much easier and I don't feel nearly as bad now taking
on this assignment.
I do have part 2 to all this but will give my brain a rest for a day or two
then if I may, get back to you and ask some more mind numbing questions....

Many Thanks
David
 
Hi All

Can anyone tell me why this formula isn't working please:
=IF(AZ13="","",IF(BK13="","",IF(BX13="","",IF(AZ13="CA","CA",IF(BK13="CA","CA",IF(BX13="CA","CA",IF(AZ13="CNA","CNA",IF(BK13="CNA","CNA",IF(BX13="CNA","CNA")))))))))

Cheers
David
 
Hi Dave

You are exceeding Excel's limit of 7 levels of nesting, and you don't
have a final FALSE result if all other tests fail.
I often find it easier to see the problem if I insert line feeds after
each If statement

=IF(AZ13="",""
IF(BK13="","",
IF(BX13="","",
IF(AZ13="CA","CA",
IF(BK13="CA","CA",
IF(BX13="CA","CA",
IF(AZ13="CNA","CNA",
IF(BK13="CNA","CNA",
IF(BX13="CNA","CNA"
)))))))))

Try amending to

=IF(OR(AZ13="",BK13=""),"",
IF(AZ13="CA","CA",
IF(AZ13="CNA","CNA",
IF(OR(BK13="CA",BX13="CA"),"CA",
IF(OR(BK13="CNA",BX13="CNA"),"CNA",
""
)))))
 
Hi there Roger...how are things in the UK (I think thats where you are)
Many, Many thanks for the amendment...The only nesting I'm familiar with is
the one that birds do. I think it will take quite a while to get my head
around Excel...but I'm not giving up, I've promised myself to finish this
assignment even though I have till Christmas.

Cheers
David
 
Hi David
Thanks for the response.
Rather damp, dull October morning here in Wales, but can't complain it's
been glorious weather right through till now.

For more information on Excel in general take a look at Chip Pearson's
site
http://www.cpearson.com/excel/topic.htm
and in particular for nesting problems and how you can overcome the 7
level limit (if you really have to)
http://www.cpearson.com/excel/nested.htm
but I would prefer other techniques rather than large numbers of If's.

If you have more problems, do post back, but you are probably better
starting a new thread for any new questions.
 
Back
Top