calculate percent met (comparing 2 date columns)

C

Cobra

tia for helping me with this.

I have 2 columns of dates "duedate" and "compdate"

I want to calculate the percentage of rows that met the due date base
on the last 4 weeks.

if n2:n20 < (today-28) and if n2:n20 < d2:d20 = met

(something that takes all the dates within the last 4 weeks and count
how many are on or before the due date)

divided by

n2:n20 < (today-28) = total (the total number of compdates)

hope that was clear. In my efforts to try and solve this I'm thinkin
something like

=sum (countif n2:n20, <today(-28), <d2:20) / (count n2:n20
today(-28))

again thank
 
D

daddylonglegs

You could use this formula

=AVERAGE(IF(N2:N20>TODAY()-28,IF(N2:N20<=D2:D20,1,0)))

which needs to be confirmed with CTRL+SHIFT+ENTER

or this one which just needs enter

=SUMPRODUCT(--(N2:N20>TODAY()-28),--(N2:N20<=D2:D20))/MAX(1,COUNTIF(N2:N20,">"&TODAY()-28))

format as percentage

Note second formula also has the advantage that it won't return a
#DIV/0! error
 
C

Cobra

Excellent, thank you daddylonglegs and I now understand where I was
missing the formating
 
Top