calculate percent met (comparing 2 date columns)
 

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 based
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 counts
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 thinking
something like

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

again thanks


--
Cobra
------------------------------------------------------------------------
Cobra's Profile: http://www.excelforum.com/member.php...o&userid=31065
View this thread: http://www.excelforum.com/showthread...hreadid=507405

calculate percent met (comparing 2 date columns)
 

You could use this formula

=AVERAGE(IF(N2:N20TODAY()-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:N20TODAY()-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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=507405

calculate percent met (comparing 2 date columns)
 

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


--
Cobra
------------------------------------------------------------------------
Cobra's Profile: http://www.excelforum.com/member.php...o&userid=31065
View this thread: http://www.excelforum.com/showthread...hreadid=507405