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