Subtract X Amount Every Y Years
I am building an investing strategy model and am looking for a fomula or combination of formulae that would subtract an amount (lets say a 100) every so many years (lets say 10). Data is set up horizontaly, i want to be able to set how much will be subtracted and how often in a "control panel" next to other inputs and variables. I am not proficient in Macros and most often have trouble with them so if its possible to solve this without using any that would be great.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Reference Cell & Add Amount If Positive & Subtract Amount If Negative
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do: I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
View Replies!
View Related
Calculate The Monthly Repayments On A Loan Taken Out Over A Certain Amount Of Years
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 Replies!
View Related
Formula To Subtract Invoices From Total Amount Available
I have 2 tables, one with invoices, the other with purchase orders. I would like to have a "PO Amount Remaining" column on the invoice table that looks up the PO listed on the invoice with the PO $ amount on the PO table. Once it is matched, I would like to subtract the total invoices to give me the amount of the PO that is left. Basically I would like the "PO Amount Remaining" column right now to have $4,200 listed in each row for invoices 1 & 2.....
View Replies!
View Related
Best 3 Of The Last 10 Years
I 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........
View Replies!
View Related
Datedif- Years
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?
View Replies!
View Related
Printing To Labels For Years
I created a wedding list with a bunch of fields for each household: first name person 1, second name person 1, first name person 2, second name person 2, street #, street address, apartment, city, state and zip. Then I realized that I probably needed to combine fields for each line of the address so I created 3 combined fields: 'combined 1 ' that looks like this "joe smith & patsy cline," a 'combined 2,' that looks like "14 jones street, #3," and a 'combined 3,' that looks like "New York, NY 10037." I haven't printed to labels for years and when I did some quick research via the help function on excel it said to print labels via word...which worked fine until i got to this section: " In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help. " I have no idea what this means, but here's what I need to figure out: " how do I print labels using just 3 of 8 existing columns in my spreadsheet, i.e I just want to use combined 1, combined 2 and combined 3 for the 3 lines of the address. My first instinct was just to cut the contents of the combined fields into a new spreadsheet and just do it taht way, but I created the combined fields using a formula which relates to the other fields........
View Replies!
View Related
Changing Dates To Years
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.
View Replies!
View Related
Advanced Filter Between Years
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
View Replies!
View Related
Dates, Number Of Years
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 Replies!
View Related
Concatenating Months And Years
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"
View Replies!
View Related
YTD Sum Across Multiple Years
I 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
View Replies!
View Related
Calculating Days,Dates And Years
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
View Replies!
View Related
Counting Number Of Years Between To Dates
I have a list of people with birthdays that needs to be checked against TODAY to determine how old people are. If I subtract the two fields from one another, I get number of days. But is there a way to convert into years? In my attached example, I'd like column C to display 3 (age for person).
View Replies!
View Related
YEARFRAC & Leap Years
It looks like a pretty silly problem but it's driving me mad. The yearfrac function takes a two dates and a basis. Now assume basis = 1 ( Act / Act ) and two cases: 1) 06-Jan-06 -> 06-Jan-08 ( 730 days ) = 1.998175182 =>> divisor = 730/1.998175182 = 365.33333 2) 06-Jan-05 -> 06-Jan-07 ( 730 days ) = 2 =>> divisor = 730/2 = 365 In case 1) 2008 is a leap year ( but the "leap" has yet to come! ). Can anyone explain me the logic behind? I suppose the extra day is divided by 3 but where is the "Actuality" of the divisor?
View Replies!
View Related
Calculate Age In Years And Months
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 Replies!
View Related
Countif- Sheet That Contains Dates Going Back A Few Years
I have a sheet that contains dates going back a few years. I am trying to use the countif function to count the different sites in column B according to date. eg I want to be able to find out how many jobs for PHO1 were created between todays date and 7 days ago, this can go into column C, In column D I need to find out how many jobs were created by PHO1 between 7 and 14 days ago etc etc....
View Replies!
View Related
Formula To Calculate Years Months And Days
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 Replies!
View Related
Adding Years To A Specific Year And Then Rounding It Up
There are two columns in an excel sheet, one is date of birth and other is date of reteirment looks somewhat like this: D_O_B D_O_R 5-Mar-53 31-Mar-11 30-Jun-57 30-Jun-15 20-Jun-51 30-Jun-09 2-Feb-55 28-Feb-13 2-Jul-51 13-Oct-55 1-Sep-51 7-Jul-54 14-Mar-53 3-Aug-50 3 0-Sep-13 Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column. D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)
View Replies!
View Related
Display Number Of Years Based On A Date
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 Replies!
View Related
Offset Function For Cash Flows In Different Years
I have got three period like this Period: 2010 2015 2018 Cash: 100 200 300 I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc... The three periods described above will be changeable, so the cashflows will adjust accordingly...
View Replies!
View Related
Filter By Years In Dates & Other Criteria
I have mutliple non-contiguous columns that are SUPPOSED to contain dates. If the particular item for that coulmn doesn't require a date then "waived" is typed; others may be blank. I want to apply a filter that will tell me if any one of the columns YEAR is older than "2004". I'm really bad at formulas to bare with me. I inserted the following formula: =IF(OR(YEAR(N6)<=2004,YEAR(V6)<=2004,YEAR(X6)<=2004,YEAR(AB6)<=2004,YEAR(AD6)<=2004,YEAR(AF6)<=2004,YEAR(AI6)<=2004),"EXPIRED","GOOD") The problem is that if the cell is blank or if contains "waived" it either produces "#value!" or it reports "True" when it is actually "False". I'm not sharp enough to know how to account for those two conditions in my formula.
View Replies!
View Related
Time Elapsed In Years, Months & Days
I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet is opened, based on the current date, to calculate how many years, months and days have elasped yy mm dd since the start date... My date formula knowledge is a little limited.
View Replies!
View Related
Highest 3 Year Average Over A Span Of Ten Years
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years). Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes. So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...). I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.
View Replies!
View Related
Calculate Day, Months & Years Between Dates
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 Replies!
View Related
Adding Years, Days, Months To Date/time Format
I have some cells which must be in the format 15/06/2007 15:25 I then need to add either days, months or years onto it. Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00
View Replies!
View Related
Subtract Dates D:hh:mm
Seems that when I subtract a future date from NOW() and format the cell d:hh:mm that the result is 1 day off. So if I subtract NOW() (today is 10/24) from 10/25/08 it returns a 1 for the d when I would expect it to be zero.
View Replies!
View Related
Subtract From Inventory
Firstly i am running Excel 2003 (at work) Now my problem is that i have been asked to make a spreadsheet about Printer and Fax Cartridges (Zzzzz) but i do not know how to do the sum. this is how my Spreadsheet looks like this (cartidge amount etc all on top line of spreadsheet) (Hp45 etc all down left hand side) ...
View Replies!
View Related
Subtract The Time
I need to be able to determine a departure time based on: Appointment - Transit time - 1 hour buffer......Clear as mud? I tried using =SUM(M2-N2)-1, but being a noob, it's not working and I am lost, yes I admit it, I'm lost... I do have all the cells format set [h]:mm
View Replies!
View Related
Subtract The Dates.
I have 2 cells in a row that are times, it has been a while since I did any work in excel. I want to take the first cell as start Time, the second cell is end time and I need to subtract them and put the total in a cell called Hours. What is the correct format to use and how would I do the subtraction. Generally start time will be AM and End Time will be PM.
View Replies!
View Related
If? Vlookup? (take The Value And Subtract It)
Cell D10 to be a formula that; Looks at Cells E13:E38 and if in this range the text "Sick" appears looks at the adjacent cell H13:H38 (which has a numeric value). It will then take that value and subtract it from D8. I will have to do this for 2 other text strings as well, Vacation and Holiday.
View Replies!
View Related
Subtract Dates
I have a column with start date and another next to it with end date. I now want a third column that gives the number of days alloted. I tried =G6-F6 and that didn't work. What should I do?
View Replies!
View Related
Multiply Then Subtract Value
I have 3 columns. A B and C. In column C I want to input the answer to a formula into each cell using data from cells in their respective rows, For. e.g. Cx= Ax- (Bx X 0.8). I want to be able to input this formula somewhere that will automatically fill out the cells in column C. Column C is 20 cells long so x would range from 1 to 20.
View Replies!
View Related
Add Blank Cells For People Not On Each Years List So All Names Align
I have 10 columns that are as follows. Name, rank 04, name, rank 05, name, rank 06, name, rank 07, name, rank 08. The longest name and rank list is over 1200 names long. What I need to do is create 1, 3, and 5 year averages based on rank. My question is, is there a way to sort alphabetically that will automatically align column a,c,e,g,and i alphabetically that will also align each row by name. example: name 04 05 06 joe 75 72 paul 82 carl 72 48 Joe isn't present in 05, Paul isn't present in 04 or 06, and Carl isn't present in 06. Basically I need excel to add blank cells for people not on each years list so all names align.
View Replies!
View Related
Macro Worked For 2 Years, Suddenly Started Giving Strange Results
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View Replies!
View Related
|
TRACKER
