I have a spreadsheet with my students that needs to find the date a person will be 18. based on their date of birth. I have =B27+(365.25*18) where their DOB is in B27. I have had several instances where it is correct and several instances where it is a day off.
View 4 RepliesI have a dataset of students' grades (see sample below) which I need to compute the aggregates for.
Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.
Rules for computing aggregates:
1st subject: EL or HMT
2nd subject: H1 or H2
3rd subject: M1, M2, S1 or S2
4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used)
5th & 6th subjects: Any other 2 not previously used
I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.
Writing a single nested IF statement function to calculate the grades of the students the following rules:
If the student grade isHis/her letter grade is
Greater or equal to 90:A
Greater or equal to 80 and less then 90:B
Greater or equal to 70 and less then 80:C
Greater or equal to 60 and less then 70:D
Any other grade:F
I need to calculate a quote over 4 years but the first and last years are not full 12 months.
On Row 20 are monthly costs for each year. The start and end date is in Row 3.
I need to find a formula that can calculate ANNUAL TOTAL for the all these year in Row 22 populated automatically, not just manually times the months in each cell.
Here is a sample file.
I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.
=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"
I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.
However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.
I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').
I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?
Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?
The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? This is the formula that I have in E2
[Code] .....
Attached File : VACATION DAYS ACCURED.xls
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.
I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.
How to average a particular month over all years like so:
{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}
But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.
I am looking to calculate compound interest over a period of 10 years.
I am looking at putting a lump sum in at the beginning and contribute in monthly installments for the entire 10 years. How would I go about this.. I know there are formulas there, however I'm not a financial person at all..
I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"
C7 3/24/04
D7 1/4/08
What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Book10.xlsx
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 have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies View RelatedA1 has column heading Name.
B1 has column heading Credits.
C1 has column heading Rank
A2 has student's name and B2 has his Credit value
Row 3,4,5 are blank.
A6 has a student's name and B6 has his credit value.
Row 7,8,9,10,11 are blank.
A12 has a student's name and b12 has his credit value. So on so forth.
All I need to do is rank those students in C2, C6, B12 so on so forth.
Its taking a lot of time and I am not getting any result. I tried reading about rank but it tells about asc/desc order, which I dont understand how it fits in above example. I just need rank of a student at a particular cell.
i have a spreadsheet with a list of students in it and next to it i have a column with their current score and the letter equivilant (A*-U)
so say
joe bloggs 615 B
Joe bog 750 A*
etc
is there a way if I list the grading boundaries for it to work out how many marks are required for the student to reach the next grade?
boundaries are
A* 728
A 669
B 610
C 554
D 467
E 383
F 298
G 214
U under 213
I have a problem with counting the number of students assigned to each professor and determining the number of students who have passing grade. Let say that the passing grade is 80.
View 9 Replies View RelatedI am working on an excel master marksheet which will be distributed to many teachers to fill in their students marks. How can I enter an average function that can work out the mean for those different classes. For example I'd like the main average function to be like this =average(d5:d50) but if a teacher has thirty students in his list the mean will be only limited to the marks of those thirty students. Same thing I want for the mode function and the median function.
View 7 Replies View RelatedEssentially I'm in charge of managing schedule for 5 students, I would like to get them to meet during their free time (free for all 5 of them)
1. Schedule has to run from 8 am to 9 pm.
2. I have all 5 individual schedules.
I want to combine all 5 into one so everyone can see their free/occupied times, it would be extra useful if I can modify individual schedules (One student's work schedule changes bi weekly).
It has to be able to show who is occupied at which time and free time for all of them. (color coding each of them would be amazing also, granted I can show multiple clashes with gradient or something similar)
I understand it involved creating individual spreadsheets and a master sheet but how to go on about it.
How to return ranking based on students' score?
STUDENT SCORE Rank MAY 11255 4 JACK 5262 5 TIM 432525 1 STEVEN 352332 2 HUGO 32232 3 FERNANDO 5153 6 SANDY 4556 7 SARAH 2265 8
How to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
I need to find out over aged students by date of birth - now()-age started to school: DOB 8/16/2000 , Now() Age started school is 8/30/2005. Student is in grade 1. should be age 6, however, he is 8. Answer should be a little over 2 yrs.
View 2 Replies View RelatedI am working on a retirement calculation sheet. For retirement calculations the employees need to figure out their highest yearly income for three out of their last ten years. The three years can be based on any consecutive twelve months but each of the three years must be the same months, (example, Jan thru Dec, or July thru June). The three years cannot overlap.
Here is what I have so far. Column A, paydate. Column B, amount. Column C, year to date amount. Column D, amount for past 12 months.
I would like to list the three highest years in the first 3 rows of Column E.
Any help is greatly appreciated.
Pay Date Gross Year to Date Gross
Past 12 Months Highest 3 Years 08/10/06 3,594.58 3,594.58 3,594.58
08/24/06 3,332.79 6,927.37 6,927.37
09/07/06 2,595.59 9,522.96 9,522.96
09/21/06 2,457.36 11,980.32 11,980.32
10/05/06 2,457.36 14,437.68 14,437.68
10/19/06 2,457.36 16,895.04 16,895.04
11/02/06 6,212.51 23,107.55 23,107.55
11/16/06 3,206.09 26,313.64 26,313.64
11/30/06 4,104.56 30,418.20 30,418.20........
how to change years in a row after i put in a year in K i have add the file
View 2 Replies View RelatedI need to calculate YTD sums using the data that spans three years. I am using
SUM(B2:INDEX(B2:AC2,MATCH($B$10,$B$1:$AC$1,0))). This works fine if all of my data is contained in one year. However, when my data spans multiple years it does not. For example, if I have the following and today is April 2007 I want to calculate Jan-April 2007.
Labor Type09/0610/0611/0612/0601/0702/0703/0704/0705/0706/0707/0708/0709/0710/0711/0712/0701/08
Bus External2404526907569511,2018808059439237781,1216191,0639378001,156
Bus Internal1,4412,7942,9992,5914,0093,9444,2714,3254,7593,5493,0172,4371,9262,9722,3711,7582,931
IT External1,3483,3993,8653,8586,0247,75510,11110,40911,86911,42311,29710,4878,32713,13412,96912,41917,265
IT Internal3,3537,7428,7107,88412,73112,74614,91114,66215,30413,80513,13511,8248,63511,7728,8876,80911,283
Staff Aug12138195169195408796641626340403428469675604395637
I have 2 dates..
09-feb-09
15-dec-09
Is therea way to calculate the number of years? this is equivalent to 0.27777 years.
Is there a way to get this with the datedif or another functiom?
I have list of dates:
e.g: D/M/Y
1/03/1997
22/05/2005
13/09/1945
I want a new list that just shows the year and is formatted as a number
e.g:
1997
2005
1945
Is there a way of doing this without doing it manually, I have 20,000 observations.
The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference
View 2 Replies View Relatedi am trying to make and age table to see if a player is too old by years, months and days all i can do is Years i need something like the following
1/10/2009 cut off date birthday is 8/5/1991
all i can do is the following
=YEAR(A3)-YEAR(A2)
Please refer to the attached screenshot of my working spreadsheet.
I'm attempting to concatenate lines 86 and 87, month and year together.
The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"
I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.
View 2 Replies View Related