Joel,

I thank you for all of your help. As I said I am a novice to Excel and I
will be working on this in the next few days. Is there any difference that I
am using Office 2003 with the formulas?
Also cells C5 and C15 will remain on the same sheet called "Scores", just
"Changed" will be on its own sheet?
Joel I am sorry if I am a little hard headed this is somewhat new to me. I
am giving this my all on my days off.

Thank You Once Again!
Glenn


"Joel" wrote:

I probably didn't supply enough information for a novice to get everything
working. People usually refere to worksheet functions starting with equal
signs as "formulas" and the SUBs in VBA as macros. Her is additional
information

1) Make sure your worksheet and a sheet named "Changes" (no double quote).
Right click on one of the tabs on the bottom of the worksheet like the one
called SHEET3
1) In C5 don't add anything except the numbers. the worksheet change will
take the numbers that you enter In C5 and automatically put them on the
worksheet called Changes.

2) In C10 enter =COUNTIF(Scores!$B:$B,"Changed")

3) In C15 =SUM(Scores!$A:$A)/C10

4) You need to add the macro to the VBA sheets. Type Alt-F11 from the
worksheet to get to VBA. Go to menu: View- Project Explorer. In the
explorer window look for the sheet name where you are placing the formulas
like Sheet1 or equivalent. Double click the sheet name you will see the
sheet name on the Top of the VBA window like [ Sheet1 (code)] to indicate
your are on the correct sheet. Copy the macro below from: Sub to: End Sub
and paste the code on the [ Sheet1 (code)] sheet. To check that the code is
ok go to VBA menu DEBUG - Compile Project. No errors should occur.


5) Now go back to C5 and start typing number. the number should
automatically get placed on the sheet "Changes.

6) If it is no working you need to change your security level to medium. Go
to Worksheet menu Tools - Macro - Security and Select Medium. Save your
workbook and exit. The re-enter the workbook a pop up menu wil ask you if
you want to enable macros. Select Yes. Go back and retry entering number in
C5 and see if they appear on the sheet Changes.


Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub



"Glenn" wrote:

Joel,

I am a novice at Excel.

When I create Cell C5 I am going to create a Macro.
=COUNTIF(Scores!$B:$B,"Changed")
=COUNTIF(scores!$C:$5,"Changed") ?
I have tried it several ways and haven't had much success. Am I doing
something wrong? Any is deeply appreciated!
--
Thank You
Glenn


"Joel" wrote:

Using the worksheet change macro I put the scores on a worksheet called SCORES.
Column A is the scores and column B get either "Changed" or "Not Changed".
I assumed the 1st entry will always be a change since there was no previous
data.

When you enter a number in cell C5 the worksheet change function
automaticaly gets called.

the number of chaes in C10 is a formula
=COUNTIF(Scores!$B:$B,"Changed")

The results in C15 is
=SUM(Scores!$A:$A)/C10

Sub worksheet_change(ByVal Target As Range)

If Target.Address = "$C$5" Then
With Sheets("Scores")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
If LastRow < 1 Or _
Target < "" Then

LastRow = LastRow + 1
End If

.Range("A" & LastRow) = Target.Value
If LastRow = 1 Then
.Range("B1") = "Changed"
Else
If .Range("A" & LastRow) = _
.Range("A" & (LastRow - 1)) Then

.Range("B" & LastRow) = "Not Changed"
Else
.Range("B" & LastRow) = "Changed"
End If
End If
End With
End If

End Sub


"Glenn" wrote:

I would like to know if this is possible? I would like to create a
spreadsheet that would have three calls. first cell C5 as a daily score cell,
then have another cell C10 only add all the daily score and count how many
times there was a change in the score, Then have cell C15 take the total of
cell C10 divide the amount of daily score and come up with a average. If I am
barking up the wrong software can you point me in the right direction.
--
Thank You
Glenn