I need to calculate the average amount of days it is taking for files to be processed. Here is the example I have.
Assuming that B1=0 and C1=1 (in the # of days row)
# of Days 0123456789101112131415Total Files
File Count011712113203000000049
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message.
give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount?
attached is the start of the assesment question.
You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid.
Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received:
• If students pay the course fee less than 7 days prior to the course commencing then they receive no discount.
• If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%.
• If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%.
• If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
My sheet looks like this - example:
ID Dates CombinedAverageDaysFromID
0001 2012-02-01 ?
0001 2013-08-10
0001 2013-10-10
0402 2011-01-02 ?
0402 2013-01-05
0402 2013-01-22
0003 2009-02-04 ?
0003 2009-12-04
0003 2010-01-04
0003 2010-03-03
0003 2010-08-02
0003 2012-04-04
0003 2013-01-05
0003 2013-10-03
I need to calculate the average days between the dates for each ID? How do I do this?
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
Is there a formula to count the amount of days, for example.....a1=8/1/08 b2=8/10/08 then c1=9 days....
View 4 Replies View Relatedfigure out the amount of days in a cell.
I have dates in "A"
and need the amount of days to show up in "C"
EX:
A B C
11/24/12
12/13/12 18
So i need the code to have the "18" show up when I enter the date in "A"
I am trying to determine a formula that will tell me what the date will be 61 days and 90 days from a selected date. For example:
A1: 04/11/2014
A2: (Date 61 days from date in A1)
A3: (Date 90 days from date in A1)
Is this possible??
Different items and their respective amounts are entered in the table attached. I neeed a formula to work out the average per week of each item as shown.
View 3 Replies View RelatedPhasing Design.xlsx
We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).
We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.
So far i have the following code:
VB:
Sub phasing()
Dim SourceA As Range
Dim Weight_ResA As Range
Dim TargA As Range
Dim i As Long
[Code] .....
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.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
The example:
Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.
Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00
Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.
So the sort of result I'm looking for would be like
Assume todays date is 19-04-09
A B C
12-04-09 £5.00 Last Week 4 Payments Value £45.00
12-04-09 £10.00
13-04-09 £10.00
14-04-09 £20.00
I have been looking all over and cannot find an answer on how to do this. I work for a call center that takes inbound technical support calls. We recently added a chat support option for our end users. Our chat agents can take up to 3 chats at the same time. I need to determine the total amount of concurrent chats for each agent per day as well as the total amount of concurrent time for each agent per day. Below is a sample of my data. Notice how the start time on row 5 is earlier than the end time on row 4. I need excel to count this as a concurrent chat and then calculate the amount of time that was concurrent (in this example it would be 16 minutes). Currently I have 2000 rows of data and 30 different agents.
Start_Time
End_Time
Agent Name (Num)
6/3/13 12:13
6/3/13 12:24
14
[Code] .........
I need to calculate the monthly repayments on a loan taken out over a certain amount of years, which I can do fine.
I just cant get my head around how to calculate monthly repayments over a certain amount of years when the intrest is compounding annualy.
What I have so far:
p*(1+(r/100))^n
Where p is value of original loan, r is annual intrest rate, n is amount of years, and I am hoping I am right in saying this is the total repayable amount of the loan?
Then putting that aside I created a amortization table. (which I am certain i forgot to include compound intrest in!)
To keep it short i followed this guide for the amortization table.
and now I am so confused about if I should be using PMT, PPMT, NPER?!
I'm trying to calculate the amount of money invoiced per company per month. In column B I have the date the invoice was issued. In column E I have the Company name. In column G I have the amount invoiced.
View 6 Replies View RelatedI need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View 2 Replies View RelatedI have following data
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)
How can I translate that into averaging a range?
I'm using Excel 2003
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.
How do I put this in excel terms? ....
I want to keep track of housekeeping attendants' average productivity of rooms cleaned per day/shift for the last 10 days worked.
I need a formula which will give me an average of the 10 most recent cells with a value (0 should be counted).
I also want to have each month in a separate tab, and for the formula described above to average between two tabs should the 10 days cross-over.
I am trying to calculate the total amount of kilos for a specific date with a given time range.
As well as the average time they have been handled with in the same specifications.
Attached is a sample sheet of the info I am working with but I cant seem to get the formulas to work.
Book1.xlsx
I need to calculate the subscription amount based on the capacity of the wind mill.
The slab rates are as follows:
1. Up to 0.6 MW - Rs.3,000
2. more than 0.6 up to 3MW-Rs. 7,000
3. 3 MW up to 98MW -Rs. 7,000 for the first three MW and Rs.1,500 per MW in excess of 3 MW
4. Above 98 MW -Rs. 1,50,000
I had tried IF statement but was not successful.
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.
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.
Avg Number of Days - Excel Help.xlsx‎
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.
These functions are returning the wrong answer of zero in col GJ , I have put correct answers into col GO
They are looking at the dates in cols FP to FR , then averaging all margins
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.
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
View 2 Replies View RelatedI 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
John
3
[Code] ..........
I have a data set that looks like the following.
Description S M T W T F S
Item 1 X X
Item 2 X X
I have a few thousand rows of this. The X's could be placed in any column. I need to go through this data and determine the maximum days between X's for each row. I can't think of how to do something like this with either an equation or with VBA.
I can't get it to line up in the table above. But basically, there could be an X in Sunday and on Wednesday in one row. The next, could be every day of the week, the next could be any other combination. There could be 7 X's, 1 X, 3 X's, whatever.
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.
"Position | Date Opened | Stage 1 | Stage 2 | Stage 3 | Stage 4"
Pos 1 | 01/01/2014 |02/01/14|03/01/14|07/01/14|09/01/14
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.