formula for days between completed meetings
 

Here's the deal. I have 10 meeting dates (some may be blank) and 10 columns
beside each that are either blank (meaning completed) or have the word
'Missed', as such:

A B C D E
1/2/09 (blank) 1/4/09 Missed 1/5/09 (blank) etc etc

Too many to use nested IFs.

I have been asked to calculate the average days between non-missed meetings.
Some of the meeting date cells will be blank. I would appreciate any advice.

--
cm

formula for days between completed meetings
 

do we consider end date as 10th column date or Col A & Col C dates.




On Oct 20, 11:04*pm, cm wrote:
Here's the deal. I have 10 meeting dates (some may be blank) and 10 columns
beside each that are either blank (meaning completed) or have the word
'Missed', as such:

A * * * * B * * * *C * * * * D * * * * *E * * * * * *
1/2/09 *(blank) *1/4/09 Missed * 1/5/09 (blank) * etc etc

Too many to use nested IFs.

I have been asked to calculate the average days between non-missed meetings.
Some of the meeting date cells will be blank. I would appreciate any advice.

--
cm

formula for days between completed meetings
 

Sounds like a task for a Pivot Table:
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"cm" wrote:

Here's the deal. I have 10 meeting dates (some may be blank) and 10 columns
beside each that are either blank (meaning completed) or have the word
'Missed', as such:

A B C D E
1/2/09 (blank) 1/4/09 Missed 1/5/09 (blank) etc etc

Too many to use nested IFs.

I have been asked to calculate the average days between non-missed meetings.
Some of the meeting date cells will be blank. I would appreciate any advice.

--
cm

formula for days between completed meetings
 

end date would be the last date that is filled in. so if the person had 10
meetings scheduled, all 10 would have a value. if the person had only 4
meetings scheduled, there would only be 4 values.
--

cm


"muddan madhu" wrote:

do we consider end date as 10th column date or Col A & Col C dates.




On Oct 20, 11:04 pm, cm wrote:
Here's the deal. I have 10 meeting dates (some may be blank) and 10 columns
beside each that are either blank (meaning completed) or have the word
'Missed', as such:

A B C D E
1/2/09 (blank) 1/4/09 Missed 1/5/09 (blank) etc etc

Too many to use nested IFs.

I have been asked to calculate the average days between non-missed meetings.
Some of the meeting date cells will be blank. I would appreciate any advice.

--
cm

.

formula for days between completed meetings
 

use this User Defined Function

right click on sheet name, click view code, insert module paste the
below code,
Go to excel sheet put formula as =Avg(A1:K1)

Let me know errors, if any

Function Avg(Inputvalue As Range)
Dim cell As Range
For Each cell In Inputvalue
If cell.Value = "missed" Then
result = cell.Offset(0, -1).Value + result
counter = counter + 1
End If
Next cell
With Application.WorksheetFunction
X = .Count(Inputvalue)
y = .Sum(Inputvalue)
Z = y - output
z1 = X - counter
End With
Avg = Z / z1
End Function




On Oct 21, 6:51*pm, cm wrote:
end date would be the last date that is filled in. so if the person had 10
meetings scheduled, all 10 would have a value. if the person had only 4
meetings scheduled, there would only be 4 values.
--

cm

"muddan madhu" wrote:
do we consider end date as 10th column date or Col A & Col C dates.

On Oct 20, 11:04 pm, cm wrote:
Here's the deal. I have 10 meeting dates (some may be blank) and 10 columns
beside each that are either blank (meaning completed) or have the word
'Missed', as such:

A * * * * B * * * *C * * * * D * * * * *E * * * * * *
1/2/09 *(blank) *1/4/09 Missed * 1/5/09 (blank) * etc etc

Too many to use nested IFs.

I have been asked to calculate the average days between non-missed meetings.
Some of the meeting date cells will be blank. I would appreciate any advice.

--
cm

.