average but dont include the duplicate value

mark692

Active Member
Joined
Feb 27, 2015
Messages
321
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 :)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
Use this formula. Change rows range as per your worksheet
=AVERAGE(IF(FREQUENCY(A2:A15,A2:A15),A2:A15))
 
Upvote 0
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.
 
Upvote 0
@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
 
Upvote 0
@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))
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top