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 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 RelatedI 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 RelatedWhat I require is the macro code to achieve the following:-
Cells A2, B2 and C2 will be completed manually, however, when a new quotation is required the user must enter a new Control Reference Number in cell E2 and then press the Generate button.
Once pressed the Macro behind the button will generate the following :-
(a) Quote number underneath the last one in column 'A'
(b) Change Ref underneath the last one in column 'B' (this was the ref number input above in E2)
(c) today’s date underneath the last one in column 'C'
(d) a blank Quote Tab i.e. labelled the next number in the sequence i.e. Quote 2, Quote 3 etc which should be created copying the Template Tab
N.B. the total number of Quotes in any one work book should be a maximum of 30 so some form of checking would be required.
I have been using Excel for many years but haven't dived much into using VBA. I believe it holds the answer to my question. I have developed a spreadsheet with 10 tabs that is used for billing customers. I'd like to take it a step further and, on a separate tab, have a quote automatically populated and then be able to be printed. I need for the VBA to scan certain areas in each tab to see if data is present or greater that 1. If there is data present, it would need to list the content of 3 cells (description, quantity, and total cost). I know that I can directly link but it needs to be dynamic and only display/show data that is present (as not all cells will be needed for every client).
View 2 Replies View RelatedSomeone might find this interesting. Inherited a file that need to total columns but almost 50% of the file has cells w/ the single quote tick infront of the number.
Ex '2257894 or '2234. How can one remove those single quotes out of a cell? NOTE: Cell values are not the same length.
I work in sales and when under target I need to know how much more i need to sell to hit target. e.g
quotes 10 sold 1 currently at 10%
my target is 50%
at the minute i would have to work this out manually
like i need to quote and sell the next 8 to hit the 50% target.
What can i use on excel putting my current quotes and sales and my target in so it gives me the quotes and sales i need to do?
Any escape characters in Excel. I'm trying to do something like this:
=FIND(""",A1)
Way to create order and quote numbers. I'm currently starting an order number with a "O" in front of the order number and a "Q" in front of the quote number but this presents a problem when trying to create the next consecutive number because the number has a letter at the start of it so I can't use the method plus 1. I'd just simply remove the "O" and "Q" from these numbers but I need the numbers to be unique from each other and putting the "O" or "Q" in front was the only thing I could come up with to avoid the numbers ever being identical. I often need to look up these numbers and return the values in that row so the numbers need to be unique from one another.
The information for quotes and orders is saved on separate workbooks but I'd still like the numbers to be unique from one another.
Having a letter at the start of each number or a work around for using the plus 1 method when you do have a letter at the start of the number?
i want to create a quote worksheet with a type of material validated list, a type of stock validated list, a volume of material sum, a cost per lb of material varible and a typ of stock % markup to quote stock material. i have a pdf of what i want it to look like i just need help formulating it. thanks. Auto Merged Post;here is a link to the pdf. i am not sure what to call what i want to do...
[url] Auto Merged Post;here is the xls file.
I am trying to create a basic stock quote reader.
I recorded what happens when I use the "from web" tool from the data tab. I then tried to modify it by allowing me to manually (with the inbox) put in the ticker that I want. When I run it, it does nothing. When I manually put in the ticker symbol for the company, it works.
For instance, I I were to replace
"URL;http://ca.finance.yahoo.com/q?s=" & TICKER & "&ql=1", Destination:=Range("$A$1"))
with
"URL;http://ca.finance.yahoo.com/q?s=ATVI&ql=1", Destination:=Range("$A$1"))
It would then work, but I would have to manually change it every time.
Here is the code
___________________________________________________________
Sub Macro1()
Dim TICKER As String
InputBox ("enter ticker symbol")
Cells.Clear
With ActiveSheet.QueryTables.Add(Connection:= _
[code]...
Is there a way to clean all of the single leading quote marks from all cells in a sheet?
'Bob
'Tom
'Jerry
to
Bob
Tom
Jerry
I'm importing data and I have several columns to deal with and will never know how many columns/rows are in a sheet.
I have to create a formated file to test our system every other week, I want to create a File Generator using Excel and the replacement values forumla "& A1 &". However the file is in a CSV format containing a lot of quotes and when I attempt to use a line of the file in a formula with "& < Cell> &" excel reports errors, if you accept excels help it strips out the quotes and commas. Anyway around this? having to have a user save the file is CSV format would be to complicated. Sample file attached.
View 2 Replies View RelatedI want to alert a user that he or she has typed in a text string that begins or ends with an apostrophe ' or begins or ends with a quotation mark " so that they can take corrective action in removing these characters. The reasons for this are superflous to this post but what I thought was a logical answer returns a formula error. Here's the scenario:-
A user enters a string of text in cell A1.
Cell B1 checks if the text, if any, in A1 begins or ends with a ' or a " and if it does, returns an "Error" message
The formula I've tried in B1 is =IF(OR(LEFT(A1,1)="'",LEFT(A1,1)=""",RIGHT(A1,1)="'",RIGHT(A1,1)=""")),"Error","Ok") but Excel will have none of it.
I am using Microsoft 2013 and am looking for 4 formulas in order to split 1 cell in to 4 (across same row). Number of characters varies between each instance of """.
I would like the formulas to start in column B-D (data in A)
I am looking at formulas based on specific instances of a quote mark in cell from data in column A
Formula 1 - Return with characters up to and including the 5th instance of """
Formula 2 - Return with characters after 5th """ and up to and including 6th """
Formula 3 - Return with characters after 6th """ and up to and including 7th """
Formula 4 - Return with characters after the 7th instance of """
Example below...
Cell A1
xxyy","aabb","ee,ff,""gghh"hh,"llmm,mmnbijp"oossww"
Desired results
Cell B1 - Formula 1
xxyy","aabb","ee,ff,"
Cell C1 - Formula 2
"gghh"
Cell D1 - Formula 3
hh,"
Cell E1 - Formula 4
mmnbijp"oossww"
I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
=hyperlink("#Sheet1!r1c1","click here")
Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here") (note leading single quote).
I cannot use the replace function to edit them out, so must hand edit each one out -tedious at best.
Trying to build array elements from a list
19909
19953
etc
My thought is in helper column to concatenate the quotes and comma "xxxxx", Then I will copy paste special transpose and should have an array list
I tried ="""&A2&"","
Returns
"&A2&",
But I need it to return "19909",
In a large range of cells, how I can remove the single quote at the beginning of the contents of each cell? (without single quote they would be formulas). It didn't work using CTRL + L and trying to replace the single quote by blank applying this to the whole range. I am currently using Excel 2003.
Example:
cell a1 contents: '= IF (I17 = "----------", "----------", E17-C17)
cell a2 contents: '= IF (I18 = "----------", "----------", E18-C18)
cell a3 contents: '= IF (I19 = "----------", "----------", E19-C19)
....
etc., etc., etc.
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........
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?