Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto date script for 2 columns? have 1.. | Excel Discussion (Misc queries) | |||
search for latest date | Excel Worksheet Functions | |||
calculate future date | Excel Worksheet Functions | |||
calculate anniversary of date after specified date | Excel Worksheet Functions | |||
Comparing Data in two columns | Excel Worksheet Functions |