+ Reply to Thread
Results 1 to 3 of 3

Help with VB code - average of numbers over specified weeks

  1. #1
    phil-rge-ee
    Guest

    Help with VB code - average of numbers over specified weeks

    I have a spreadsheet that has weeks 1-9 (columns C-K). In each week is 3
    numbers (rows 4-6). Those numbers get added together to get a total for that
    week (row 7).
    I need help with creating the VB script that when run adds the totals
    together and gives an average for a specified week range.

    Example: user clicks a button and a dialog box comes up where they enter in
    the week range in textboxes. So textbox1.text could be 3 and textbox2.text
    could be 7. Then the user clicks an ok button and the script runs. The script
    would add together the totals for week 3,4,5,6,7 (in row 7) then divide that
    sum by: number of weeks x 3 (in this case 15)
    Another example: user enters in week 6 to week 9, they click ok and the
    script runs. It would add the totals for week 6,7,8,9 then divide that sum
    by: number of weeks x 3 (in this case 12)

    The week range will be different all the time, depending on what specified
    week average I need at that time. Can anyone help me with this?

    Thanks for any help you can provide,
    Phil


  2. #2
    Bob Phillips
    Guest

    Re: Help with VB code - average of numbers over specified weeks

    Sub myAVerage()
    Dim rng As Range
    Dim iStart, iEnd

    Set rng = Range("C7")
    iStart = 3
    iEnd = 7

    Do
    iStart = InputBox("Input first column (1-7)")
    Loop Until iStart = "" Or (iStart > 0 And iStart < 8)

    If iStart <> "" Then
    Do
    iEnd = InputBox("Input first column (1-7)")
    Loop Until iEnd = "" Or (iEnd > 0 And iEnd < 8)

    myval = Application.Sum(rng(1, CLng(iStart)).Resize(, iEnd - iStart
    + 1)) / ((iEnd - iStart + 1) * 3)

    MsgBox myval
    End If

    End Sub


    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "phil-rge-ee" <[email protected]> wrote in message
    news:[email protected]...
    > I have a spreadsheet that has weeks 1-9 (columns C-K). In each week is 3
    > numbers (rows 4-6). Those numbers get added together to get a total for

    that
    > week (row 7).
    > I need help with creating the VB script that when run adds the totals
    > together and gives an average for a specified week range.
    >
    > Example: user clicks a button and a dialog box comes up where they enter

    in
    > the week range in textboxes. So textbox1.text could be 3 and textbox2.text
    > could be 7. Then the user clicks an ok button and the script runs. The

    script
    > would add together the totals for week 3,4,5,6,7 (in row 7) then divide

    that
    > sum by: number of weeks x 3 (in this case 15)
    > Another example: user enters in week 6 to week 9, they click ok and the
    > script runs. It would add the totals for week 6,7,8,9 then divide that sum
    > by: number of weeks x 3 (in this case 12)
    >
    > The week range will be different all the time, depending on what specified
    > week average I need at that time. Can anyone help me with this?
    >
    > Thanks for any help you can provide,
    > Phil
    >




  3. #3
    phil-rge-ee
    Guest

    Re: Help with VB code - average of numbers over specified weeks

    Thanks, Bob. Man you guys are good. I've taught myself some VB, but when it's
    way over my head I turn to the experts. Thanks alot, I'll give it a try.

    Phil

    "Bob Phillips" wrote:

    > Sub myAVerage()
    > Dim rng As Range
    > Dim iStart, iEnd
    >
    > Set rng = Range("C7")
    > iStart = 3
    > iEnd = 7
    >
    > Do
    > iStart = InputBox("Input first column (1-7)")
    > Loop Until iStart = "" Or (iStart > 0 And iStart < 8)
    >
    > If iStart <> "" Then
    > Do
    > iEnd = InputBox("Input first column (1-7)")
    > Loop Until iEnd = "" Or (iEnd > 0 And iEnd < 8)
    >
    > myval = Application.Sum(rng(1, CLng(iStart)).Resize(, iEnd - iStart
    > + 1)) / ((iEnd - iStart + 1) * 3)
    >
    > MsgBox myval
    > End If
    >
    > End Sub
    >
    >
    > --
    >
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from the email address if mailing direct)
    >
    > "phil-rge-ee" <[email protected]> wrote in message
    > news:[email protected]...
    > > I have a spreadsheet that has weeks 1-9 (columns C-K). In each week is 3
    > > numbers (rows 4-6). Those numbers get added together to get a total for

    > that
    > > week (row 7).
    > > I need help with creating the VB script that when run adds the totals
    > > together and gives an average for a specified week range.
    > >
    > > Example: user clicks a button and a dialog box comes up where they enter

    > in
    > > the week range in textboxes. So textbox1.text could be 3 and textbox2.text
    > > could be 7. Then the user clicks an ok button and the script runs. The

    > script
    > > would add together the totals for week 3,4,5,6,7 (in row 7) then divide

    > that
    > > sum by: number of weeks x 3 (in this case 15)
    > > Another example: user enters in week 6 to week 9, they click ok and the
    > > script runs. It would add the totals for week 6,7,8,9 then divide that sum
    > > by: number of weeks x 3 (in this case 12)
    > >
    > > The week range will be different all the time, depending on what specified
    > > week average I need at that time. Can anyone help me with this?
    > >
    > > Thanks for any help you can provide,
    > > Phil
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1