Calculate Totals Based On Age Restrictions And Years Of Service

May 28, 2014

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‎

View 4 Replies


ADVERTISEMENT

Formula To Calculate Years Of Service For Next Year

Dec 13, 2012

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‎

View 9 Replies View Related

Redundancy Table To Calculate Lenght Of Service

Apr 16, 2007

i am working on a redundancy table to calculate lenght of service at a certain age and what the redundancy in years will be,

age----------------years
less than 23------0.75 per year
23 to 42----------1.5 per year
43 and above----2.25 per year

these figures only count for the years they have been that age

examples

a person who is 22 and worked for two years would get 1.5 years redundancy, 2 x 0.75

a person who is 23 and worked for two years would get 2.25 redundancy 1 x .75 + 1 x 1.5


i can do this table manually adding on to each previous but wondered if someone might have a nice formula that would calculate it automatically

i would imagine there would have to be some kind of lookup to find out how many years they have been in each age bracket

View 9 Replies View Related

Length Of Service For Service Awards

Dec 8, 2009

I have an excel spreadsheet with the column headings a1(Emp Name), b1(Hire Date[dd/mm/yy]). There around 1000 records of employees. Every month I have to generate a report for the following month, for example by the 3rd week of this December 2009 I have to extract the report of following staffs from those 1000+ records.

- those who will be completing 1 year in January 2010
- those who will be completing 5 years in January 2010
- those who will be completing 10 years in January 2010
- those who will be completing 20 years in January 2010

How shoud I do it? What should be the formula in which cells? I would appreciate if anyone could guide me with the complete formula with a very reliable way to do it.

View 9 Replies View Related

Calculate When Students Will Be 18 Years Old?

Mar 14, 2012

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 Replies View Related

Calculate A Quote Over 4 Years

May 24, 2012

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.

View 9 Replies View Related

Calculate Age In Years And Months

Oct 5, 2006

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"

View 4 Replies View Related

Calculate Day, Months & Years Between Dates

Dec 20, 2006

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?

View 4 Replies View Related

Calculate Averages Of Individual Months Over 40 Years

Jan 5, 2012

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.

View 6 Replies View Related

Calculate Compound Interest Over A Period Of 10 Years?

Mar 5, 2013

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..

View 9 Replies View Related

Formula To Calculate Years Months And Days

Sep 13, 2009

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?

View 9 Replies View Related

Calculate Totals

Dec 12, 2007

I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:

=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))

In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

View 9 Replies View Related

Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years

Feb 21, 2010

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?!

View 9 Replies View Related

Creating Catch All Booking Form And Contract System Service Based Company

Feb 19, 2014

I've been tasked with creating an updated booking system for a company which offers projects / services to the educational sector (UK Primary Schools.) I've done an alright job at augmenting their current Excel based system, but I would like to take it further so that the system is more all encompassing.

Currently, the booking form is filled in when a booking is made and the data from that is populated into their copy of the contract and our copy of the contract using simple "=" and to a certain extent, the text on the contracts is manipulated based on the booking form data using "IF" functions.

There is a basic macro which removes excess lines from the contracts if they aren't used, but that's about it. The difficulty we're having with the current system is that a lot of our projects are bespoke, happen over more than one date, and require a lot of manual manipulation in order to get them ready, which sort of defeats the object of having a 'clever' booking system.

I don't know very much about VB, but the sort of thing I would be looking to integrate is having drop down lists to select a 'project' which would load project-specific text into the contracts area. Also, instead of having to remove lines manually, I would like the data to be 'dropped in' creating rows as appropriate.

View 2 Replies View Related

How To Calculate Series Of Row Totals

Aug 7, 2014

I have a list of stores and the costs for various items.

Each store has (usually) more than one row.

Each store's items are totaled at the end of each line. Simple

What I do not know how to do is total all rows for each store, automatically. Like I said, I don't know how to explain it, so searching for it does not work out well for me.

Please see the attached sheet : How to Calculate Store Total.xlsx

View 9 Replies View Related

Calculate Totals With An IF Statement

Nov 8, 2006

I need to know how to calculate totals from a spreadsheet in conjunction with an IF statement.

I have a columns A & B below, i would like to calculate the numbers in coulmn B only if they match a specific criteria in column A.

For example: I want calculate the total of the numbers in coulmn 'B' only if column A is a '1'.

AB
24
33
12
21
35
13
22

View 10 Replies View Related

Calculate Totals In Column AB

Aug 21, 2013

I have a large spreadsheet, almost 9000 rows. I need to calculate the totals in column AB. There is a blank row seperating each group I need to total. Is there a faster and easier way for this to be done without adding the formula manually to each row? Here is a small example - I need to total the data under Total_USG

TOTAL_USG
LETTER_SMPLX
LETTER_DPLX
LEGAL_SMPLX
LEGAL_DPLX

154
162

[code]....

View 2 Replies View Related

Formula To Calculate Totals From Three Different Sheets

Jun 3, 2009

I have to calculate the totals for every individual for the past three months. I have to do this for 200 people, is there a formula i could use to do this? Eg. if a person made 50 sales in Jan, 40 sales in Feb and 100 sales in March i need a formula that calculates the grand total, which will be 190.

View 3 Replies View Related

Calculate Totals Over A Date Range

Oct 12, 2009

I am trying to calculate a total of a supplier between 2 dates. The total must fall in between the entered dates but the dates can be any given date and not limited to the dates in the supplied list. See attached scaled down example?

View 2 Replies View Related

Calculate Totals Within A Range Object

Feb 21, 2007

I have a range object called tbl which consists of a number of rows and columns of numbers.

I have another range object called e that is the total row just beneath tbl. I want to sum up the total of each column within tbl and put the result in the appropriate cell within e.

I can do it using a for/next loop as shown below but there could be 10,000 rows and a hundred columns in my range which woulod probably take forever. I know there must be another simple way to do it but despite racking my brains and trying various things I can't get it. I'll be very grateful for any assistance.

My current solution is as follows. (e is selected).

'Calculate totals and display them

For g = 1 To tbl.Columns.Count ' the number of columns
h = 0 ' Holds the column total
For f = 1 To tbl.Rows.Count - 1 ' The number of rows
h = h + tbl.Rows([f]).Cells(g).Value 'add the cell value
Next
e.Cells(g).Value = h ' Display the column total
Next

View 8 Replies View Related

Calculate Totals At The End Of Filtered Values

Mar 18, 2007

I need to calculate a subtotal like value on the last row of a filtered region.

e.g.

description year sum
name1 3 yrs ago 200
name1 2 yrs ago 230
name1 last year 250
name1 forecast 220
name1 forecast value 1400

name2 3 yrs ago 200
name2 2 yrs ago 230
name2 last year 250
name2 forecast 220
name2 forecast value 1400

How to put a value on the last row of filtered column. let's say that above list is very little in comparison with the real list. so, let's say that i look up for name36, witch begins on row (36*5) -> 5 is the values of the year column; after autofilter with "name1" the very left column with row index become blue and show rows from 1 to 5, than, next row is the last non-empty row.

View 9 Replies View Related

Time Sheet - Calculate Bi-Weekly Totals

Feb 25, 2014

I have a time sheet that I can figure the daily OT and DT on, but how to calc the the Bi-Weekly totals.

I can only have 80 hours per week, the rest needs to be carried to the OT field, but I can only have 40 of OT per week and the rest needs to be carried to the DT.

View 1 Replies View Related

Calculate Weekly Totals From Daily Figures?

Mar 4, 2012

I have a spreadsheet called PRODUCTION, where information is entered daily. I need weekly totals (mon - sun) to be extracted into another worksheet.

View 7 Replies View Related

Compare Cells And Use A Range Of Values To Calculate Totals

Apr 16, 2007

I want to compare cells and use a range of values to calculate totals (i.e. count if cell A1=boy and cell A2 equals either "fat" or "skinny")? Also, I tried using wildcard character "*" in the SUMPRODUCT formula, which didn't work (i.e. if cell A1="boy*" would return at TRUE if the cell contained "boy", "boys", "boy and girl", etc.). Is there a way to include wild card characters in a SUMPRODUCT formula like the ones described above?

View 9 Replies View Related

Adding Years To A Date (taking Into Account Leap Years)

Aug 28, 2007

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 Related

Filter Data Based On "Service Type"

Dec 7, 2009

I'm trying to use the following formula to filter data based on "Service Type" and it seems to work on one of the service types but not the other. here is the formula

=IF(LEFT(A5,1)="0","Both",IF(OR(A5="SERVICE",A5="REVENUE",A5="REVENUE",A5="CIC",A5="RAO",A5="CARRIER NAME",A5="TYPE",A5="RECORDS",A5="INCLUDING TAX",A5="BEFORE TAX",A5="FEDERAL TAX",A5="PROVINCIAL TAX"),"Both",IF(LEFT(D5,3)="REG","Table1",IF(OR(D5="900"),"Table2",IF(A6="",IF(B6="","XXX","TRUE"),"XXX")))))

Notice the highlighted red text, this is where I'm trying based it on "Service Type". I have also attached on example workbook, the items highlighted in green should marked as "Table1" and items highlighted redish orange should be marked as "Table". Wierd thing is that I use a similar formula in another workbbok and it seems to work fine.

View 2 Replies View Related

Forecasting Based On Two Years Of Data

Nov 21, 2011

Have to forecast/create a trend for the current year based on the data available for last two years. Below is the format of data. The current year data is kept blank as the data needs to be filled based on forecasting/ trending.

30-Apr-0931-May-0930-Jun-0931-Jul-098/31/20099/30/200910/31/200911/30/200912/31/20091/31/2010
2/28/20103/31/20106075443580901511256025304030-Apr-1031-May-1030-Jun-1031-Jul-108/31/20109/30/2010
10/31/201011/30/201012/31/20101/31/20112/28/20113/31/2011100150756070859611045663544
30-Apr-1131-May-1130-Jun-1131-Jul-1131-Aug-1130-Sep-1131-Oct-1130-Nov-1131-Dec-1131-Jan-1228-Feb-1230-Mar-12

View 3 Replies View Related

Display Number Of Years Based On A Date

Mar 28, 2009

Right now, in a column I would like to display an number (length of employment) based on the hire date.

In one cell the employee's hire date is entered. In a column of other cells the pay period ending date is displayed and in another column the length of employment is displayed.

Example:

03/17/2000

03/01/2009 8
03/15/2009 8
03/29/2009 9
04/12/2009 9
04/26/2009 9

How would I create a formula in the length of employment cells that would indicate the correct number of years based on the hire date and adjusts when the pay period date passes this hire date?

View 2 Replies View Related

Conditional Formatting Based On Number Of Years From Now

Dec 17, 2013

I have a list of 2000 pieces of inventory. Only the year of purchase is associated with each one, not a specific month or day. I'm trying to apply conditional formatting so that...

Less than 2 years old = green
Between 2 and 4 years old = yellow
Between 4 and 6 years old = orange
More than 6 years old = red

I've been trying =TODAY()-365 (365 replaced by number of days in the above named ranges) but the cell only changes to the color of the most recently applied formatting rule.

View 2 Replies View Related

Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month

Jan 28, 2010

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved