Consulting

Results 1 to 6 of 6

Thread: [Question] Dynamic amount of averages

  1. #1

    [Question] Dynamic amount of averages

    Hello.

    rather complex question i assume, as i have no idea of how to go about it.

    i have about 500 lines of values, i want to turn into about 10lines.
    the 10 lines consisting of 10 different averages based on the 500 values.

    With the first of the 10lines being the most common average
    The second line being the second most common average and so on.

    with hopefully, an easy way to "set value", with which to differentiate when a "new" average breakpoint, should be calculated/made.

    EDIT just to make it even harder
    If only 6 averages are found within the "set value", is it then possible to only have 6 averages shown.

    anyone know a way to start this ?
    Last edited by technician12; 09-16-2020 at 05:39 AM.

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    Hopefully, this will get you a really good head start. This will create a list of unique values and FREQUENCY, in turn, your top 10 values
    Option Explicit
    Sub UniqueItems()
    ' This will display unique items using Column A
    ' The big difference of this routine is that it is using Dictionary
        Dim myArray As Variant
        Dim dDictionary As Object
        Dim vItem As Variant
        Dim Index As Long
        Dim NewSheet As Worksheet
        Dim sOutput As String
        
        ' you can use RANGE to assign to the array
        '
        ' myArray = Range ("B2:B" & LastRow)
        '
        ' or
        myArray = ActiveSheet.[a1].Resize(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row)
        If IsEmptyArray(myArray) Then
            MsgBox "You have nothing in Column A", vbCritical, "Nothing in Column A"
        Else
            Set dDictionary = CreateObject("scripting.dictionary")
            For Index = 1 To UBound(myArray)
                dDictionary(myArray(Index, 1)) = dDictionary(myArray(Index, 1)) + 1
            Next
            
            If SheetExists("Unique List") Then
                Application.DisplayAlerts = False
                Worksheets("Unique List").Delete
                Application.DisplayAlerts = True
            End If
            Set NewSheet = Worksheets.Add
            NewSheet.Name = "Unique List"
        
            NewSheet.Select
            With NewSheet
                .[A1:B1] = Split("Item Frequency", " ")
                .[A2].Resize(dDictionary.Count) = Application.Transpose(dDictionary.Keys)
                .[B2].Resize(dDictionary.Count) = Application.Transpose(dDictionary.Items)
            End With
            
            ' Output onto screen
            With dDictionary
                For Each vItem In .Keys
                    sOutput = sOutput & vbLf & vItem & " (" & .Item(vItem) & ")"
                Next
            End With
            Set dDictionary = Nothing
        End If
    End Sub
    Public Function IsEmptyArray(InputArray As Variant) As Boolean
    
       On Error GoTo ErrHandler:
       IsEmptyArray = Not (UBound(InputArray) >= 0)
       Exit Function
    
    ErrHandler:
        IsEmptyArray = True
        On Error GoTo 0
    End Function
    Function SheetExists(sName As String, Optional oWb As Workbook) As Boolean
    
    'Returns true if sheet exists in the specified workbook.
    'If no workbook supplied, the activeworkbook is used.
    
        If oWb Is Nothing Then
            Set oWb = ActiveWorkbook
        End If
    
        On Error Resume Next
        SheetExists = CBool(Not oWb.Sheets(sName) Is Nothing)
        On Error GoTo 0
    End Function

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    i have about 500 lines of values, i want to turn into about 10 lines.
    the 10 lines consisting of 10 different averages based on the 500 values.

    My simplistic brain tells me that if you take the average of 500 lines 10 times, you'll get the same number 10 times

    So I know that there must be something I'm missing



    With the first of the 10 lines being the most common average
    The second line being the second most common average and so on.
    What did I miss?


    Can you attach a sample workbook?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Quote Originally Posted by Paul_Hossler View Post
    Can you attach a sample workbook?
    yes

    also, if possible without VBA, i'd greatly appreciate it, as it would get far easier to get approved inhouse.
    Attached Files Attached Files

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Can you explain what you mean by

    1. Sort all within ±500 of each other

    2. the 10 ( or less ) most common averages, considering the ±500 interval of each other

    With examples if you can
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    to be honest, i've found a much cleaner solution to my end goal, than using the system i requested here, but for pure interest i'll explain

    i wanted the list of 500 values to sorted and averaged based on the value in I6 based on how common they were.

    Example would be, if the value in I6 was 100.
    then, it would look for the most common range spread, in the 500's values within ±100 and return the average of the 500 most commonly found within ±100 in M6, and the second most common in M7 and so on.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •