VBA/MACRO for automating data calculating

[rtcoake]

I am trying to determine average data calculation from a large amount of data, some of this data is not needed. I am wanting to create a macro or VBA code to take the large amount of copied data, delete the data below the calculated average for the first set of data and return the new average with the new set of data. I hope that makes since. I am not a VBA user and would like to stream line my process to make it more simple. Any help is appreciated. Now I copy the data from the source, paste into excel, manually sort largest to smallest, manually delete anything below the calculated average from a formula, then use the new average as my data point.

 

[Oddball2020]

Did you want the macro to first grab the data out of another workbook, or just address everything internally in the workbook you're using to do the crunching? Have you ever used VBA before? If not, a bit more explanation is involved to get you situated.

 

[Oddball2020]

I am trying to determine average data calculation from a large amount of data, some of this data is not needed. I am wanting to create a macro or VBA code to take the large amount of copied data, delete the data below the calculated average for the first set of data and return the new average with the new set of data. I hope that makes since. I am not a VBA user and would like to stream line my process to make it more simple. Any help is appreciated. Now I copy the data from the source, paste into excel, manually sort largest to smallest, manually delete anything below the calculated average from a formula, then use the new average as my data point.

Did you want the macro to first grab the data out of another workbook, or just address everything internally in the workbook you're using to do the crunching? Have you ever used VBA before? If not, a bit more explanation is involved to get you situated.

Until then, here's how you can do it using formula:

Put this flagging formula in a column next to the raw data you're trying to average (assumes Column A has your raw data in my example, so start with this in B2, row 1 has headers):

=IF(A2>AVERAGE(A:A), "Yes", "No")

Drag/autofill this down your entire list.

Then in another cell (not in those columns) use this formula to assess the average for only that data that was above the average of the original list:

=SUMIFS(A:A, B:B, "Yes")/COUNTIFS(B:B, "Yes")

And that's your answer! You never had to delete, or sort anything, nor resort to VBA (but using VBA to dump that formula for you automatically would be simple to do).