Counting rows to generate stats.

montecarlo2012

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
984
Office Version
  1. 2010
Platform
  1. Windows
HELLO.
Trying to accomplish some statistics. I need to see thedistance between every number counting the rows NOT CELL BY CELL; the attach isclear.<o:p></o:p>
1 ) H1 is the max inte, mean take the maximum numberin the distance by row report.<o:p></o:p>
2) I1 is the Trend function apply to that linenumber<o:p></o:p>
3) J1 is theaverage<o:p></o:p>
4) K1 close average is the first number minus average<o:p></o:p>
5) L1 close trendis the first number minus the trend<o:p></o:p>
And after this come the distance report . example the number 5 in the bin the first distance is0 and the second is 1 number 5 shows up in the first row mean iqual 0 afterjump one row so reason why the number in N6 is 1, second example is number 1 inA8 so the number in M2 is 7 because one jump seven lines count from the first.
My bin is G2:G37 (numbers from 1 to 36)
My data is A1:E4000
Code: 
[/SIZE]
[TABLE="width: 768"]
[SIZE=3] [/SIZE]<colgroup><col style="width: 48pt;" span="16" width="64">[SIZE=3][/SIZE][SIZE=3] [/SIZE]<tbody>[TR]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]G[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]H[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]I[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]J[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]K[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]L[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]M[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]N[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]O[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]P[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]Q[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]R[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]S[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]T[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]U[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, width: 64, bgcolor: transparent"][SIZE=3]V[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3]max inte[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3]trend[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3]average[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3]close/av[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3]close/tre[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]6.727273[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]4.6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2.4[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0.272727[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]8[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2.311111[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2.777778[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0.222222[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0.688889[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]4[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]13[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]8.25[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]7.875[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1.125[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0.75[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]13[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]13[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]10[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]4[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]10[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]4[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]39[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]17.8[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]11.2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]-5.2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]-11.8[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]39[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]8[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1.666667[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]2.166667[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]-2.16667[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]-1.66667[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]4[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]0[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent, align: right"][SIZE=3]6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl63, bgcolor: transparent"][SIZE=3] [/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3][/SIZE]</tbody>[/TABLE]

[SIZE=3]
DATA EXAMPLE
Code: 
[/SIZE]
[TABLE="width: 240"]
[SIZE=3] [/SIZE]<colgroup><col style="width: 48pt;" span="5" width="64">[SIZE=3][/SIZE][SIZE=3] [/SIZE]<tbody>[TR]
[SIZE=3]  [/SIZE][TD="class: xl65, width: 64, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, width: 64, bgcolor: transparent, align: right"][SIZE=3]6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, width: 64, bgcolor: transparent, align: right"][SIZE=3]17[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, width: 64, bgcolor: transparent, align: right"][SIZE=3]31[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, width: 64, bgcolor: transparent, align: right"][SIZE=3]34[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]18[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]19[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]23[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]33[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]11[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]19[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]36[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]24[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]30[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]12[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]26[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]28[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]15[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]30[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]4[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]8[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]24[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]28[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]16[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]19[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]34[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]10[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]19[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]31[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]10[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]32[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]20[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]23[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]30[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]15[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]21[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]23[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]16[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]22[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]23[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]25[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]31[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]21[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]30[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]31[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]32[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]11[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]35[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]13[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]19[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]20[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]26[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]21[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]23[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]11[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]15[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]36[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]18[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]21[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]35[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]17[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]22[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]33[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]14[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]16[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]23[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]26[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]9[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]11[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]13[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]34[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]7[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]20[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]31[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]36[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]11[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]24[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]26[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]27[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]35[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]10[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]17[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]19[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]22[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]1[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]6[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]10[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]15[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]36[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]3[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]25[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]26[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]33[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]36[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]8[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]11[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]13[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]29[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3] [/SIZE][TR]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]2[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]5[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]21[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]26[/SIZE][/TD]
[SIZE=3]  [/SIZE][TD="class: xl65, bgcolor: transparent, align: right"][SIZE=3]35[/SIZE][/TD]
[SIZE=3] [/SIZE][/TR]
[SIZE=3][/SIZE]</tbody>[/TABLE]

[SIZE=3]
thanks for reading this post.

<o:p> </o:p>
<o:p> </o:p>
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try this:-
You will need to put your trend formula in columns "I" & "L" aftyer the code has run.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG10Nov30
[COLOR="Navy"]Dim[/COLOR] rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q       [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Omax    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oSub    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 5)
    ReDim Ray(1 To rng.Count, 1 To 2)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] rng.Rows
    n = n + 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rw.Columns
    
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Ray(1, 1) = n - 1: Ray(1, 2) = n - 1
        .Add Dn.Value, Array(Ray, 1)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
            Q(1) = Q(1) + 1
            oSub = IIf(Q(1) > 2, 1, 2)
            Q(0)(Q(1), 1) = n
            Q(0)(Q(1), 2) = n - Q(0)(Q(1) - 1, 1) - oSub
            Omax = Application.Max(Omax, Q(1))
        .Item(Dn.Value) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
 c = 1
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, 7) = K
     Cells(c, 12).Font.Bold = True
        [COLOR="Navy"]For[/COLOR] r = 1 To .Item(K)(1)
            Cells(c, 12 + r) = .Item(K)(0)(r, 2)
        [COLOR="Navy"]Next[/COLOR] r
 [COLOR="Navy"]Next[/COLOR] K
    Range("G2").Resize(.Count, Omax + 6).Sort Range("G2"), xlAscending
    Call RwData(Range("M2").Resize(.Count), Omax)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] RwData(rng [COLOR="Navy"]As[/COLOR] Range, col [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
[COLOR="Navy"]With[/COLOR] Application
    Dn.Offset(, -5) = .Max(Dn.Resize(, .CountA(Dn.Resize(, col))))
    Dn.Offset(, -3) = .Average(Dn.Resize(, .CountA(Dn.Resize(, col))))
    Dn.Offset(, -2) = Dn - .Average(Dn.Resize(, .CountA(Dn.Resize(, col))))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
I'm assuming that the formulae in columns H:L you already have, so to address the second part, the distance reportL:
Using only the limited data you supplied, and referring only to that data, the formula in M3 and M4 should both be ARRAY-ENTERED (Ctrl+Shift+Enter, NOT just Enter). That in M3 should then be copied down. That in N3 can be copied down and across. Adjust the formulae for your data range of A1:E4000.

I've used IFERROR, again an assumptpion on my part, that you're using XL2007 or higher.
Excel Workbook
GMNOPQRSTUVW
3175280
42303503070
539313
646
7501453010
Sheet
 
Upvote 0
work only, until the line 1550 and debug said Cells(c,12+r)=.Item(k)(0)(r,2). my data is 6057 and increase daily. I am talking about the Mick'code. when I include my whole data do not work.
 
Upvote 0
When you click "DeBug" , hold the curser over the variable "r" and "oMax" see if the number is less then the number of columns you have.
The variable "r" will show the column it failed on and "oMax" will show the max number of columns the results need.
 
Upvote 0
Thanks MickG.
I put the cursor on "r" (what you mean by hold) and say =245, I don't understand when you say "oMax". thanks again
 
Upvote 0
What version of excel are you using, Ealier versions "2003" only have 250 columns.
If your using a later version, this is obvioulsy not the problem
"oMax" in a variable in the code, if hold you curser over it in "Debug" it will show the number of columns you need for the results to fit within the sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Individual Ranking
2
Replies
11
Views
370
sksanjeev786
S
J
  • Question
Removing blank rows based on cell value
Replies
2
Views
208
Peter_SSs
Peter_SSs
D
  • Solved
List of items within a specific month
Replies
4
Views
150
Fluff
Fluff
F
  • Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
136
Peter_SSs
Peter_SSs
K
  • Question
conditional formatting question
Replies
2
Views
507
kerm007
K

Forum statistics

Threads
1,221,497
Messages
6,160,151
Members
451,625
Latest member
sukhman

Share this page

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