# average but dont include the duplicate value

#### mark692

##### Active Member
hi guys do you know how to average a column but dont include the datas duplicate in averaging,, sample below

 1 2 2 3 3 4 4 5 5 6

<tbody>
</tbody>

i want to averagethe column above but only the 1,2,3,4,5,6 not the 1,2,2,3,3,4,4,5,5,6..can you give me the formula thanks

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### pvr928

##### Well-known Member
Hi mark692

A bit out of left field but create a table out of the column (highlight the range and then ALT N T - no headers) and then create a pivot table from the table (place your cursor in the table and then ALT N T - place the pivot table in the same sheet).

In the Pivot Table Field List, place 'Column1' into Row Labels - and voila - the pivot table shows only the unique values.

Then AVERAGE() the range of unique values.

If your original range changes you will have to refresh the pivot table and likely the AVERAGE() formula range.

This can be done dynamically if you wish.

Have fun!

Cheers

pvr928

#### fiberboysa

##### Board Regular
Use this formula. Change rows range as per your worksheet
=AVERAGE(IF(FREQUENCY(A2:A15,A2:A15),A2:A15))

#### pvr928

##### Well-known Member
nice one fiberboysa

If you create a table out of the range (post #2 shows how), if the range changes (and you've used the Table named ranges in the fiberboysa's formula), the result will automatically update.

#### mark692

##### Active Member

@PVR i know that bro but as much as possible i need a formula becouse i will use it with my other formulas in my table :D
@fiber how can i use that? nothings happening :D

##### MrExcel MVP
@PVR i know that bro but as much as possible i need a formula becouse i will use it with my other formulas in my table :D
@fiber how can i use that? nothings happening :D

You need to switch to the generic formula for determining distinct/unique items...

Control+shift+enter, not just enter:

=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(A2:A11),MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1),A2:A11))

#### mark692

thanks

##### MrExcel MVP

You are welcome.

Replies
4
Views
144
Replies
19
Views
505
Replies
0
Views
232
Replies
1
Views
237
Replies
3
Views
296

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,982
Messages
5,834,704
Members
430,310
Latest member
Excelorate3305

### 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