Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

binned average of large set of data

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
Nikhil Sharma
Asked:
Nikhil Sharma
2 Solutions
 
Nikhil SharmaAuthor Commented:
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
 
Patrick MatthewsCommented:
Why not simply use a PivotTable?
0
 
Rob HensonIT & Database AssistantCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nikhil SharmaAuthor Commented:
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
 
Nikhil SharmaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Rob HensonIT & Database AssistantCommented:
Alternative, with Pivot Table as suggested by Patrick in attached file. Also shows formula option above.
average--2-.xlsx
0
 
Nikhil SharmaAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
frankhelkCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now