LinkBack URL
About LinkBacks
Hi
As per simple calculation average is 16 for below numbers.
A 10
B 12
C 10
D 20
A 30
B 14
ButA & B are getting repeated.
Is it possible to calculate it as 96/4= 24
Thanks
Hi
As per simple calculation average is 16 for below numbers.
A 10
B 12
C 10
D 20
A 30
B 14
ButA & B are getting repeated.
Is it possible to calculate it as 96/4= 24
Thanks
This will work
Formula:
=SUM(B1:B6)/SUM(IF(FREQUENCY(MATCH(A1:A6,A1:A6,0),MATCH(A1:A6,A1:A6,0))>0,1))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Another way, entered as an array formula with CTRL+SHIFT+ENTER:
=SUM(B1:B6)/SUM(1/COUNTIF(A1:A6,A1:A6))
Spread the love, add to the Rep
"None of us are as smart as all of us."
Try one of these...
If you only have a "few" rows of data:
=SUM(B1:B6)/SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6))
If you have "many" rows of data, this array formula**:
=SUM(B1:B6)/SUM(IF(FREQUENCY(MATCH(A1:A6,A1:A6,0),ROW(A1:A6)-ROW(A1)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
LOL!
This post "ties in" quite nicely with what we've just been discussing in the other!
I was just about to re-post, advocating the use of the much simpler SUMPRODUCT formula that Tony has just posted...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks