1

So I have this output to track my tickets system that has the following columns:

1. create date 
2. resolved date
3. location (A,B,C,D)
4. item (1,2,3,4)

How do I create a formula that could give me an output of average days open of an item in a week?

For example, week 32 of the year 2015, what is the average days open of item categorized '1' in location 'A'?

This shoudl be equal (total days open of all ticket '1')/(total tickets open of item '1') assuming I already know the total ticket opens.

And (resolved_date) – (create_date) if (last day of week) > (resolved_date), this indicates ticket is resolved within report week; = (last day of report week) – (create_date) if (last day of report week) < (resolved_date), this indicate ticket is resolved after report week; = (last day of report week) – (create_date) if resolved_date = null, this indicates ticket is not resolved by now.

I am thinking of a nested ifs formula like this IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 )) but not familiar with Excel enough to construct one.

Thanks!

trouble
  • 11
  • 1
  • 4
  • Have you tried a pivot table? – Balinti Aug 13 '15 at 21:55
  • 2
    You could help yourself a lot by adding a calculated column "time to close" and then basing your calculations on that. Then look at `AVERAGEIFS()` http://www.techonthenet.com/excel/formulas/averageifs.php – Tim Williams Aug 13 '15 at 21:57

1 Answers1

0

Given what you've said, in my opinion it would be easier to break the data down into understandable steps. See the attached. I created some random data and a way to see by location and item the metric you describe at the beginning of any given work week. I'd recommend having Col A - D be the items you describe as 1-4 then make a columns adjascent E - H.

A = Create Date
B = Resolved Date
C = Location
D = Item


Formulas in each columns, row 2:
E:  =IF($K$8>B2,"Closed",IF(A2<$K$8,"Open","Not Yet Open"))
F:  =C2&D2
G:  =IF(AND($F2=$K$5,$K$8>A2),1,0)
H:  =IF(AND($F2=$K$5,$E2="Open"),$K$8-A2,0)

Make a menus tab. In column A put Locations in Cell A1, then all locations beneath it. Select the locations, including the header and hit Ctrl + Shift + F3. This will name the range of locations (Menus!A2:An), Locations. Do the same thing with Items in Column B. Now we can make some data validations. Now make a calendar tab. Put the first day of each WW in one column and the WW label in the next column. For example (12/28/2014 might be the first day of WW1). Add 7 the first date cell and drag it down. Drag the WW1 cell down to make it corresponding. Name the WW range 'WW'. Select the dates and the corresponding WW labels and name that 2-column range 'Calendar'

Head back to the page with your data. And do the following:

Location: (K3) Data Validation, List, Range =Locations
Item: (K4) Data Validation, List, Range =Items
Location + Item: (K5) =K3&K4

Work Week: (K7) Data Validation, List, Range =WW
First Day: (K8) =INDEX(Calendar,MATCH(K7,WW,0),1)

Total Days Open: (K10) =SUM(H:H)
Total Tickets:  (K11) =SUM(G:G)
Avg Days Open:  (K12) =K10/K11

Now you should be able to select the Location and item with drop downs and then a work week and get your metric.

Adam Belnap
  • 38
  • 5