?
Solved

Count the number of days formula and average numbers formula

Posted on 2012-08-27
6
Medium Priority
?
726 Views
Last Modified: 2012-08-27
If you look in my sheet, there is a field called dispatch. In that column are dates I need to count the days from the current date to that date listed in the column and populate the fields below. Keep in mind the number of rows changes daily so I would need this to work dynamically.

I need to know how many :

Tickets open 1-2 Days            
Tickets open 3-5 Days            
Tickets open 6 -10 Days            
Tickets 11+ Days      

I also need to average out DISPATCH DELAY column.

Thanks in advance for your assistance!
sample-sheet-8-27.xlsx
0
Comment
Question by:Edward Pamias
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 38338274
To make it dynamic, the easiest is to turn your range into a table. Then using countif and countifs is easy. See attached file.

Thomas
Copy-of-sample-sheet-8-27.xlsx
0
 
LVL 19

Author Comment

by:Edward Pamias
ID: 38338326
Unfortunately I cant change it to a table. Would the offset function work as an alternative?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38338347
Yes it would, it's just more of a pain to manage. Your open date column is would be offset($C$13,1,0,counta($C:$C)-1,1) instead of (tbDispatch[OPEN DATE]

Thomas
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Author Comment

by:Edward Pamias
ID: 38338378
Thanks Thomas... it's not going by open date... I am counting from today's date till the dates in the Dispatch column and then getting the number of tickets for each of the fields.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38338402
OK, you should be able to us offset($E$13,1,0,counta($C:$C)-1,1) then.
0
 
LVL 19

Author Closing Comment

by:Edward Pamias
ID: 38338421
Thanks Thomas .. this should get me on the right path if any problems I will post another question.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Web hosting control panels were first developed to make it faster and easier for most users to set up and operate websites. The graphical user interface (GUI) allows users to perform tasks by pointing and clicking rather than typing highly specific…
Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question