Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Conditional sum gives a (runtime) FormulaArray error

  1. #1

    Join Date
    Mar 2002
    Location
    Chennai, India
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a sheet with the following fields:
    IMS No (Bug Id)
    Assigned on (date on which assigned)
    Planned Start (the date on which I planned to start fixing the bug)
    Planned Close (the date on which I planned to finish fixing the bug)
    Actual Start (the date on which I actually started fixing the bug, may be same/ different from plan date)
    Actual Close (the date on which I actually finished fixing the bug, may be same/ different from plan date)
    Closed after fix (the date on which it was verified and passed by the testing team)
    Status (Open or Closed)
    Time To Repair (TTR - Time taken from date assigned to date closed, i.e. Closed after fix-Planned Start+1)
    Time To Fix (TTF - Time taken from actual start to actual end i.e. Actual Start - Actual End +1)
    My problem is to find a mean of the TTR for all bugs planned to start AND also closed in this month. Note that some bugs may have started last month and closed this month and also some bugs started this month may slip into the next month. Such bugs are to be avoided for calculations.
    As usual I went to Tools-> Wizard-> Conditional Sum. I selected the range ($A$1:$J$300). I have about 265 bugs, but just to be more general (generous?) I took 35 rows extra.
    In Step 2 of the wizard, I chose Time To Repair as the column to sum, and added the following conditions:
    Planned Start>=01-Mar-02
    Planned Start<01-Apr-02
    Closed after fix>=01-Mar-02
    Closed after fix<01-Apr-02
    In Step 3, I chose to Copy just the formula to a single cell.
    In Step 4, I chose the Metrics!$B$10 as the target cell to contain the value. The moment I click on Finish, I get the following error:
    Run-time error '1004':
    Unable to set the FormulaArray property of the range class.
    The end. Continue and Debug buttons are disabled. Help is of no use, as usual.
    Let me add that I have checked for the validity of all dates. But where did I go wrong? Is there any settings problem? I tried with and without data filter, calculations- manual & automatic, giving the same results.
    Please note that if the conditions are given individually or a combination of them, I am getting the results without any errors, although they are meaningless to me. It is only when I give all four together that I am facing this problem.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,660
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    In Metrics!B10 enter:

    =SUMPRODUCT((MONTH(C2:C265)=3)*(MONTH(G2:G265)=3),I2:I265)/MAX(1,SUMPRODUCT((MONTH(C2:C265)=3)*(MONTH(G2:G265)=3)))

    where C2:C265 houses the Planned Start dates, G2:G265 the Closed After Fix dates, and I2:I265 the computed duration in days.

    Note. Your data area appears to change (that is, it's not fixed). If you post 5 rows of your data including the labels like TTR and FTR, and the name of the sheet that houses your data, it's possible to devise a more effective formula(s) to use in your Metrics sheet. In order to post the sample data, go to the data sheet, activate an empty cell, type =, select 5 rows of your data, hit F9, copy what you see, and paste the copied bit in the follow up.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-10 01:44 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •