Averages Question

[NJLOU]

Hi there... new to the board and I have a question about an average function question in Excel.

Is it possible to average numbers in a group of cells but only to treat those values in the cells as a value of 1?

I run an online racing league and keep stat sheets for the players. An example of a stat that I would like an average for is lap leaders. However, I want an average of having at least one lap leader per race, not for the total amount.

So an example would be cells A1:A5 The values would be 3,5,0,1,0

I want to calculate the average as 1,1,0,1,0

Is it possible to do this? Any help would be appreciated, thanks!

 

[Andrew Poulsom]

Try:

=COUNTIF(A1:A5,">0")/COUNT(A1:A5)

 

[BrianM]

Probably a better way, but this should work:
=COUNTIF(A1:A5,">0")/COUNTA(A1:A5)

 

[Seti]

Another option, but it is a C-S-E formula

=AVERAGE(SIGN(A1:A5))

 

[NJLOU]

Great, I just tried it and it works fine... thanks, you saved me a lot of extra work