average but dont include the duplicate value

mark692

Active Member
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows
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 :)
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
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
Joined
Apr 25, 2012
Messages
90
Office Version
  1. 2019
Platform
  1. Windows
Use this formula. Change rows range as per your worksheet
=AVERAGE(IF(FREQUENCY(A2:A15,A2:A15),A2:A15))
 

pvr928

Well-known Member
Joined
Oct 21, 2002
Messages
790
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
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
@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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top