Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average last 6 weeks and year before | Excel Discussion (Misc queries) | |||
Help Please: Need an Average for 16 Weeks according to Month | Excel Worksheet Functions | |||
Calculation based on numbers of weeks service | Excel Discussion (Misc queries) | |||
I have 12 scores over 12 weeks . I want to average the highest 5 | Excel Worksheet Functions | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions |