# Averages Question

#### NJLOU

##### New Member
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!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try:

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

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

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

=AVERAGE(SIGN(A1:A5))

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

Replies
5
Views
484
Replies
2
Views
467
Replies
5
Views
195
Replies
11
Views
364
Replies
5
Views
557

1,203,139
Messages
6,053,719
Members
444,681
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back