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

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

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

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

.

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

.