Jump to content

Calling all excel experts ...


Phooey
 Share

Recommended Posts

... I am trying to put some sheets together to help me track the progress of my students.


What I would like to do is to input their results of various assessments and from these results have a projected grade displayed. I know that this is easy enugh to do with simple maths but it is time consuming when I have 155 students to track so I'm looking for something to save me some time.


I can input simple formulae like IF(B3>60, "PASS", "FAIL") but what I'd really like to do is something along the lines of :


IF B3 is between 40 and 49, "C"

IF B3 is between 50 and 59, ""B

IF B3 is between 60 and 69, "A"

IF B3>69, "A*"


Can any of you geniuses help?


I am using excel 2010 and just for info, know nothing about conditional formatting other than colouring my boxes.


Cheers.

Link to comment
Share on other sites

On my mobile at the moment so difficult to type, but you can embed if statements within if statements.

So something like that:

IF(B3>69, "A*", IF(B3>60, "A", IF(B3>50, "B", IF(B3>40, "C", "FAIL"))))

Link to comment
Share on other sites

On my mobile at the moment so difficult to type, but you can embed if statements within if statements.

So something like that:

IF(B3>69, "A*", IF(B3>60, "A", IF(B3>50, "B", IF(B3>40, "C", "FAIL"))))

 

So does it read the conditions from inside out then?

Link to comment
Share on other sites

I think you need to explore conditional formatting. At least I think that is what it is called. This should allow you to run several rules on the same set of data. I'm not near a PC at the mo so cannot test it out for you but take a look at:


http://www.excel-easy.com/data-analysis ... tting.html


See if that sounds like it could be useful. I use it to highlight cells in differing colours for different result ranges. Sounds similar to your needs.

Link to comment
Share on other sites

It tests them in sequence.

With a single if, it evaluates the condition. If true it returns one value if false returns the other (pass/fail)

In this case instead of fail you tell it to run another If statement.

Link to comment
Share on other sites

Thanks Joe. That works great. I really need to get my head around conditional formatting. I'd be able to do so much more then. :cheers:

No problem, glad it worked!

Conditional formatting is only really for making things look pretty (colours based on cell value) what you needed was a formula to generate a new cell value based on another.

Always happy to help if you have anymore questions :)

Link to comment
Share on other sites

Joe: Can we combine IF with AND?


I can do when this AND this occurs do this


with your help I have managed IF this occurs do this but IF different_this occurs do different_this


What I need to finish it is IF this occurs AND this occurs do this but, IF different_this occurs AND different_this occurs do different_this


Does that make sense?

Link to comment
Share on other sites

There is an AND function in Excel that can do that. The formula would look something like:


=IF(AND(condition1,condition2),value1,value2)


If condition1 and condition2 are both true, then you get value1, otherwise you get value2. And you can drop in another IF statement in place of either of the values. Like:


=IF(AND(condition1,condition2),value1,IF(AND(condition3,condition4),value3,value4))

eg: =IF(AND(B2<5,C2<5),"A",IF(AND(B2>10,C2>10),"B","C"))


If condition1 and condition2 are both true, then you get value1, otherwise if condition3 and condition4 are true, you get value3, otherwise you get value4.

Link to comment
Share on other sites

That brings back memories, not worked with spread sheets to that extent in 10 years.

Now just gave to look at them not create them. :(

Conditional formatting great for showing when items go out of spec or fail.

Link to comment
Share on other sites

There is an AND function in Excel that can do that. The formula would look something like:


=IF(AND(condition1,condition2),value1,value2)


If condition1 and condition2 are both true, then you get value1, otherwise you get value2. And you can drop in another IF statement in place of either of the values. Like:


=IF(AND(condition1,condition2),value1,IF(AND(condition3,condition4),value3,value4))

eg: =IF(AND(B2<5,C2<5),"A",IF(AND(B2>10,C2>10),"B","C"))


If condition1 and condition2 are both true, then you get value1, otherwise if condition3 and condition4 are true, you get value3, otherwise you get value4.

 

Thanks Mark. Will take a look at this. I wish I didn't have to but my boss wants all this data displayed so that he can see it and I need to ammend with the minimum of fuss!

Link to comment
Share on other sites

What might also be useful here, depending on you use, would be to put iferror(then the formula you have)folowed by ””. This would mean that in the event of there being nothing in the cell the formula is looking at, it will display nothing, rather than showing an error. This would mean you could make a template for next time, and it not look strange when it is not filled in.

Link to comment
Share on other sites

Why not simply use the math formulae you have and embed it in a simple Macro? The 'And' and 'If' statements etc. you can call up are just predetermined macros for ease of use.


I would build a pivot table to give the 'results' function and a nested macro to run the math.


There are many 'blogs' on this subject - 'g@@gle' is your fiend!


:cheers:

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

  • Welcome to The Motorbike Forum.

    Sign in or register an account to join in.

×
×
  • Create New...

Important Information

Terms of Use Privacy Policy Guidelines We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.

Please Sign In or Sign Up