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

1. Registered User
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

3. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
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?

4. Registered User
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

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

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)))
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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

6. Registered User
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. Registered User
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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by kst3268
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

Originally Posted by kst3268
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.

9. Registered User
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
•