I am a teacher who gives students points for attendance. If they are gone,
they have to use sick leave, bereavement leave, etc. to avoid having to make
up an article summary. Once they have used all of their leave, they must do
an article summary to get their attendance points for a missed class period.

I want to set up a spreadsheet to figure their attendance points
automatically. I have used the following two formulas:

=IF(COUNTIF($G$2:$FZ$2,"P")=0,1,IF(2-COUNTIF($G$2:$FZ$2,"P")0,2-COUNTIF($G$2:$FZ$2,"P"),0))

=IF(AND($B$2=0,G2="P"),0,IF(AND($C$2=0,G2="S"),0,I F(AND($D$2=0,G2="F"),0,IF(AND($E$2=0,G2="A"),0,IF( AND($F$2=0,G2="E"),0,LOOKUP(G2,{"P","S","F","A","E ","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2 ,1,0,1,0,3}))))))

I want to have a section of the worksheet that is devoted to tracking their
remaining balances for their leave days and a section of the worksheet that
is devoted to awarding daily attendance points based upon whether they have
any leave left or not.

I finally figured out the formulas...or at least I thought I did. For
example, I want Excel to give the student 3 points for the day if I enter "P"
in cell G2 as long as they still have 1 personal day left to use. Once they
use their one given personal day, I set up the formula to give them 0 points
for every time "P" is entered. My problem is that when I enter a "P" the
second time (and Excel gives a 0 because the student is out of personal
lave), it changes the previous P's 3 points to 0 because their "balance" for
personal leave is now zero.

It all boils down to this...I want to be able to copy the formula to many
cells and have the formula work without changing previous calculations.

Any suggestions would be greatly appreaciated!!!!! (Thanks for reading my
LONG problem!!)

Kate

kate_suzanne wrote:
I am a teacher who gives students points for attendance. If they are
gone, they have to use sick leave, bereavement leave, etc. to avoid
having to make up an article summary. Once they have used all of
their leave, they must do an article summary to get their attendance
points for a missed class period.

[cut]

Any suggestions would be greatly appreaciated!!!!! (Thanks for
reading my LONG problem!!)

Kate

Hi Kate,

I think it would be better if you could upload an example file (describing
what is actual and what are your desiderata) to www.savefile.com or
www.rapidshare.de, because I think I have understood quite nothing, but I
think it could be very interesting... (Sorry, but maybe it depends on the
late hour: here is 2:58 am...).

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Here is a link for the file I am working with to help articulate my problem...

http://rapidshare.de/files/30705775/...e_Balances.xls

"kate_suzanne" wrote:

I am a teacher who gives students points for attendance. If they are gone,
they have to use sick leave, bereavement leave, etc. to avoid having to make
up an article summary. Once they have used all of their leave, they must do
an article summary to get their attendance points for a missed class period.

I want to set up a spreadsheet to figure their attendance points
automatically. I have used the following two formulas:

=IF(COUNTIF($G$2:$FZ$2,"P")=0,1,IF(2-COUNTIF($G$2:$FZ$2,"P")0,2-COUNTIF($G$2:$FZ$2,"P"),0))

=IF(AND($B$2=0,G2="P"),0,IF(AND($C$2=0,G2="S"),0,I F(AND($D$2=0,G2="F"),0,IF(AND($E$2=0,G2="A"),0,IF( AND($F$2=0,G2="E"),0,LOOKUP(G2,{"P","S","F","A","E ","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2 ,1,0,1,0,3}))))))

I want to have a section of the worksheet that is devoted to tracking their
remaining balances for their leave days and a section of the worksheet that
is devoted to awarding daily attendance points based upon whether they have
any leave left or not.

I finally figured out the formulas...or at least I thought I did. For
example, I want Excel to give the student 3 points for the day if I enter "P"
in cell G2 as long as they still have 1 personal day left to use. Once they
use their one given personal day, I set up the formula to give them 0 points
for every time "P" is entered. My problem is that when I enter a "P" the
second time (and Excel gives a 0 because the student is out of personal
lave), it changes the previous P's 3 points to 0 because their "balance" for
personal leave is now zero.

It all boils down to this...I want to be able to copy the formula to many
cells and have the formula work without changing previous calculations.

Any suggestions would be greatly appreaciated!!!!! (Thanks for reading my
LONG problem!!)

Kate

I have placed a link to the file as a reply to my original message...here it
is for you...

http://rapidshare.de/files/30705775/...e_Balances.xls

"Franz Verga" wrote:

kate_suzanne wrote:
I am a teacher who gives students points for attendance. If they are
gone, they have to use sick leave, bereavement leave, etc. to avoid
having to make up an article summary. Once they have used all of
their leave, they must do an article summary to get their attendance
points for a missed class period.

[cut]

Any suggestions would be greatly appreaciated!!!!! (Thanks for
reading my LONG problem!!)

Kate

Hi Kate,

I think it would be better if you could upload an example file (describing
what is actual and what are your desiderata) to www.savefile.com or
www.rapidshare.de, because I think I have understood quite nothing, but I
think it could be very interesting... (Sorry, but maybe it depends on the
late hour: here is 2:58 am...).

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

kate_suzanne wrote:
Here is a link for the file I am working with to help articulate my
problem...

http://rapidshare.de/files/30705775/...e_Balances.xls

"kate_suzanne" wrote:

I am a teacher who gives students points for attendance. If they
are gone, they have to use sick leave, bereavement leave, etc. to
avoid having to make up an article summary. Once they have used all
of their leave, they must do an article summary to get their
attendance points for a missed class period.

I want to set up a spreadsheet to figure their attendance points
automatically. I have used the following two formulas:

=IF(COUNTIF($G$2:$FZ$2,"P")=0,1,IF(2-COUNTIF($G$2:$FZ$2,"P")0,2-COUNTIF($G$2:$FZ$2,"P"),0))

=IF(AND($B$2=0,G2="P"),0,IF(AND($C$2=0,G2="S"),0,I F(AND($D$2=0,G2="F"),0,IF(AND($E$2=0,G2="A"),0,IF( AND($F$2=0,G2="E"),0,LOOKUP(G2,{"P","S","F","A","E ","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2 ,1,0,1,0,3}))))))

I want to have a section of the worksheet that is devoted to
tracking their remaining balances for their leave days and a section
of the worksheet that is devoted to awarding daily attendance points
based upon whether they have any leave left or not.

I finally figured out the formulas...or at least I thought I did.
For example, I want Excel to give the student 3 points for the day
if I enter "P" in cell G2 as long as they still have 1 personal day
left to use. Once they use their one given personal day, I set up
the formula to give them 0 points for every time "P" is entered. My
problem is that when I enter a "P" the second time (and Excel gives
a 0 because the student is out of personal lave), it changes the
previous P's 3 points to 0 because their "balance" for personal
leave is now zero.

It all boils down to this...I want to be able to copy the formula to
many cells and have the formula work without changing previous
calculations.

Any suggestions would be greatly appreaciated!!!!! (Thanks for
reading my LONG problem!!)

Kate

Hi Kate,

I downloaded your file and now I'm starting to understand... (I hope...
:-) )

I think your worksheet needs a little bit of reengineering... :-)

As a starting point, I think you should tell us how many P, S, F, A and E
eache student can have and on which base (i.e. 1 P, 3 S and so on per week
or per month) and how many points will you give for each letter "saved"...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Here are the "allotments" for each category:

P=1
S=5
F=3
A=5
E=1

Their totals are per semester...I would clear the spreadsheet of names and
numbers each semester but leave the formulas in place to use again second
semester.

Let me know of anything else I can answer.

Thank you for taking the time to help me out!! I really appreciate it!!!!

"Franz Verga" wrote:

kate_suzanne wrote:
Here is a link for the file I am working with to help articulate my
problem...

http://rapidshare.de/files/30705775/...e_Balances.xls

"kate_suzanne" wrote:

I am a teacher who gives students points for attendance. If they
are gone, they have to use sick leave, bereavement leave, etc. to
avoid having to make up an article summary. Once they have used all
of their leave, they must do an article summary to get their
attendance points for a missed class period.

I want to set up a spreadsheet to figure their attendance points
automatically. I have used the following two formulas:

=IF(COUNTIF($G$2:$FZ$2,"P")=0,1,IF(2-COUNTIF($G$2:$FZ$2,"P")0,2-COUNTIF($G$2:$FZ$2,"P"),0))

=IF(AND($B$2=0,G2="P"),0,IF(AND($C$2=0,G2="S"),0,I F(AND($D$2=0,G2="F"),0,IF(AND($E$2=0,G2="A"),0,IF( AND($F$2=0,G2="E"),0,LOOKUP(G2,{"P","S","F","A","E ","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2 ,1,0,1,0,3}))))))

I want to have a section of the worksheet that is devoted to
tracking their remaining balances for their leave days and a section
of the worksheet that is devoted to awarding daily attendance points
based upon whether they have any leave left or not.

I finally figured out the formulas...or at least I thought I did.
For example, I want Excel to give the student 3 points for the day
if I enter "P" in cell G2 as long as they still have 1 personal day
left to use. Once they use their one given personal day, I set up
the formula to give them 0 points for every time "P" is entered. My
problem is that when I enter a "P" the second time (and Excel gives
a 0 because the student is out of personal lave), it changes the
previous P's 3 points to 0 because their "balance" for personal
leave is now zero.

It all boils down to this...I want to be able to copy the formula to
many cells and have the formula work without changing previous
calculations.

Any suggestions would be greatly appreaciated!!!!! (Thanks for
reading my LONG problem!!)

Kate

Hi Kate,

I downloaded your file and now I'm starting to understand... (I hope...
:-) )

I think your worksheet needs a little bit of reengineering... :-)

As a starting point, I think you should tell us how many P, S, F, A and E
eache student can have and on which base (i.e. 1 P, 3 S and so on per week
or per month) and how many points will you give for each letter "saved"...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

kate_suzanne wrote:
Here are the "allotments" for each category:

P=1
S=5
F=3
A=5
E=1

Their totals are per semester...I would clear the spreadsheet of
names and numbers each semester but leave the formulas in place to
use again second semester.

Let me know of anything else I can answer.


Hi need an explanation about the formula in row 3, i.e.:

=IF(AND($B$2=0,G2="P"),0,IF(AND($C$2=0,G2="S"),0,I F(AND($D$2=0,G2="F"),0,IF(AND($E$2=0,G2="A"),0,IF( AND($F$2=0,G2="E"),0,LOOKUP(G2,{"P","S","F","A","E ","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2 ,1,0,1,0,3}))))))


try to describe what does it do, so I can understand it, expecially the
lookup... Are those the points you are giving?

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy