# Thread: having trouble with functions and formulas (excel 2003)

1. ## having trouble with functions and formulas (excel 2003)

Hi there I m having trouble trying to edit this spread sheet using IF and AVERAGE etc can anyone help fill in the holes, but please tell me why and how you have added a formular or function, as i need to learn some how, below are the questions I m asking as to make it clear of what I need to input. Many thanks and if anyone is stuck on Powerpoint or Word I m quite good at those applications. Thanks Kitty

1. Calculate the required totals and statistics.
2. Calculate if the student has passed or failed the assignment and the exam based on the given criteria (in workbook)
3. Calculate the unit result (fail, pass, merit, distinction) based on the criteria given in the workbook and also the fact they must have passed both the exam and the assignments (failure to do so means a fail overall).

2. ## Re: having trouble with functions and formulas (excel 2003)

Welcome to Woody's Lounge!

You're asking a bit much - you want us to complete the entire worksheet for you and provide explanations; that is beyond the scope of this forum.
Many of the formulas are straightforward - in rows 37 to 40, for example, you simply calculate the COUNT, AVERAGE, MAX and MIN of the data in rows 10-35 above.
I'd suggest that you try to create these formulas first. Feel free to post back with a specific question if you get stuck.

3. ## Re: having trouble with functions and formulas (excel 2003)

hi there sorry it was mainly the FAIL PASS MERIT DISTINCTION % that i was stuck on but if no one could do that any help was better than no help, i can do the AVE, MIN, MAX and total. i am very new to excel so all this is mind blowing, cant even make sence of the books i have bought. still hope someone can help even if its a little, i no on the pass/fail i think i have to use absolute referencing but as i said i m not sure about it all, regards kitty

4. ## Re: having trouble with functions and formulas (excel 2003)

The best way to do it is to create a little lookup table with the boundaries 0, 40, 60 and 75 in one column and the grades FAIL, PASS, MERIT and DISTINCTION to the right of the boundaries:

<table border=1><td align=right>0</td><td>FAIL</td><td align=right>40</td><td>PASS</td><td align=right>60</td><td>MERIT</td><td align=right>75</td><td>DISTINCTION</td></table>
You can then use the VLOOKUP function to look up the result of a student and return the corresponding grade. Let's say that you calculate the overall result in S10:S35, and that the lookup table is in V10:W13.
The formula

=VLOOKUP(S10,\$V\$10:\$W\$13,2)

in R10 will return the grade for the student in row 10. But this doesn't yet take into account that the student must pass both the assignments and the examination. The formula can be expanded for that:

=IF(OR(E10="FAIL",O10="FAIL"),"FAIL",VLOOKUP(S10,\$ V\$10:\$W\$13,2))

This formula can be filled down to R35.

5. ## Re: having trouble with functions and formulas (excel 2003)

Replace R5:U5 with 0, 40, 60, 75 for the lookup
In E10:
=IF(OR(COUNT(B1010)<\$B\$5,MIN(B1010)<\$B\$4),"Fail","Pass")

In F10:
=HLOOKUP(AVERAGE(B1010),\$R\$5:\$U\$6,2)

Copy/autofill E10:F10 to E11:F35.

O10 will be similar to E10 but using H4 ands H5 instead of B4 and B5 and using P10 instead of min(B1010)

Steve

6. ## Re: having trouble with functions and formulas (excel 2003)

Once you have got your averages you could use a simple IF formula to get your pass, fail, merit and distinction.

If P10 is the determing cell use the formula =IF(P10>75, "Distinction", IF(P10>60,"Merit",IF(P10>40,"Pass","Fail")))

7. ## Re: having trouble with functions and formulas (excel 2003)

thanks to you all i will attempt to try adding those, its always better if there an explanation, makes it easier to understand, kitty

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•