I have a column chart with 3 series stacked on top of each other. Is there a
way in this chart to indicate a combined total of the values of all 3 series
at the top of the column?

Hi Everyone,

I need two formula's to get me going, the posible sinerios are either the
student has an Absent (ABS) or he has a pass mark of between 40 - 100. while
draging it down i want it to ignor the cells to the left where there is
either no score or its blank

i.e CELL(A1) 40 1
CELL (A2) Blank ( Ignor if no score)
CELL (A3) 32 0

1. IF(O9<0,"
",IF(O9<"ABS",0,IF(O9<40,0,IF(O9<45,1,IF(O9<50,2,I F(O9<60,3,IF(O9<70,5)))))))


The second formula has three conditions.

Test Exam Total Letter Grade Grade Point Remarks
15 40 55 C 3
PASS
ABS 30 30 F 0
FAIL
12 12 F 0
FAIL
ABS ABS ABS 0 0
ABS

I cant figure out how to go about this?

Thanks in anticipation

With Test in Column A, Exam in B and Total in Column C,

=IF(AND(A2="",B2=""),"",IF(AND(A2=B2,B2="ABS"),"AB S",SUM(A2:B2)))

Will give the totals. Your requirements for Letter Grade and Grade Point
are not very well defined. What are the rules to decide how to calculate
them?

--
Regards

Sandy

Replace@mailinator with @tiscali.co.uk

"Tivpine" wrote in message
...
Hi Everyone,

I need two formula's to get me going, the posible sinerios are either the
student has an Absent (ABS) or he has a pass mark of between 40 - 100.
while
draging it down i want it to ignor the cells to the left where there is
either no score or its blank

i.e CELL(A1) 40 1
CELL (A2) Blank ( Ignor if no score)
CELL (A3) 32 0

1. IF(O9<0,"
",IF(O9<"ABS",0,IF(O9<40,0,IF(O9<45,1,IF(O9<50,2,I F(O9<60,3,IF(O9<70,5)))))))


The second formula has three conditions.

Test Exam Total Letter Grade Grade Point Remarks
15 40 55 C 3
PASS
ABS 30 30 F 0
FAIL
12 12 F 0
FAIL
ABS ABS ABS 0 0
ABS

I cant figure out how to go about this?

Thanks in anticipation

Add the totals in the worksheet. Add a new series using these totals. Select this
added series, and using Chart Type on the Chart menu, change this series to a line
chart type. Double click on the new line series, and on the Patterns tab, select
None for line and markers, then on the Data Labels tab, choose the Values option.
Then double click on a label, and on the Alignment tab, choose the Above position.

You can remove the legend entry for the new series if desired: select the legend,
then click on the series name, and press the Delete key.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

bhank wrote:

I have a column chart with 3 series stacked on top of each other. Is there a
way in this chart to indicate a combined total of the values of all 3 series
at the top of the column?

Hi,

Thanks a million the Formula worked just fine, it was simply magical. About
the requirements for letter grade and grade point, please find below:

Letter Grade formula -
IF(G17<=0,"",IF(G17<40,"F",IF(G17<45,"E",IF(G17<50 ,"D",IF(G17<60,"C",IF(G17<70,"B",IF(G17<=100,"A"," ")))))))
ABS in Total should return F for Letter Grade

Grade Point (GP) -
IF(G17<=0,"",IF(G17<40,0,IF(G17<45,1,IF(G17<50,2,I F(G17<60,3,IF(G17<70,4,IF(G17<=100,5,"")))))))
ABS should return a zero (0)

Remarks Formula (Score Sheet) -
IF(G17<=0,"",IF(G17<40,"FAIL",IF(G17<=100,"PASS"," ")))
ABS should return €œABS€� in the Remarks column

I shall also need assistance with the following:

The following formulas are working but I need them shortened (i.e, the TCR,
TCE, TGP and the Remarks column formulas, each time I apply them I get a
FORMUAL TOO LONG, they DONT fit into the formula bar. My templates are about
70 columns long and have about the same number of rows depending on the
number of students per class.

Total Credit Registered (TCR) Formula -
IF(G10="",0,IF(H10<=5,G$9))+IF(I10="",0,IF(J10<=5, I$9))+IF(K10="",0,IF(L10<=5,K$9))+IF(M10="",0,IF(N 10<=5,M$9))+IF(O10="",0,IF(P10<=5,O$9))+IF(Q10="", 0,IF(R10<=5,Q$9))+IF(S10="",0,IF(T10<=5,S$9))+IF(U 10="",0,IF(V10<=5,U$9))+IF(W10="",0,IF(X10<=5,W$9) )+IF(Y10="",0,IF(Z10<=5,Y$9))+IF(AA10="",0,IF(AB10 <=5,AA$9))+IF(AC10="",0,IF(AD10<=5,AC$9))

Total Credit Earned (TCE) Formula -
IF(H10<=0,0,IF(H10<=5,G$9))+IF(J10<=0,0,IF(J10<=5, I$9))+IF(L10<=0,0,IF(L10<=5,K$9))+IF(N10<=0,0,IF(N 10<=5,M$9))+IF(P10<=0,0,IF(P10<=5,O$9))+IF(R10<=0, 0,IF(R10<=5,Q$9))+IF(T10<=0,0,IF(T10<=5,S$9))+IF(V 10<=0,0,IF(V10<=5,U$9))+IF(X10<=0,0,IF(X10<=5,W$9) )+IF(Z10<=0,0,IF(Z10<=5,Y$9))+IF(AB10<=0,0,IF(AB10 <=5,AA$9))+IF(AD10<=0,0,IF(AD10<=5,AC$9))

Total Grade Point (TGP) Formula -
(G$9*H10)+(I$9*J10)+(K$9*L10)+(M$9*N10)+(O$9*P10)+ (Q$9*R10)+(S$9*T10)+(U$9*V10)+(W$9*X10)+(Y$9*Z10)+ (AA$9*AB10)+(AC$9*AD10)

Remarks Formula for Final Year Students
-IF((IF(G9="",,IF(OR(G9<40,G9="ABS"),G$7,""))&"
"&IF(I9="",,IF(OR(I9<40,I9="ABS"),I$7,""))&"
"&IF(K9="",,IF(OR(K9<40,K9="ABS"),K$7,""))&" "&IF(M9="",,IF(OR(M9<40,
M9="ABS"),M$7,"")))="
",IF(R9<=0.99,"RPT",IF(R9<=1.49,"PASS",IF(R9<=2.39 ,"THIRD CLASS
HONOURS",IF(R9<=3.49,"SECOND CLASS HONOURS LOWER
DIVISION",IF(R9<=4.59,"SECOND CLASS HONOURS UPPER DIVISION",IF(R9<=5,"FIRST
CLASS HONOURS","")))))), "RPT" & " - " &
(IF(G9="",,IF(OR(G9<40,G9="ABS"),G$7,""))&"
"&IF(I9="",,IF(OR(I9<40,I9="ABS"),I$7,""))&"
"&IF(K9="",,IF(OR(K9<40,K9="ABS"),K$7,""))&" "&IF(M9="",,IF(OR(M9<40,
M9="ABS"),M$7,""))))

Thanks in Anticipation

"Tivpine" wrote:

Hi Everyone,

I need two formula's to get me going, the posible sinerios are either the
student has an Absent (ABS) or he has a pass mark of between 40 - 100. while
draging it down i want it to ignor the cells to the left where there is
either no score or its blank

i.e CELL(A1) 40 1
CELL (A2) Blank ( Ignor if no score)
CELL (A3) 32 0

1. IF(O9<0,"
",IF(O9<"ABS",0,IF(O9<40,0,IF(O9<45,1,IF(O9<50,2,I F(O9<60,3,IF(O9<70,5)))))))


The second formula has three conditions.

Test Exam Total Letter Grade Grade Point Remarks
15 40 55 C 3
PASS
ABS 30 30 F 0
FAIL
12 12 F 0
FAIL
ABS ABS ABS 0 0
ABS

I cant figure out how to go about this?

Thanks in anticipation