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