How do I string formulas together in Excel to display variables

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
 
B

Bob Phillips

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)
 
S

SteveW

WHat error messages
or give us an example of the edited formula that doesn't work

Steve
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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
 
R

Roger Govier

Hi David

Try
=IF(A1>84,"HD,IF(A1>74,"D",IF(A1>64,"C",IF(A1>49,"P","NGP"))))
 
G

Guest

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
 
G

Guest

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
 
R

Roger Govier

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",""))))))
 
G

Guest

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
 
R

Roger Govier

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"))))))
 
G

Guest

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
 
G

Guest

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
 
R

Roger Govier

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",
""
)))))
 
G

Guest

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
 
R

Roger Govier

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to create a bubble chart with more than 2 variables 0
How can I turn off all formulas in Excel? 5
Simple formulas don't work (very very weird) 2
Copying Formulas in Excel 2003 2
how do I display cell formulas in Excel 2007 2
How do I display formulas in 2007 Excel spreadsheets? 3
Nest 3 SUMPRODUCT formulas - Possible? 7
How do I add the formula bar? 2

Top