Marco to check data

[Asalhaljayar]

Hi,

How can i macro this requirement in excel for existing list:
List of all data above average rate by 40%

 

[Belleke]

Nice example

 

[Monty]

Hi,

How can i macro this requirement in excel for existing list:
List of all data above average rate by 40%

To create a macro in Excel to list all data above the average rate by 40%, you can use VBA (Visual Basic for Applications). Follow these stepsress Alt + F11 to open the Visual Basic for Applications (VBA) editor.Insert a new module by right-clicking on any item in the Project Explorer, selecting Insert, and then choosing Module.Copy and paste the following VBA code into the module:

>>> use code - tags <<<

Sub FilterAboveAverage()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim averageRate As Double
    Dim threshold As Double

    ' Set the worksheet and range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to the name of your sheet
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) ' Assumes data is in column A

    ' Calculate the average rate
    averageRate = Application.WorksheetFunction.Average(rng.Value)

    ' Set the threshold (40% above average)
    threshold = averageRate * 1.4

    ' Clear any previous filters
    ws.AutoFilterMode = False

    ' Apply a filter for values above the threshold
    rng.AutoFilter Field:=1, Criteria1:=">" & threshold

    ' Copy the visible cells to a new sheet (adjust destination as needed)
    rng.SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("B1") ' Copies to column B, adjust as needed

    ' Clear the filter
    ws.AutoFilterMode = False
End Sub

Close the VBA editor.Press Alt + F8, select FilterAboveAverage, and click Run.This macro will filter the data in column A based on values above 40% of the average rate and copy the visible cells to column B. Adjust the sheet name and column references as needed.Make sure to save your workbook as a macro-enabled workbook (.xlsm) to keep the macro functionality.