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