 # Average of Top 5 Values [and some homework]

Share

The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard.

but average of top 5?

I said what any consultant would say. “It is possible

After thinking for a while, I found the solution by nesting LARGE() formula with AVERAGE() formula. Like this:

`=AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))` There is no need to press CTRL+SHIFT+Enter after this formula and it works fine.

You can use similar formula to get Average of bottom 5 values like this:

`=AVERAGE(SMALL(A1:A10,{1,2,3,4,5}))`

Ok, here is an interesting twist to this formula. My formula works fine as long as the list has at least 5 values in it. But, lets say the input range (a1:a10) is dynamic. That means, it can grow or shrink.

Now, how would you modify this formula so that it works even when there are less than 5 values ?

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

I will be presenting at two events in London in April 2020. Come & join me.

Chandoo is an awesome teacher
5/5

– Jason

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### 99 Responses to “Average of Top 5 Values [and some homework]”

1. Martin says:

Thanks for the tip. It's always interesting to see a new way of solving a problem. Here's my shot at your homework.

One way to make this work when the range has less than 5 entries (but at least one) is to dynamically create the second parameter for the LARGE function. Instead of always passing {1,2,3,4,5}, this should only be the case when the range is sufficiently large otherwise a shorter array must be passed. So we can replace this by:
IF(ROWS(A1:A10)>5;{1;2;3;4;5};ROW(A1:A10))

If the range doesn't happen to start on row 1, the row numbering returned by the ROW function must be corrected. So to be correct wherever the range is, the ROW(A1:A10) should be replaced by ROW(A1:A10) - ROW(A1) + 1.

Finally, this needs be an array function (unlike the original).

The whole formula is the following:
=AVERAGE(LARGE(A1:A10,IF(ROWS(A1:A10)>5;{1;2;3;4;5};ROW(A1:A10) - ROW(A1) + 1)))

• gaurang says:

Hi Martin,

Can u explain why u have used – ROW(A1) + 1))) in the above formula.

Regards,
Gaurang

• Chandoo says:

Imagine if your data is in A5:A14, then this formula becomes,

ROW(A5:A14) – ROW(\$A\$5) + 1

2. Martin says:

Sorry for the follow-up. Just noticed that I missed a few things while translating the formula from German to English Excel. Of course, the separator for parameters is "," not ";". The correct formula is:
=AVERAGE(LARGE(A1:A10,IF(ROWS(A1:A10)>5,{1,2,3,4,5},ROW(A1:A10) – ROW(A1) + 1)))

3. Brook says:

G'day Chandoo,

=AVERAGE(IF(RANK(DataRange,DataRange)<=5,DataRange))

where DataRange is your dynamic range.

Of course you can integrate 'averageif' into this if you're using excel 2007 and 2010, but thought this might be friendlier for those using 2003.

Have an awesome weekend! 🙂

• Jagdish Negi says:

Hy Brook,
I am using your formula for TOP 5 values AVERAGE but I am not gettion exact answer. As per your formula answer is 6.8 but correct answer would be 10.4.
Can you help how can I will get exact answer.
For example my data as mentioned below:-
Data range is A1:A10
10
12
11
9
4
7
10
3
2
0
If I am useing this formula =AVERAGE(LARGE(A1:A10,{1,2,3,4,5})) than my AVERAGE is 10.4
If I am useing your formula =AVERAGE(IF(RANK(A1:A10,A1:A10)<=5,A1:A10)) than my AVERAGE is 6.8
Why so?
My main reason is using using your formula is I want TOP 50 Values AVERAGE in the range of A1:A95000. How can I will get the correct answer

• Hui... says:

@Jagdish

You can use either

`=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))`
or
`=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,5)))),AVERAGE(A:A))`
The second formula is an Array formula and must be entered with a Ctrl Shift Enter

In the second formula change the 5 to 50 to get the Average of the Top 50
eg:
`=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,50)))),AVERAGE(A:A))`Ctrl Shift Enter

• Jagdish Negi says:

@ Hui,

This Formula is working fine!!!
Thanx once again.

• Arun says:

Thanks for the solution.

A small improvement in formula given above for Top 5.

The following formula will work in all versions of excel.

=AVERAGE(IF(COUNT(A:A)<=5,A:A,LARGE(A:A,ROW(OFFSET(A1,,,5))))) Ctrl Shift Enter

• Joseph says:

Hi Hui,

I was just looking through your formula and was wondering if you could help explain Row(Offset)?

I tried the formula and it works but I'm not sure how it works. I also tried to lock A1 --> \$A\$1 and the answer came out different but have no idea why. Could you help explain a bit please?

4. Parveen says:

Assumption:
1. Only numeric data in "Values"
2. Maximum number of data rows data are known

In column "B" lets say, put an if logic which checks
if there is a value in "A1",
if yes than put the same value,
else, if no value put 0 in column "B1"
[ Formula: =IF(A1="",0,A1) ]
Paste this formula from "B1" through "B10" (assuming 10 max rows of data)

Now use SUM formula on new data column "B1:B10"
[ Formula: =SUM(LARGE(B1:B10,{1,2,3,4,5})) ]

Also, use COUNT formula to count number of rows on original data column "A1:A10"
[ Formula: =COUNT(A1:A10) ]

Finally we divide SUM with the COUNT to get AVERAGE on a dynamic list
[ Formula: = SUM(LARGE(B1:B10,{1,2,3,4,5}))/COUNT(A1:A10) ]

Is this too long for a solution. 🙂 Looking for more ideas.
BTW ... thanks Chandoo, I have been a regular reader and great work.
I have learnt many things from the blog, keep up the good work.

Cheers, Parveen

5. Dick Kusleika says:

Create MyDynamicRange equal to

=Sheet1!\$A\$1:INDEX(Sheet1!\$A:\$A,COUNTA(Sheet1!\$A:\$A))

Then enter this

=AVERAGE(LARGE(MyDynamicRange,IF(ROWS(MyDynamicRange)>5,{1,2,3,4,5},ROW(MyDynamicRange))))

with Control+Shift+Enter.

6. Steve says:

=AVERAGE(IF(COUNT(A:A)<5,OFFSET(A1,0,0,COUNTA(A:A),1),LARGE(OFFSET(A1,0,0,COUNTA(A:A),1),{1,2,3,4,5})))

or with a named range where "myRange" = "=OFFSET(\$A\$1,0,0,COUNTA(\$A:\$A),1)"

=AVERAGE(IF(COUNT(A:A)<5,myRange,LARGE(myRange,{1,2,3,4,5})))

7. Cyril Z. says:

Hello Chandoo...

Here's my shortest guess :

= AVERAGE( LARGE( A1:A10 , OFFSET( E3:E7, 0,0, MIN(5, COUNTA( A1:A10 ) ), 1 ) ) )

and press CTRL+SHIFT+ENTER

the helper column E3:E7 contains 1, 2, 3, 4 ,5.

8. Cyril Z. says:

By the way I've found a website translating every Excel function into several languages

http://dolf.trieschnigg.nl/excel/excel.html

If anyone figure how to enter a {1,2,3,4,5} array into offset instead of using the helper column...

Cyril

9. Daniel Ferry says:

Chandoo,

Here's how I would do it.
.
=AVERAGE(LARGE(dr,ROW(OFFSET(dr,,,MIN(5,ROWS(dr))))))
.
with Control+Shift+Enter, and "dr" is the dynamic range.

No IF() functions needed, which Dick may be interested in knowing 🙂

Regards,

Daniel Ferry
excelhero.com

10. Abbas Sura says:

I used the following formula and it worked fine

=IF(COUNT(B2:B14)<5,AVERAGE(B2:B14),AVERAGE(LARGE(B2:B14,{1,2,3,4,5})))

Some interesting answers in the comments above. A number of formulas are being used that I have never used before....very interesting. Could someone explain the meaning of the use of {} in the LARGE formula. This is something new to me. Thanks.

11. Cecilia says:

I would use {=AVERAGE(IF(LEN(A1:A10)>0,LARGE(A1:A10,{1,2,3,4,5})))} (array entered!)
Have a nice weekend!
CC

12. Daniel Ferry says:

@Abbas Sura -
{} is how you specify an array of constants. Some of Excel's functions, such as LARGE, SMALL, AND, and OR (and some others), can work with an array of values without requiring the entire formula to be Array-Entered.
.
In Chandoo's LARGE formula for the blog entry, the {1,2,3,4,5} array compels the formula to pull out the first largest value in the range, then the second, then the third, then the fourth, and then the fifth, and to average them together for the result. The problem is that if you give the LARGE function this directive and there is no fifth largest value (because there is only four values in your range), the LARGE function will produce and error.
.
Using arrays in formulas can be very potent, as is taking it one step further and making the entire formula an "Array Formula", as many of the examples above demonstrate, including mine.
.
@CC -
Your formula does not work for me.

.
Regards,
Daniel Ferry
excelhero.com

13. Mike says:

=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))

14. dscg says:

A non-volatile, non-array (in that it doesn't require CSE) dynamic range option:

=SUMPRODUCT(LARGE(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),ROW(A1:INDEX(A:A,(MIN(5,MATCH(9.99999999999999E+307,A:A)))))))/MIN(5,MATCH(9.99999999999999E+307,A:A))

Can be consolidated further by naming last row & dynamic range

lr=MATCH(9.99999999999999E+307,\$A:\$A)
dr =\$A\$1:INDEX(\$A:\$A,lr)

To:

=SUMPRODUCT(LARGE(dr,ROW(A1:INDEX(A:A,(MIN(5,lr))))))/MIN(5,lr)

15. Shel Price says:

I created a named range "Data" and used offset (=OFFSET(Sheet1!\$A\$2,0,0,COUNTA(Sheet1!\$A\$2:\$A\$200),1)). I then added the name "Data" into the original formula in place of the array (=AVERAGE(LARGE(Data,{1,2,3,4,5}))).

16. Shel Price says:

I created a named range "Data" and used "=OFFSET(Sheet1!\$A\$2,0,0,COUNTA(Sheet1!\$A\$2:\$A\$200),1)" in the refers to field. I then replaced the array in the original formulas with "Data". "=AVERAGE(SMALL(Data,{1,2,3,4,5}))"

17. Daniel Ferry says:

@Shel Price -
Your formula will not work when there is less than five entries in the dynamic range. Chandoo's objective was to find a formula that would work for five or more (like his does and like yours does), but ALSO for less than five entries.

Regards,

Daniel Ferry
excelhero.com

18. Mike says:

=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))

That provides for less than 5, and the top 5 no matter where they are in the column.

19. Shel Price says:

Yeah, I saw that after I posted. Tested for more rather than less.

20. Alan says:

My dodgy tuppence worth (CSE):
IFERROR(AVERAGE(LARGE(\$A:\$A,MID(ROW(INDIRECT("1:"&MIN(COUNT(\$A:\$A),5))),1,1))),"")

(Error in case there are no values).

21. Elias says:

MyRange equal to.
=\$A\$1:INDEX(\$A:\$A,MATCH(9.99E+307,\$A:\$A))

Formula to get the average
=CHOOSE((COUNT(MyRange)>5)+1,AVERAGE(MyRange),AVERAGE(LARGE(MyRange,{1,2,3,4,5})))

Regards

22. Kanti says:

Dear Chandoo,

With all due apologies to my Hero Daniel Ferry here is a solution from a lesser mortal

With a dynamic range named Data

=IF(COUNT(Data)<5,SUM(Data)/COUNT(Data),AVERAGE(LARGE(Data,{1,2,3,4,5})))

Daniel Ferry, you are not only a hero but an Excel Picasso as well, I really enjoy your Excel Hero blog. But a question please IF i may.

When and IF ever have you used IF. All the solutions that you propose without IF are truly elegant.

Chandoo Thanks for a most inspiring web site

Cheer
Kanti

23. Daniel Ferry says:

@Kanti -
Here is a quote from a comment I left one of my readers recently regarding the IF() function:
.
"I am not philosophically opposed to the IF() function. It's just that I feel it is used too much and often when there are better choices. However, when it comes to throwing an #N/A to force data points to hide themselves on a chart, I think that IF() is an excellent choice. For some reason, Excel is able to conditionally (with the IF() function) output an #N/A at lightning speed."
.
This comment was from my celtic muse blog entry:
http://www.excelhero.com/blog/2010/05/excel-animated-chart-2.html

.
Kanti, I've found over the years that if I force myself to look for solutions that do not rely on the IF() function, two things happen:
1.) I discover angles of attack on a problem that would never have occurred to me otherwise and hence interesting solutions that are sometimes elegant and sometimes not.
2.) The exercise makes me a better formula crafter in all situations.
.
Regards,
Daniel Ferry
excelhero.com

24. Eliel Lew says:

Hi guys,

Hope that this solution can help.

The formula I use is just a slight modification of the formula shown in the article.

Let data_range = cell range that contains the numbers

Key in (or copy and paste) the formula below in a cell
=Average(large(data_range,row(indirect("1:"&min(5,count(data_range))))))
and press control+shift+enter

In this case, if the range contains more than 5 figures, only the average of largest 5 figures will return; if less than 5 figures, the average of those figures will return.

Thanks and best regards,
Eliel Lew

25. jager says:

=IFERROR(AVERAGE(LARGE(--DynamicRange--;{1;2;3;4;5}));AVERAGE(--DynamicRange--))

26. jager says:

if the dynamic range is named NamedRange then:

=IFERROR(AVERAGE(LARGE(NamedRange ;{1;2;3;4;5}));AVERAGE(NamedRange))

27. Oscar says:

David Ferry,

OFFSET is a volatile function and is always recalculated at each recalculation. This can slow down large excel sheets considerably. Should volatile functions be avoided??

28. Daniel Ferry says:

@Oscar -
While OFFSET is volatile, it is extremely fast, and with it some amazingly creative solutions can be found. I routinely use it on large projects with many thousands of formulas with no problem. The advice to look for nonvolatile solutions is sound, but it should not be absolute. ROW() is also listed as volatile, but it has NEVER adversely affected one of my projects.

With OFFSET and ROW in particular, it is well worth exploring their use. Every circumstance is unique, but I would never shy away from either of these.

By the way, my name is Daniel (but I do have a brother named David - though I don't think he knows what Excel is ;))

Regards,

Daniel Ferry
excelhero.com

29. Chandoo says:

Excellent contributions by everyone. Thank you so much for the creativity and ideas you have shown here.

One observation in general.

When the list has less than 5 values, Average of top 5 is nothing but average. So the simplest way to write this formula can be,

=AVERAGE(IF(COUNT(A1:A10)<6,A1:A10,LARGE(A1:A10,{1,2,3,4,5})))

But then again, you have shown so much more variety in your answers. Thank you. 🙂

• Bryguy says:

Just what I was looking for, thanks!

30. Jie says:

Hi

This is my solution

=IF(COUNTA(A:A)<5,AVERAGE(A:A),AVERAGE(LARGE(OFFSET(A1,0,0,COUNTA(A:A),1),{1,2,3,4,5})))

Thanks for the amazing site.

31. sam says:

Row() is not volatile. It is wrongly listed as volatile

32. Daniel Ferry says:

@Chandoo -
That may be the simplest, but I think Brook's formula (comment #3) is the best.

=AVERAGE(IF(RANK(dr,dr)<=5,dr))

where "dr" is the data range.
.
Elegant. I even liked how Brook used the IF() function. Cudos Brook!
.
Regards,
Daniel Ferry
excelhero.com

• Alex M says:

I agree that this is a great formula, but after testing it, there is one flaw in that if values tie for 5th place or lower, it will skew the average. This, as opposed to the large formula which will explicitly pull back 5 values.

Great use of a function I've never seen though. Really elegant formula.

• saleem says:

• Hui... says:

@Tahir or Saleem ?

Two different names and same question, what goes?

You need to setup a column of values for each Range
ie
A2 =1/3600/24
A3 = 2*A\$2
A4 = (Row()-1)*A\$2
etc

Then use an =Averageifs function to retrieve your average scores

If you post the question at the Chandoo.org Forums
http://forum.chandoo.org/

Please attach a file and you will get a more targeted response

33. [...] July 1, 2010 at 5:47 PM | Posted in General | Leave a Comment Chandoo has a quick tip on how to calculate the average of only the top or bottom few values in a [...]

34. aldus says:

@Brook - comment #3
Can you explain, how to write the formula AVERAGEIF (using Excel 2007)?

Chandoo, Thanks for your very inspiring excel site.
Thanks and best regards,
aldus

35. [...] Average of top 5 values [...]

36. [...] Average of Top 5 Values – Array Formula in Excel [...]

37. jovie says:

thanks for the tip it made me perfect in our exam

thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!................,,,,<<<<<<<<

38. archana says:

CHANDOO I TRIED THIS FORMULA BUT AM NOT GETTING THE ANSWER I MENTIONED
=AVERAGE{LARGE{A1:A10,(1,2,3,4,5)}} .AM NOT GETTING THE ANSWER.

39. Adi says:

adi is the range for whole column.
you can select range by selecting the whole column A1 to A65536
using ctrl up and down and after selection plz mention adi in
Its working completely fine for me

let me know if anyone needs help on this 🙂

40. Adi says:

I hope chandoo will be proud of me..

41. Oli says:

Hi - being a bit lazy by not defining a dynamic range name - but this formula does the job (i think) =AVERAGE(LARGE(A:A,ROW(INDIRECT("1:"&IF(COUNT(A:A)

42. Jagdish Negi says:

Dear All,
This is the one valued Trick on excel.
I have a question where I want calculate AVERAGE with with TOP 50 values where my data range is A1:A15000.
How can I will calculate I don't want to type mannually 1 to 50. Inside of LARGE Function values. Is there any way how can I will caluculate?
=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))

• Hui... says:

@Jagdish
Try the following Array formula
`=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,50)))),AVERAGE(A:A))`
It has to be entered with Ctrl Shift Enter
Change the Value 5 to 50 to suit

43. Robert says:

This is the formula I made up which includes two added components:
1. average based on a condition
2. weighted average if over 4 values.

```=CEILING(IF(COUNTIF(\$C\$10:\$C\$60,\$B66)>4,AVERAGE(SMALL(IF(\$C\$10:\$C\$60=\$B66,\$R\$10:\$R\$60),\$W\$5) * \$W\$6, SMALL(IF(\$C\$10:\$C\$60=\$B66,\$R\$10:\$R\$60), \$X\$5) * \$X\$6, SMALL(IF(\$C\$10:\$C\$60=\$B66,\$R\$10:\$R\$60), \$Y\$5) * \$Y\$6, SMALL(IF(\$C\$10:\$C\$60=\$B66,\$R\$10:\$R\$60), \$Z\$5) * \$Z\$6) * 4, AVERAGE(IF(\$C\$10:\$C\$60=\$B66, \$R\$10:\$R\$60))), 5) ```
Now if someone could improve my formula so that the weighted averages worked with 4 or less entries, I would be impressed (and grateful)!

44. bill says:

A few questions... what about calculating the standard deviation of the top five... is it just a simple? Also, what happens if there are three items tied for fifth place? One three items tied for second place?

45. Michael Lev says:

What if I wanted not the top 5, but rather the top 50% of the data?

46. Amy Frain says:

Hi
I would like to take the top 5 average for a number of scores in specific columns. What would be the formula say if the columns were F8, I8, M8, O8, R8,T8, W8 for example?

Thanks

• Hui... says:

=AVERAGE(LARGE((\$F\$8,\$I\$8,\$M\$8,\$O\$8,\$R\$8,\$T\$8,\$W\$8),ROW(OFFSET(\$A\$1,,,5,1)))) Ctrl+Shift+Enter

or
=AVERAGE(LARGE(F8:W8,ROW(OFFSET(\$A\$1,,,5,1)))) Ctrl+Shift+Enter

or
=AVERAGE(LARGE(myRng,ROW(OFFSET(\$A\$1,,,5,1)))) Ctrl+Shift+Enter
Where myRng is a Named Formula
myRng =Sheet1!\$F\$8,Sheet1!\$I\$8,Sheet1!\$M\$8,Sheet1!\$O\$8,Sheet1!\$R\$8,Sheet1!\$T\$8,Sheet1!\$W\$8

47. Camahof says:

I am a college student and I wanted to create an excel sheet so I could easily input my quiz grades and figure out my quiz average. My professor drops our lowest 2 quizes out of 7 but before I had all of my quiz grades I still wanted to know what my average was so I used the above formula with a slight modification so that it will average regardless of the number of values i currently have entered and will give me my average of only my top 5 scores. This is my modified formula:

=IF(COUNTA(B2:B11)>4,AVERAGE(LARGE(B2:B11,{1,2,3,4,5})),AVERAGE(B2:B11))

48. sanjeev says:

I simply used

=AVERAGE(LARGE(arrr,1),LARGE(arrr,2),LARGE(arrr,3),LARGE(arrr,4),LARGE(arrr,5))

where arrr= array

• sanjeev says:

=AVERAGE(LARGE(arrr,{1,2,3,4,5}))

49. Yassin says:

i want to count the numbers that means i have 40 to 100 numbers i want to count how many numbers are there between 50 to 59 manually i can count 10 or may be 6 numbers so to calculate using excel what shall i do

• Hui... says:

@Yassin
Try using the Countifs() function

It will be something like: =Countifs(A1:A100, " GT 50", A1:A100, " LT 59")
Change the range and GT LT etc as appropriate
GT = Greater than
LT = Less Than

50. MarkS says:

So what would this look like if you wanted to calculate the average of the highest 5% (or 10%) of the numbers. I have a data range that has 4000 values and i want the average to the top 200 (or 400) items?

Any thoughts??

• Arun says:

Assuming your values are in column A, the formula can be modified as

=AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,CEILING(COUNT(A:A)*5%,1))))) Ctrl Shift Enter

for average of top 5% Values.

51. Craig says:

Greetings Im trying to create a formula that measures the avg high and low of several different arguments for instance

=AVERAGE(LARGE(E3:E14,E19:E30,E51:E62,E35:E46,E67:E78,E97:E108,E126:E137,E156:E167,E185:E196,E214:E225,E230:E241,E246:E257,{1,2,3})) is there anyway for this to work.

52. Nadav says:

Hi Chandoo,

That was a great tip man, thanks a lot for posting the formula!

53. Sandeep says:

Hi Chandoo,Iran hui,

hi am working one similar kind of data,

Data:

Each line of Transaction has
Date, Hour, Job processed, Average processing(dynamic), CPU Util
10-04-2016,04:01:00PM,3700,5500,90%

Average processing value is dynamic that i should calculate when the CPU util is more than or equal to 90% and i should only consider last 4 Job procesed transactions + current tranaction with CPU util greater or equal to 90 %. Please suggest some way to do this in excel.

54. Tahir says:

Hi everyone
I have done an experiment and sensor gives me reading after every 1ms (millisecond). There are thousands of row ( 3 – 993434) I want to do something in excel to reduce the number of rows e:g average) or through some calculation in a new colum so that I will be able to get the values for every 1sec
Means it will give me 1 value for one second and 60 values for 1mint

• Hui... says:

@Tahir or Saleem ?

Two different names and same question, what goes?

You need to setup a column of values for each Range
ie
A2 =1/3600/24
A3 =(Row()-1)*A\$2
A4 =(Row()-1)*A\$2
etc

Then use an =Averageifs function to retrieve your average scores

If you post the question at the Chandoo.org Forums
http://forum.chandoo.org/

Please attach a file and you will get a more targeted response

55. saleem says:

Hi everyone
I have done an experiment and sensor gives me reading after every 1ms (millisecond). There are thousands of row ( 3 – 993434) I want to do something in excel to reduce the number of rows e:g average) or through some calculation in a new colum so that I will be able to get the values for every 1sec
Means it will give me 1 value for one second and 60 values for 1mint

56. saleem says:

For example I have a data set in colum A with 1000000 number of rows I am trying to reduce the data set by averaging every 1500 rows in this data frame, How can i do that. I am very new to Excel

• Hui... says:

@Saleem

Same Question, now different question, what is going on ?

You need to setup a column of values for each Range
ie
A2 =1
A3 =(Row()-2)*1500
A4 =(Row()-2)*1500
etc

Then use an =Average with an offset to retrieve the various ranges defined by the row numbers above

If you post the question at the Chandoo.org Forums
http://forum.chandoo.org/

Please attach a file and you will get a more targeted response

57. saleem says:

Hi Hui thankx, I am sorry for two Name ist my first and last Name i haved tried to post the question and have not found the question and i again post it with my second Name

58. saleem says:

hi hui can i have you time on s kype to get the expert advice

• Hui... says:

@Saleem

I don't do Excel Consulting

Every time I have asked you to:

"If you post the question at the Chandoo.org Forums
http://forum.chandoo.org/

Please attach a file and you will get a more targeted response"

59. Ufoo says:

Thanks all for ingenious solutions!

60. Steven says:

Can anyone solve this,

If l am having an average value of 59% for 5 Days and want to bring it down on 50% on 6th Day for which the value should be 5% of 6th day but l have to do this on manual basis but require a formula to do it so e.g

1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul AVG %
59% 59% 59% 59% 59% 5% 50%

• Chandoo says:

Thanks Steven for your comment and welcome to Chandoo.org

Assuming your original data is in A1:E1 and target average is in A2, write below formula.
=A2*(count(A1:E1)+1) - sum(A1:E1)

• Steven says:

Thanks Chandoo: but guess what l am still having the same answer and the answer is 50%

• Steven says:

Sorry it's 59% instated of having 5%

• Chandoo says:

Not sure I understand the problem. Adjust the A2 value to whatever target average you want and the formula will give the answer.

• Hui... says:

@Steven
Looking at your data above and assuming the dates are in Row 1, Chandoo's formula should be:
F2 should be: =G2*(count(A2:E2)+1) - sum(A2:E2)
where G2 has the Target Value 50%

61. Steven says:

Thanks Chandoo: Your a Jew bro... it help after understanding.....

62. Muhammad Raja says:

Dear all i want to know that the average of top 100 values in colum A starting from A2 to A101 and put the value in B2: after it is finished with avg of top 100 values then it moves automatically to next avg of A102 to A 202 and put the value in B2

is it possible that i can do it in excel

• Arun N says:

Let me rephrase the question

Average of A2:A101 in B2
Average of A102:A201 in B3
... and so on.
i.e., average of 100 numbers at a time in consecutive rows.

The formula in B2 is :

=IF((ROW()-2)<CEILING(COUNT(A:A)/100,1),AVERAGE(OFFSET(\$A\$2,100*(ROW()-2),0,100)),"")

the same can be copied and pasted in subsequent rows till the point the result is not blank.

• Shane says:

Hi @Arun N. This falls right in line with something I'm trying to accomplish. Rather than subsequent rows, is it possible to do this by % ? For example, I want to average the first largest 25% within the list of values. Then the next 25% of the largest. And so on.

• Hui... says:

@Shane

http://chandoo.org/forum/
Please attach a sample file to get a more targeted response

• Shane says:

@Hui Ok thanks. I have done so.

63. Muhammad Raja says:

I want to attach one file can i do it ?

64. PINOY-exceler says:

=IFERROR(SUMIF(values,">="&LARGE(values,5))/COUNTIF(values,">="&LARGE(values,5)),AVERAGE(values))

Note: I defined the range as "values" for dynamicity.

65. Ufoo says:

=IF(COUNT(A1:A10)>5,AVERAGE(LARGE(A1:A10,{1,2,3,4,5})),AVERAGE(LARGE(A1:INDEX(A1:A10,COUNT(A1:A10)),ROW(A1:INDEX(A1:A10,COUNT(A1:A10))))))

66. noticias sobre deportes says:

¡Muy practico! Contundentes criterios. Manten este nivel es un articulo genial. Tengo que leer màs blogs como este.

Saludos

67. Mike says:

=IF(COUNT(A1:A10)<5,AVERAGE(A1:A10),AVERAGE(LARGE(A1:A10,{1,2,3,4,5})))

If there are fewer than five cells containing values, excel will simply average all the values. Remember that excel automatically ignores blank cells when calculating averages within a given range.

68. balwant says:

Please Tell me, if i’m fill the value in First column & Row the
reflectet to next column & Row. And if First value in addition &
Subtraction then reflected to next column also.

A B
12+5 1200 then ok
if (1200-100) refleted (1100)
both are one column reflected

69. chuck says:

=averageif(A:A,">="&large(A:A,5))

This says compute the average of the cells in column A if they are >= the 5th largest value in the column

70. Yves S says:

=AVERAGE(IFERROR(LARGE(A1:A3,{1,2,3,4,5}),""))

71. Yves S says:

or for whole column:
=AVERAGE(IFERROR(LARGE(A:A,{1,2,3,4,5}),""))

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.