Calling all excel experts ...

[Phooey]

... 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.

[Joeman]

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

[Phooey]

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?

[tPIC]

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.

[Joeman]

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.

[Phooey]

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.

[Joeman]

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.

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

[Phooey]

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?

[Marky]

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.

[JRH]

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.

[Phooey]

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!

[RantMachine]

http://memesvault.com/wp-content/uploads/I-Have-No-Idea-What-Im-Doing-Dog-02.jpg

[Phooey]

I think, THINK, I've done it guys

[RantMachine]

[Phooey]

 

More likely that they all got A's because I f**ked up the excel formulae

[Rik398]

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.

[Phooey]

Now that would be usefull because I will need templates to populate over and over again. Thanks.

[Grumpy Old Git]

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!