I am trying to find a formula which will show the date of a potential student on a certain date e.g 1st August 2014
Cell A2 has the students date of birth
Cell B2 has the students current age
I would like cell C2 to show the students date of birth on the 1st August 2014.
I think I may have over engineered this formula.
$F$40 = Cell with Event Date
=IF(WEEKDAY($F$40,2)
So I have some stats I have been tracking for NHL hockey games. I want to find a formula that is predictive of future outcomes, based on whether or not a team has a statistical advantage. Obviously, there are so many other factors, and there is no golden formula to accurately predict the winner of ever game. I want to find maybe a 70% success rate here. I want to be able to plug in all of the stats, and essentially have it tell me who statistically should win the game.
I have 11 independent variables (Stat A, B, C, D, E, etc.). I have a running total of how often the winning team had the stat advantage for each of these, and I think that should factor into the formula. My dependent variable is whether the team wins or not. how to do that with multiple independent variables.
I have a cell that shows the PENDING time from a future event date/time vs Today
eg.
Cell L4 shows the future date/time as 2/2/2013 09:40:00
Cell AM9 shows the current date/time as 02/02/13 09:36:01
Cell N2 contains =IF((L4-AM9)>0,(L4-AM9),"CLOSED")
which either shows the time before expiry as hh:mm:ss or closed
There is another cell (say O2) which is a link to another workbook which either shows NO or YES
What I want to do is to populate another cell with the DELAYED TIME between when cell N2 changes to CLOSED and when cell O2 changes to YES
But I only want to to populate this cell once, at the moment it keeps being updated from Today
how to populate a cell ONCE ONLY
Is there a beta function in excel (for a stock vs. an index)? If not, whether the slope function does the same or not.
View 2 Replies View RelatedI'm looking for a formula to calculate the age of some one. I used a formula but it gave me a #value! Error
20/05/1936 (E4)
=YEAR(E4)-YEAR(TODAY())
I am trining to calculate SLA times based on P2, P3 & P4 calls, the only issue is with P2's as the SLA includes the weekend, how to include the weekends into my formula?
AE2 = Priority
U2 = START DATE & TIME
V2 = END DATE & TIME
[Code]....
I manage a team in a call centre and deal with lots of reports on excel.
I am dealing with a report on a daily basis that i need to use in order to calculate the total idle time that each of agents have. There are roughly 170 agents.
After I have formatted the cells to hh:mm:ss, the obvious thing to do would be : =(B1-A1)
Is there a way to write a macro (or anything) in order to reapply this to the report on a daily basis? Bearing in mind that this has to be done daily for 170 individual reports. They work a 8am - 5pm shift. So there is an entry every time they make a call.
Agent Name
Extension
Initiateddate
TerminatedDate
MSGR_nmasango
9363
28/02/2013 8:06:47 AM
28/02/2013 8:08:54 AM
[Code] ........
I am working in Excel 2003. I consider myself a little more experienced than a beginner, but not quite an intermediate. My problem is, I am doing percentage changes comparing the average year-to-date data of 2009 through 2012, and then I am attempting to get the percentage change of the current year-to-date data to the average of the aforementioned years.
I took an average of these numbers, that are pulled from other worksheets. The cells are set as "General".
112241 = 1.833333333 or rounded, 2 divided by 1 (the current year to date total), the result is -45%, which should be -50% decrease. The percent cell is set at percentage.
It appears the cells are not automatically rounding up.
Excel function or macros for calculating the average of waterSD column where the TT column is less than or equal to 100(red markings). functions like IF TT IS LESS THAN OR EQUAL TO 100, THEN CALCULATE AVERAGE OF WATERSD....
I had done manually on the right side in the attached excel sheet. only those yellow markings.
Because already I have some macro which do this process but it is not accounting for the TT column less than 100. it starts from 200 TT values. but i need to include 100 TT also. That's is where now we have yellow markings. I will provide you the macro code if you can edit that where it will start calculating form 100TT value, it will be really great. The code is,
[Code] .....
Attached File : average for watersd OF 100TT.xlsx
Using Microsoft Excel 2007 and all of a sudden, my spreadsheets are not automatically calculating the formulas. It does not matter if I have other workbooks open or not. I still get the problem. It does not happen 100% of the time to make it even more complicated.
- Calulation set to auto in Excel Options.
- No VBA functions being used. I can the worksheet summing 1 + 1 and get the error periodically. It does not have to do with the spreadsheet being too complicated.
- Even if I can hit Ctrl-Alt-F9 to force the formulas to calculate, it won't work.
I am using excel 2007. I am trying to calculate the internal rate of return, without creating an array. I figured out that I can use the rate function to give me the same answer as the IRR function if I have one payment stream of say 36 months @ 1000. Where I am an running into trouble is if I have second payment stream of 6 months extension of 1500. Rate # pay 36 amt 75 PV = 5000 FV 500 , How to handle the the last 6 months ?
This is the situation
asset costs is 5000
monthly lease is 75 for 36 months
extension for 6 months is 125
end of lease buyout is 500
what is the internal rate of return without creating an array.
how to calculate average talk time in excel.
View 9 Replies View RelatedI have a workbook with alot of results that employees will need to wait for results to show.
While it Calculates.. can we get a cell to show "Please Wait - Searching" so they know its actually doing something for them?
In our sql server table, we have a field of type DATETIME. This field is populated by a vendor product with the elapsed time of an event.
We created a SQL Server view of this column and other columns of info joined together from several tables.
Then we create in Excel 2010 a new spreadsheet with a data connection to the SQL Server, instance, and view that we created.
Excel 2010 displays each of the columns in our view.
The elapsed time column appears as "dd hh:mm:ss". However, when we click on a cell to look at its formatting, Excel lists it as general.
We want to calculate the average time in this column.
We go down below the data, click on a cell, and insert =Average(C2:C17215) and we get an error saying dividing by zero.
We have 17,000+ rows of data, so there is no divide by zero math going on here. Each row in this column has data, so there are no blank cells.
We have spent some time web searching. We then tried various methods of calculating this. Nothing has worked. In most cases, we get the divide by zero. We tried some solutions that mention using an array forumula, but that just changed the error to an invalid value error.
I'm creating an engineering report and were trying to capture the number of man hrs we are committing to breakdowns the section I'm interested is shown below:-
CraftJob Start TimeJob Finish Time
PL RD02.45 03.15
There are two personnel (shown as initials) in the craft cell with the start and finish times of the job. Can I determine the number of personnel by counting the sets of initials i.e. PL + RD =2 then multiply that number by the time worked in this case 30mins (this will vary) so time committed would be 2*30=60mins.
Excel 2007
I want to set up an Excel spread sheet where I can put my total number of sales appointments for the month and the percentage of those I closed. I've done this before, but for the life of me I can't remember the formula I used & I know it's a very simple one! I'm using Office 2007.
View 1 Replies View Relatedi have an excel 2007 spreadsheet where i keep baseball stats. On the pitching side have a column for the pitchers innings pitch. The data kooks like this:
1.0
1.1
1.2
where the numbers after decimal points represents thirds of an inning.
In my spreadsheet i have 4 workheets of different divisions that have a breakdown of each team the team i am keeping stats for plays.
i manually enter the innings pitched in the totals for the season as i cannot figure out how to add these numbers from the individual worksheets to calculate the right number. once the number after the decimal point is greater than two than another full inning is recorded not a continuation of the number.
Example pitcher has already pitched 2.2 innings and he pitches 1.2 innings today making a total of 3.4 which should really be 3.1 one last one pitcher has pitched .2 innings and pitches .1 today making a total of .3 innings pitched but it should be 1.0 innings pitched. proper formula to sum the correct totals in the totals worksheet. i am at a loss and ihave been working on this problem for a little while with no luck.
I am working with data and I am trying to identify people that have moved departments using the following criteria, a person are classed as a mover if their:-
Manager AND Dept ID have changed
OR Job Code has changed
OR Status has changed
I have been trying to come up with a formula but just can't get one to work?
I am working on a commission report for our veterinarians. I have a tab/sheet for each year, with a column for each month, a row for each of the veterinarian. Each vet is on the same row, year to year. I need to be able to calculate the %, year to date, for each vet, across the past 2 years. I can do the auto sum, but do not know how to do percentage. I have row 3, column b3 thru m3. I want to do 2012 and 2013. Each sheet is name the year.
View 1 Replies View RelatedI'm trying to convert the values entered by users into specific cells into a specific percentage. When I use this macro in Excel 2010, I can enter the value to be calculated directly into the cell and the macro runs automatically. When I open the file in Excel 2007, the macro does not calculate properly unless the value to be calculated is entered into the formula bar.
For example, in Excel 2007, if I enter '30' in cell E11, it should come out as '20%'. Instead it comes out as '0.2%' unless I enter '30' into the formula bar, which then calculates properly. It is far more efficient to be able to enter the value directly into the cell.
[Code] .....
I am trying to get the formula for calculating yearly rental imcome. The range is 10 years and the interest is 34%. The first year payment is 42,000 and the 10th year payment is 56,000. I can't figure out how to do the other years. The principal is 325,000 and the sale value is 425,000.
View 4 Replies View RelatedI record all jobs that come into my department. Column A contains the Job reference. Column B Contains the date that the job arrived and Column C contains the date that the job was complete.
The job can be split into several sections and can arrive on different days and the sections can be completed in different days.
I am looking to create a table that will show how long the complete job took to complete, so I am looking for the earliest date and the latest date for specific job reference numbers.
An example of my data is:
Column A Column B Column C
Ref No Date In Date Out
2013-0055 01/03/2013 25/03/2013
2013-0061 01/03/2013 03/03/2013
2013-0061 02/03/2013 20/03/2013
2013-0055 07/03/2013 28/03/2013
2013-0061 08/03/2013 19/03/2013
From the above data I am looking to create a table that will show
Ref No First Date Last Date Number of Days
2013-0055 01/03/2013 28/03/2013 28
2013-0061 01/03/2013 20/03/2013 20
I am using Excel 2000.
I need to calculate a Stdev and Average based on a specific range. However, my range is not constant in the time. For instance, today I may need to calculate these functions based on 30 numbers, but tomorrow on 25 or 15 or 50...
Is there a way to automate this process by changing the number of data points to include in the range in one single cell and avoid manually adjusting the range in accordance with needed data points?
In other words, I would like to change the value, let's say in Cell A1 from 30 to 50 and then Excel would re-calculate the StDev and Average based on 50 data points and not 30. Is that possible?
Using Excel 2013.
I have two sheets in a workbook.
Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result
Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:
Mod 1
Mod 2
Mod 3
Mod 4
Text 1
0
0
0
0
Text 2
2
0.5
0.25
0.25
Text 3
7
1.75
0.875
0.875
What I want to happen is that the Result field value on table one is the result of:
Distance*(Modifier 1 + Modifier 2 + Modifier 3 + Modifier 4)
For example:
On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:
6*(7 + 0 + 0 + 0)
This might be exceedingly simple, but I just cannot wrap my head around how to do it.
I have a dataset storing 6 numbers in a row (where each number range from 1 to 50 and no duplication of number within each row). Totaling there is 1000 rows ....
View 9 Replies View RelatedI am trying to take scheduled start time and scheduled duration in h:mm and auto populate the scheduled minutes in the corresponding time blocks.
Example:
Schedule ID
Start Time
Duration
8AM
9AM
10AM
11AM
12PM
1PM
2PM
3PM
4PM
[Code] ....
The first 3 columns are derived from a data table and I am trying to auto populate the minutes scheduled in the time blocks using conditional formatting or some other mechanism.
I'm using Excel 2010
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
View 9 Replies View RelatedI am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)
I am using Excel 2003 and my data looks like this:
Sent Out (A) - Received (B)
01/06/2010 13:00 - 30/06/2010 13:00
02/06/2010 13:00 - 16/06/2010 13:00
09/06/2010 13:00 - 10/06/2010 13:00
21/06/2010 13:00 - 25/06/2010 9:44
23/06/2010 13:00 - 25/06/2010 10:56
23/06/2010 13:00 - 29/06/2010 13:00
I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.
Excel ( 2010 ). I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.
proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).
F2 = 150
G2 = Q2
H2 = 10%
I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. I have attached an example.
