I have a worksheet containing 5 columns. A start and end date, a number of days between these dates, the quarter number of the start date (i.e. 1 to 4) and then the year.
I want to calculate the average number of days per quarter so an average of days in column C, based on the details in column D and E.
How would you calculate the amount of days of a billing period for a consulting engagement by quarter when the engagement could span multiple quarters, be within a quarter or not be in a quarter at all? Project based billing
I would like to find the average number of days events take from start to finish.
My data is in 3 columns:
EventName StartDate EndDate
I have a ton of EventNames, each of which have a StartDate. But I want to calculate the average days to complete only for those EventNames that have EndDates.
So, if a cell in the column EndDate is greater than zero, then take that cell, subtract it from the corresponding cell in the StartDate column; Add all of them together and divide by the count of those rows in the EndDate column that have a value.
I need a formula to determine the average number of days between sales transactions for each item in inventory. There are several hundred different item numbers with thousands of transactions.
I have attached an example of what I'm looking for. I've toyed around with Average and If functions but can't seem to get it right.
I'm working on building a workbook to track sales progress, and I'd like to perform an analysis on some of the data with regards to sales efficiency.
To simplify: Column A = Initial Contact Date (mm/dd/yyyy) Column B = Close Date (mm/dd/yyyy)
As an example, I would like to calculate the average number of days between the Initial Contact Date and the Close Date to calculate the average number of days in the prospecting cycle. Basically, the formula should function as AVERAGE((B1-A1)+(B2-A2)+(B3-A3)...) and so on for the entire column.
I know one solution would be to insert a new column that performs the subtraction between the two dates, and then I could AVERAGE this new column. However, I want to perform similar calculations to analyze the time period between other key milestones in the prospecting cycle - rather than have a lot of extra columns in my sheet, I was wondering if there's some type of array formula that will calculate what I need.
How to determine how many days are remaining in the quarter after I provide it a specific date. Ultimately I am trying to build something where if I enter a date it will break down how much it will contribute for the remaining current quarter and for one full year (on a quarterly basis).
For example lets say I buy gumball machine today and I know that it will contribute X dollars for 2009 and X+1 dollars in 2010. I would like to break it down quarterly and have it say - you will get X for the rest of the quarter in 2009 and X for each quarter in 2010.
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
I'm trying to write a formula to return the AVERAGE by calendar quarter, IF the quarter has ended. For instance, IF Jan = 5, Feb = 20, Mar 5, Average would be 10. But, if Mar was 0 (only meaning no activity) I still need it to calculate.
The formula I started with is - =IF(A1=0,0,AVERAGE(A1:A3)
This works only if cells A1:A3 have a value greater than 0. How can I write the formula so that as long as A3 is not blank that it will return the AVERAGE?
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3-AL3.
First - can anyone explain the formula so I may understand it and possibly modify it for other uses
Second - wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3 - any suggestions on how to do this easily.
By the way, the formulas are in column AO, staring on row 4.
Column A Name of the company Column B Date Column C Day Column D Week of the Year Column E High Price Column F Low Price
My requirement is I need the average high price for the first three days of the week i.e Mon, Tue, Wed in Column I and average Low Price for the remaining days is Thu, Fri, Sat , Sun in Column J.
If in some case any day is missing then average high / Low price for the next two days is to be calculated.
I have a spreadsheet with dates running down column J, and a number running down column K. I want to average the numbers in column K for all the rows that are less than 30 days old.
I have a formula for determining if a date is less than a month old: =IF(DATEDIF(J2,TODAY(),"d")<31,TRUE,FALSE)
I'm trying to complete an excel project for work that keep track of orders from several systems as they come in. Every time I get a new order I put the name of the part ordered in column B4:B5000 and have it set to automatically datestamp that days date in column D4:D5000. The problem I'm running into is that on a separate sheet I want to keep track of the average time between orders while also skipping any blank cells and returning a 0 instead of a div/0 error if the sheet has no orders. For Instance:
Widgets 100 10/15/14
Sprockets 44 10/20/14
Cogs 60 10/25/14
The answer I would be looking for here should be 5 skipping over the blank row. I've scoured the net and tried numerous formula but nothing seems to work.
I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx
I am trying to get an average number of Networkdays where specific cell values are true. If the project Status is 'ongoing' or 'overdue' in Sheet2, what is the average Networkdays of the open projects for each project lead for Column C in Sheet1?
Sheet1 Column A Column B Column C
Project Lead Count of Projects Avg Age of Projects
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
I have a workbook with two sheets. The first has a list of job positions open, columns designated to stages in the employment process and in these columns, my staff enter the dates that they completed a particular stage.
This has a great number of entries and they are increasing and decreasing every day depening on the amount of jobs available.
On the second sheet, I am trying to set up a table which shows the average working days it is taking to complete each stage, divided into the months in which the job position was opened (i.e. for positions opened in january, the average completion working days for stage 1 was X amount of days etc...)
I have tried using =IF(AND(Logical, Logical),TRUE,FALSE) but this doesn't return any info as the logicals will always be false. I tried the OR function, but that requires only one of the criteria to be true to return a value. What I need is for the formula to return a number of days between two dates, ONLY if the opened date falls in one month.
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2. If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2. If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2. If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2. If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
I have a table with 2 date columns, and I need to subtract one date from another to give me the number of days the job has taken, but this also needs to exclude the weekends. There is no hours involved.
I.e. Start Date Finish Date Days Taken 22/Jan/2009 27/Jan/2009 4
Is there a formula that i can use to work out the days taken. there is multiple rows of information, the above is an example.
I have a start date (say 01/04/07) and I need to add a number of days to it to get the start of the next financial period. Unfortunately, I need to ignore a small list of dates (bank holidays, etc).