percentages...

[Dave]

If I want to make a spreadsheet with the number of times a player has turned
out for his team in a season, what is the formula to put in the cell. For
example, John Philips has made 15 appearances out of a possible 22. I do not
want to put in the formula sum=15/22*100 as I wish to simply tupe 15 in the
cell and get it to change the cells contents to 68%. I know how to get it to
put this figure into another cell, but how do I get it to do it in the same
cell as the info I type. Is this possible?
Secondly, if I have the said mr.Philip' percentages for the last say eight
years, how can I get it to give an accurate average when the player didn't
play for the team for 3 years because of other commitments. Again to example
this, the player made say 62%,23%,0,0,0,78%,88%,100%. I only want an average
for his playing years, but don't want to divide by 5 as I wish to drag this
formula down the whole team, some of whom have played more years and some
less. Is there a formula which will ignore the zeros?

Thanks for help in advance....

Dave

 

[Peo Sjoblom]

The average first

=AVERAGE(IF(A2:A9<>0,A2:A9))

entered with ctrl + shift & enter will not include zeros

The first you would need an event macro to do. If it is always 22 you can
use

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("B2"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value / 22
Application.EnableEvents = True
End Sub

right click the sheet tab and select view code, paste the above and format
B2 (change to whatever the cell you are using) as percentage

 

[Karin S]

Dave, I can't help you on the first question, but I think you could use the
following formula for your second question, to find the average while
disregarding the zero values:

=SUM(C4:J4)/COUNTIF(C4:J4,"<>0")

assuming the percentages are in C4:J4.