VBA looping to remove min / max values and recompute average

[BlueAZCoug]

Hi everyone, I'm new to this site and hoping to get some help on VBA looping. I have a spreadsheet with financial data for several companies (example shown below, except that my real data is much larger, closer to 50 companies). I compute the average of the gross profit for all companies, and if 80% of the companies are within +/-15% of that average, then I'm done. If 80% of the companies are not within 15% of that average, I remove the maximum and minimum gross profit values and start the process over again by computing the average gross margin of the remaining companies (i.e. after taking out the min and max values). It's a very iterative/repetitive process, and with so many companies it's quite time consuming to do it by hand. I figured it would be easier to use VBA looping to whittle down the population until I get to my 80% target. Hopefully that explanation makes sense. I don't want to delete any of the information, so I'm not looking to delete any rows or anything with the code, I just want to exclude those companies from the calculation in some manner. I've never worked with looping in VBA (I'm somewhat of a VBA virgin if you will), but I'm willing to learn and hoping someone can point me in the right direction. Thanks for you time!

[TABLE="class: grid, width: 712, align: center"]
<tbody>[TR]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]

Iteration #1​

[/TD]
[TD="align: center"][/TD]
[TD="colspan: 2"]

Iteration #2​

[/TD]
[TD="align: center"][/TD]
[TD="colspan: 2"]

Iteration #3​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Company Name​

[/TD]
[TD]

2013 GM%​

[/TD]
[TD]

Within 15% of Avg?​

[/TD]
[TD]

Exclude?​

[/TD]
[TD="align: center"][/TD]
[TD]

Within 15% of Avg?​

[/TD]
[TD]

Exclude?​

[/TD]
[TD="align: center"][/TD]
[TD]

Within 15% of Avg?​

[/TD]
[TD]

Exclude?​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Company A
[/TD]
[TD]5.5%
[/TD]
[TD]No
[/TD]
[TD]Exclude
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Exclude
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Exclude
[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Company B
[/TD]
[TD]21.1%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]21.1%
[/TD]
[TD]Yes
[/TD]
[TD]Exclude
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Exclude
[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Company C
[/TD]
[TD]20.3%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]20.3%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]20.3%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Company D
[/TD]
[TD]20.3%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]20.3%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]20.3%
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Company E
[/TD]
[TD]13.3%
[/TD]
[TD]No
[/TD]
[TD][/TD]
[TD]13.3%
[/TD]
[TD]No
[/TD]
[TD]Exclude
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Exclude
[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Company F
[/TD]
[TD]42.8%
[/TD]
[TD]No
[/TD]
[TD]Exclude
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Exclude
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Exclude
[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]# of companies within 15% of avg:
[/TD]
[TD][/TD]
[TD] 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD] 3
[/TD]
[TD][/TD]
[TD][/TD]
[TD] 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Total # of companies:
[/TD]
[TD][/TD]
[TD] 6
[/TD]
[TD][/TD]
[TD][/TD]
[TD] 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD] 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]% of population within 15% of avg:
[/TD]
[TD][/TD]
[TD]50%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]75%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Result of test:
[/TD]
[TD][/TD]
[TD]Fail
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Fail
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pass
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[onlyadrafter]

Hello,

this may seem a dumb question, but what data, from your example, do you start with?

 

[BlueAZCoug]

onlyadrafter --

The data I'm starting with is in column C. In the first iteration I remove the highest and lowest values (shown in column E), in the second iteration I remove the next highest and lowest values (shown in column H), and so on. I would post the workbook but can't seem to figure out how to attach it to this thread. At any rate, there's got to be a better way to whittle down the data than going through this manual process, I just can't figure out how to do it with looping in VBA.

 

[onlyadrafter]

Hello,

Have assumed that the cells in Column D in your example are formulas, so that when you copy the data over they get updated as per your posted example.

This code gives me the same result as per you example

Sub ITERATION()
    MY_START = 4
    MY_LOWEST = 100
    MY_HIGHEST = 0
'COPY OVER LAST ITERATION
    Do Until Cells(13, MY_START).Value = "Pass"
        Range(Cells(2, MY_START - 1), Cells(13, MY_START)).Copy
        Cells(2, MY_START + 2).PasteSpecial (xlPasteAll)
'REMOVE LOWEST AND HIGHEST VALUES
        For MY_COMPANY = 3 To 8
            If Cells(MY_COMPANY, MY_START + 2).Value < MY_LOWEST And _
                    Not IsEmpty(Cells(MY_COMPANY, MY_START + 2).Value) Then
                MY_LOWEST = Cells(MY_COMPANY, MY_START + 2).Value
                MY_LOW_CLEAR = MY_COMPANY
            End If
            If Cells(MY_COMPANY, MY_START + 2).Value > MY_HIGHEST And _
                    Not IsEmpty(Cells(MY_COMPANY, MY_START + 2).Value) Then
                MY_HIGHEST = Cells(MY_COMPANY, MY_START + 2).Value
                MY_HIGH_CLEAR = MY_COMPANY
            End If
        Next MY_COMPANY
        Cells(MY_LOW_CLEAR, MY_START + 2).ClearContents
        Cells(MY_HIGH_CLEAR, MY_START + 2).ClearContents
        MY_LOWEST = 100
        MY_HIGHEST = 0
        MY_START = MY_START + 3
    Loop
End Sub

Does it work as expected?

 

[BlueAZCoug]

onlyadrafter --

Yes, it works brilliantly, thank you for your help. However, I have a few questions about the code (just trying to understand the logic):

1) Obviously the data I posted above is just an example. If I have 100 companies (placed in rows 3 to 103), I just need to set "MY_COMPANY" = 3 To 103, correct?
2) How do the "MY_LOWEST" and "MY_HIGHEST" arguments work? I'm trying to understand why MY_LOWEST is set to 100 and not 0.

Thanks again, really do appreciate your help with this!

 

[onlyadrafter]

Hello,

1) Yes, change 8 to the last row number of you company name.

2) MY_LOWEST starts at 100 because as these are %'s the highest value it could be is 100%. So the first number it looks at will be lower than 100, it will then compare the next number against this number and so on. MY_HIGHEST does the same but in opposite. I could have chosen numbers that are extremely high and extremely low (1E-255 and 1E+255).

OK?

 

[BlueAZCoug]

Makes sense, thanks again for your help, much appreciated.