Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2013
    Posts
    6

    Unanswered: How to calculate the average of an unlimited number of dates entered

    I have my program saving all of the dates that they enter for each product right now. So you have a row per product and each column after the name of the item is the dates of last repair. I want to average the dates for each row. So how would I go about doing the individual averages for each one? Would arrays need to be involved?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    average dates?
    thats a new one on me.
    what do you actually intend.
    ..providing the columns are set to NULL values then the inbuilt average function should ignore null values and work correctly.
    of course you could do something radical and use a database to store data and not a spreadsheet
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Quote Originally Posted by kst3268
    I want to average the dates for each row.
    This statement doesn't actually make a lot of sense. Are you saying that you want the average date value for each row? If so, you're going to get time values in there well.
    Can you illustrate what you have with a couple of examples of what you have, and what you want to get out of it?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jun 2013
    Posts
    6
    I should've explained that a little better. But it's basically going to be set up like this:

    Item Name | Date Repaired | Next date repaired | Next date after that repaired

    ^I hope that makes sense. I have programmed it so that after they pick their item they get to type in their new repair date for that piece of equipment. So if they went a month from now to type in another repair date for that same item my code automatically puts it after the previous one. So I want to calculate the time between each date and then calculate the average of all of those. The only problem is that this document is going to be constantly updated so I need to know how to accodomate for the new dates that are going to be put in.

    So, I want to do that process for each row (or item if you're thinking about it like that).


    The bottom half of this code puts the new dates next to the old ones. Hopefully this makes a bit more sense now.



    Private Sub btnDone_Click()
    'testDate = txtDate.Value
    Dim Date1 As Date
    Dim Date2 As Date


    If Len(Trim(txtDate.Value)) = 0 Then
    TextBox1.SetFocus
    Exit Sub
    End If

    On Error Resume Next
    testDate = Str(CDate(txtDate.Text))
    On Error GoTo 0

    If testDate = "" Then
    MsgBox "Date format must be MM/DD/YYYY and must be a valid date"
    txtDate.SetFocus
    Exit Sub
    Else
    validDate = CDate(testDate)
    End If

    'date is good--continue with the code

    MsgBox "You entered a valid date: " & validDate

    Unload Me

    For Each c In Worksheets("Foreman").Range("A4:A237").Cells
    If c.Value = comp Then

    For d = 1 To 25 Step 1
    If c.Offset(0, d) = "" Then
    c.Offset(0, d).Value = validDate
    num2 = c.Offset(0, d - 1).Value
    'ADD IN SUBTRACTION OF DATES

    Date1 = c.Offset(0, d).Value
    Date2 = c.Offset(0, d - 1).Value
    'how to save range correctly*****
    'Worksheets("Detailed View").Range(Range1).Value = (Application.WorksheetFunction.Days360(DateValue(D ate2), DateValue(Date1)))
    Unload Me
    response = MsgBox("Would you like to enter in more dates?", vbYesNo)
    ' MsgBox "Would you like to enter in more dates?", vbYesNo


    If response = vbYes Then
    usfmComp.Show
    Else
    intValueToFind = txtComp.Value
    For i = 1 To 500
    If Cells(i, 1).Value = intValueToFind Then
    MsgBox ("Found value on row " & i)
    Exit Sub
    End If
    Next i
    End If



    Else
    End If
    Next d
    ' Do

    End If
    Next c

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what in your current code isn't working

    is your average an attemtp at mean time between failures?

    use the date time functions for excel, fidn the interval between each date pair then average that

    again by doing this in a spreadsheet you are making it far harder than it needs be. but being a db specialist I would say that
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2013
    Posts
    6
    My company likes excel sadly... But my current code doesn't even have it yet. I think I wrote in some code at the very bottom last week to try and get started on it, but I quickly realized I was going down a very complicated path.


    So do you just recommend that I use the date functions?

  7. #7
    Join Date
    Jun 2013
    Posts
    6
    Oh and my average is an attempt to see if the equipment is lasting as long as the life expectancies we have for them. So in the sheet you'll see the average life, and the anticipated average life.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by kst3268 View Post
    My company likes excel sadly... But my current code doesn't even have it yet. I think I wrote in some code at the very bottom last week to try and get started on it, but I quickly realized I was going down a very complicated path.
    doing this using spreadsheets will be stupidly complicated, thats why Im suggesting your company should drag itself into the 1980's and use a db

    Quote Originally Posted by kst3268 View Post
    So do you just recommend that I use the date functions?
    you want intervals
    you want the average time between failures

    that means you are doing date arithmatic, which means use the date time functions

    you probably can do what you want using hidden cells or cells outside the print range that do your calcualtions. however it will be needlessly complex and prone to breakage. prone to giving suspect or dubious results. In my opinion this is not something you should be using a spreadsheet for.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2013
    Posts
    6
    Well, seeing as though I'm not given the option I think i'll just do it like I did with entering the new dates in (the offset function). Or some variation of that to check for the new dates.

    Thanks for the help.

Posting Permissions

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