I need to create a new metric for my CCR log that shows the average closure of CCRs with a particular code. I have tried an array formula that I successfully use in another log, but for whatever reason can't tweak for this new purpose. I have the following setup:
(A) Code (B)Issued (C)Closed (D)Days to Close
3I 10/1/11 10/13/11 12
3I 10/25/11 -40841 (this one is still open)
3I 10/27/11 10/31/11 4
There are multiple codes in the log. Column D is a simple SUM formula. What I wanted to do was set up a formula that would average column D if cells in column A contained 3I. I can do this with a simple AVERAGEIF formula, but I need to exclude the negative numbers for reports that haven't closed yet. I tried adding the ">-1" condition to the formula, but it returned a #VALUE error.
Thank you in advance!
