Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

binned average of large set of data

Posted on 2016-10-06
10
24 Views
Last Modified: 2016-11-11
i want to do average of large set of data. For example 1,1,1,1,2,2,2,2,2,3,3,4,4,4,4,5,5,6,7,7,7,7,7,7

i need average of 1,1,1,1 and than 2,2,2,2,2 and than 33 and than 4444 and than 55 and than 6 and than 777777. is it possible ? can any one help me in this ?

=IF(MOD(ROW(),20)=1,AVERAGE(OFFSET(K1,,,20)),"") works but only same number of bin every time. here,  bin is variable ...looking answer from some expert...
average.xlsx
0
Comment
Question by:Nikhil Sharma
10 Comments
 

Author Comment

by:Nikhil Sharma
ID: 41832601
i want to do average of large set of data. For example 1,1,1,1,2,2,2,2,2,3,3,4,4,4,4,5,5,6,7,7,7,7,7,7

i need average of 1,1,1,1 and than 2,2,2,2,2 and than 33 and than 4444 and than 55 and than 6 and than 777777. is it possible ? can any one help me in this ?

=IF(MOD(ROW(),20)=1,AVERAGE(OFFSET(K1,,,20)),"") works but only same number of bin every time.  bin is variable ...looking answer from some expert... please find the attched file
average.xlsx
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 41832619
Why not simply use a PivotTable?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41833223
Average of 1,1,1,1 = 1
Average of 2,2,2,2,2 = 2
Average of 3,3 = 3

What are you trying to achieve??
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Nikhil Sharma
ID: 41833525
i have acquired the data from experiment. The data is in excel form. I have attached the sheet with question. This contains about 2000 data points. Like this , i have 40 sheets. I was looking for some formula in excel which can solve this.

this formula
=IF(MOD(ROW(),20)=1,AVERAGE(OFFSET(K1,,,20)),"")
gives me average of 20 data sets and i have to pull the formula up to 2000 data points. However,in my case the interval is not fixed. It depends on repetition of number.

please see the attached file.
average--2-.xlsx
0
 

Author Comment

by:Nikhil Sharma
ID: 41833528
0.5      53.35
0.5      57.19
0.6      61.03
0.6      58.94
0.6      48.11
0.7      45.66
0.7      41.47
0.7      47.06
0.8      42.87
0.8      41.47
0.8      47.41
0.8      39.72
0.8      39.03
0.8      42.52
0.8      25.05
0.9      33.44
0.9      25.40
0.9      20.86
0.9      30.64
0.9      10.03
0.9      25.05
1.0      23.66
1.0      30.29
1.0      29.94
1.0      19.47
1.0      25.05
1.0      7.24
1.0      -2.89


average of .5 , .5 than .6,.6,.6 and .7,.7,.7 and so on.....but using some formula ....its logic can be , average till same number appears other wise move to next...
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41833557
But your values in column F are not the same, they are rounded to 1 decimal place. Do you want the average based on the rounded value?

If so, in column H:

=ROUND(F1,1)

and in column I

=IF(H1=H2,"",AVERAGEIF($H$1:$H$2005,H1,$G$1:$G$2005))

Copy both down as far as required. The Average will show on the last entry for each value in F.

Thanks
Rob H
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 41833580
Alternative, with Pivot Table as suggested by Patrick in attached file. Also shows formula option above.
average--2-.xlsx
0
 

Author Comment

by:Nikhil Sharma
ID: 41833660
This worked. I appreciate your quick response. This will save my time. Thanks a lot Mr. Rob Henson and Mr.  Patrick.

Thanks for spending your valuable time on answering my question.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41840124
If you have no further questions, please do not forget to close the question. You can choose an Accepted solution and Assisted solution if so required.

Thanks
Rob H
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41883546
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Rob Henson (https:#a41833580)
-- Patrick Matthews (https:#a41832619)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question