VBA to calculate average with exclusion of outliers

yxz152830

Active Member
Joined
Oct 6, 2021
Messages
395
Office Version
  1. 365
Platform
  1. Windows
Gurus I think this one is too complicated using formulas so I'm looking for guidance to use VBA directly for solving it.
If I have a set of data that I want to calculate the average for each ID, each ID might have 1 or more than 1 lines.
Right now I'm having trouble writing the script coping with an ID with more than 3 lines of value.
In short, could you give me some hints and guidance on finding out outliers to exclude them and then do average on the remaining lines? Thanks!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Post the code you have as a starting point? If you do, please post within code tags (vba button on posting toolbar) to maintain indentation and readability.
Not much info given. Perhaps you'd only want to average if there are at least 3 rows for an id? Not much sense in averaging 2 values, perhaps. Also, what constitutes an outlier? One each of the highest and lowest value in a minimum of 5 rows? Any value that is x above or below the average if the outliers are taken into account (so remove then recalculate? Something else?

Not saying I'm game for this one; at this point I'm just trying to glean more information that I think anyone would need.
 
Upvote 0
Post the code you have as a starting point? If you do, please post within code tags (vba button on posting toolbar) to maintain indentation and readability.
Not much info given. Perhaps you'd only want to average if there are at least 3 rows for an id? Not much sense in averaging 2 values, perhaps. Also, what constitutes an outlier? One each of the highest and lowest value in a minimum of 5 rows? Any value that is x above or below the average if the outliers are taken into account (so remove then recalculate? Something else?

Not saying I'm game for this one; at this point I'm just trying to glean more information that I think anyone would need.
Hi sorry for the lengthy description but I think I'm stuck at finding the lines with same ID in the first place. Could you suggest how to find the number of lines with same ID? I think simply looping thru the lines and do i+1 would not help as I need to further break down the values by different scenarios. I was thinking about something like dictionary(dictionary should not be applicable in this situation I suppose)
 
Upvote 0
I really have no idea what this all looks like. Maybe you should post some sample data (not a pic of data) and something that shows the desired result. If you cannot answer questions (e.g. 'what is an outlier?") then you are going to have problems getting answers.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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