Calculating Person Age In Excel?
Jul 25, 2014I'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())
ADVERTISEMENT
Calculating Whether A Person Has Upgraded Between Lists
Feb 10, 2014I am trying to write a formula that isn't a gigantic IF statement to calculate whether a person has upgraded their type of house or not within a time period.
The below are the names of the types of housing. I have two of these lists, the first is from the beginning of the time period and the second is from the end of the time period:
Pucca - Normal
Kuchha - Strong
Pucca - IAY
Kuchha - Medium
Kuchha - Weak
The best option on this list is Pucca - Normal and the worst is Kuchha - Weak.
A person can start at any option on this list and end on any option. I only want to measure whether their type of house has been upgraded (i.e. from Kuchha - Weak to Kuchha - Medium). I do not need to calculate whether they have stayed the same or been downgraded.
If the person has upgraded I want to display the value of 1. If they have stayed the same or downgraded I do not want to display anything.
An added complication is that if they have upgraded to Pucca - IAY I want to also do not want to show anything.
Finding All Occurrences Of Person's Name And Calculating Hours Worked
Jan 28, 2014I'm trying to create a macro that will take name values from a list (Last name, First name) and then find these particular names in a long list of names in another sheet. Once all the occurrences of the name are found, the macro would need to add up all the hours that the person has worked during a given month. For example, I have Jane Doe and John Smith on my shortlist, the macro would have to take the name of the first person, Jane Doe, find all the occurrences of that name in the second sheet that contains names, dates and hours worked, then add up all the hours worked by that person and return the total hours worked value for that parson. Then take the name of the second person, John Smith, and do the same, and so on. Is it possible to do it with macros?
View 2 Replies View RelatedExcel 2010 :: File Sharing - Link 4 Spreadsheets Where Information Will Be Updated By One Person
Apr 21, 2013I have a Excel Spread Sheet which has been created, there will be 4 people in 4 different locations who will have access to this spreadsheet and will need to update/amend it through "Dropbox", my question/problem is, how can I link the 4 spreadsheets where information will be updated by one person, but will update all 4 sheets at the same time.
Is this possible and how do I go about it?
Calculating Age In Future Using EXCEL?
Jul 1, 2014I 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.
Calculating Excel Cell Only Once?
Feb 1, 2013I 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
Calculating Beta In Excel?
Jun 21, 2002Is 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 RelatedCalculating Date And Time In Excel?
Sep 14, 2011I 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]....
Calculating Difference In Time Using Excel
Mar 7, 2013I 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] ........
Last Date By Person
Jan 4, 2013I have a sheet that tracks when people are paid. Each time a person is paid, another entry goes into the sheet with his/her name, pay amount, and date they were paid. So, a given person could have up to 50 lines devoted to them, each with a different date associated with it (the list is sorted by name).
What I need to do is figure out the most recent pay date for each person in the sheet. I would prefer to avoid vba, but experimenting with native excel functions has gotten me nowhere...
Excel 2003 :: Cells Not Calculating Correctly
Dec 19, 2013I 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.
Macro Or Excel Function For Calculating Average
Apr 16, 2014Excel 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
Excel 2007 :: Formulas Not Automatically Calculating?
Jul 3, 2012Using 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.
Excel 2007 :: Calculating IRR Using Rate Function?
Aug 11, 2012I 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.
Calculating Average Talk Time In Excel?
Oct 2, 2012how to calculate average talk time in excel.
View 9 Replies View RelatedShow Please Wait - Searching When Excel Is Calculating
Jan 26, 2009I 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?
How To Change First And Last Name When Person Has Middle Name
Apr 4, 2014I have a worksheet with some names in a column with a persons first name then the second name and I would like to place the names in a different column I can have this to work ok with the following formula
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))
This works ok but I have people with three names like Peter May Jones
When I have changed the name I would like to have (Jones, Peter May) but I get (May Peter Jones)
Also the colon I have after the first name gets put at the end of the name not after the last name.
Returning The Person With The Highest Value
May 31, 2007I have some data as below and I want to return the name of the person with the highest value.
Col A Col B Col C Col D
John Peter Paul Luke
3 4 7 8
Random Item Per Person
Dec 19, 2007I have a list of order numbers and sales persons associated with those order numbers..
I was trying to come up with an idea of how to enter a Sales Persons Name in a field and have a random order number based on their name populate so that I can audit that particular order.
Can this be done in excel with a vlookup formula or something to that affect?
And if so, how?
Order # Sales Person
256796 Judy
248986 Brian
897655 Fred
245466 Judy
364599 Tomas
Since Judy has more than 1, I would like to just radomly pick one of those... Now, just a little side information, my actual sheet has approximately 3000 orders with 52 sales reps...
Summarize Results Onto 1 Row Per Person
Feb 2, 2009I am trying to write a series of macros to automate the creation and population of a summary tab for transmission of patient results to our Laboratory computer system and have hit a bit of a brick wall. One of our analysers creates an Excel workbook for each analytical run with each patient sample analysed on the run assigned to a separate worksheet. These worksheets contain the patient ID (lab number) in cell C1 with results for the different analytes in cells F7 to F56. We need to be able to present this data with each patient having a row to themselves (i.e. Patient ID for patient 1 being in cell A2, test result 1 in cell B2, test result 2 in cell C2 etc, then patient 2 taking row 3 etc). Row one is populated with the field Sample name in cell A1 and the cross-referenced test IDs running across the rest of the row. As analytical runs can contain varying numbers of samples we need the Summary tab to be able to cope with a indeterminate number of worksheets.
View 2 Replies View RelatedView Details By Person
Dec 27, 2007I am creating a personal expense tracking sheet for the upcoming year. I have each day of the year going down as my rows. My column headers are various expenses I expect to have, such as a phone bill and internet bill. I want a way to pick which expenses (columns) are visible. I expect this to aid in the inputting of expenses and the reviewing of expense types. I know of course, I could just click all the columns I don't want and hide them, but I'm looking for a quicker way if possible.
So, if I want to record the phone bill, or just look at the phone bill expenses alone, I could use a drop down which listed all my column headers and check which one(s) I wanted to view. I thought of like the way a pivot table option looks to pick what you want to view /shrug.
Maybe a drop down is not the way to accomplish this, and maybe my spreadsheet should be laid out differently.
Excel 2010 :: Calculating Average Elapsed Time
Apr 3, 2014In 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.
Excel 2007 :: Calculating Man Hours Committed To Breakdowns
May 7, 2014I'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
Excel 2007 :: Calculating Sales Close Rate?
Jun 13, 2014I 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 RelatedExcel 2007 :: Calculating Innings Pitched From Other Worksheets
May 12, 2009i 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.
Excel Query - Calculating Result Based On Three Possible Factors?
Jan 9, 2013I 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?
Excel 2013 :: Working With Tabs And Calculating Across Multiple Ones
Jan 6, 2014I 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 RelatedExcel 2007 :: Macro Not Calculating Unless Value Entered Into Formula Bar
Apr 12, 2014I'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] .....
Excel Formulas For Calculating Yearly Rental Income
Jul 18, 2011I 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 RelatedExcel 2000 :: Find Duplicates Then Calculating Dates?
Aug 28, 2013I 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.
