Our Company has long term leases that increase according to the Consumer Price Index every three years.
I can easily calculate the trigger date down Col. A. What "If" formula could I use in Col. B to recognize the triannal date and rate (or forecast) the new rent?
I've thought of using an array and Match but I really don't know how to write the formula.
SOLVED: adjusted functions. creating a vba macro for other reset goals. other goals have changed as well.
I have a table where the subsheets have the specific days that an employee uses vacation, sick, personal or other hours of off time. i wrote alot here to get my goals/thoughts down
my goal is to master one section then apply it to the other vacation date types. i'm stuck on going with a formula or using vb. also another concern is odd months such as someone hired 10/31 compared to a month like 6/30. not sure if this is a real concern.
a. need to reset vacation hours for the year based on annual anniversary date
-"vacation hours per year"
-"vacation hours remaining" =E8-('Vacation 1'!B8+'Vacation 2'!B8)
this data is taken from the subsheets. there is vacation 1 for jan-jun and vacation 2 for jul-dec
1year=40 hours
after 1 year anniversary =80 hours
3 years+ =120 hours
Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)
second part of my project:
b. vacation hours remaining can be carried over up until 3 months after their anniversary date. at this point they are wiped back to 0 until the next anniversary date (where the carry over hours recalculate)
ATTACHED TEMPLATE
things i am changing in design:
vacation hours per year (to include a formula to calculate anniversary date, with the amount given for 0 year, 1 year, 3 years....) rather than a manual added # (which was lets say 40 hours+ carry over hours)
-thats why i am separating the carry over hours... which i'm hoping to auto calculate by taking
anniversary date - 3 months in a statement to reset it back to 0 after the three month period.
I need to devise a formula by which Excel will automatically award employees with service-incentive leave on their anniversary date, starting TWO YEARS after they were hired.
For example, if John Doe was hired on 02/20/1998, he earns 1 day leave beginning 02/20/2000 and on each hire anniversary thereafter. How do I make Excel do that?
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
I'll try to be as concise yet informative as possible. In the included attachment you will find a list of names in column A. These names have each have an event that is due at the end of his/her month, once annually. The due month for each person is listed in column B. Once that person completes the event, it is recorded in column C. The event will then not be due until the following year by the end of their due month.
I need column D to reflect the last day of their due month.
This date will change once the event is completed and recorded in column C. There are of course a few additional requirements that puts this out of my ability. They are listed below:
The person may complete the event at any time within three months of their due month. So if Tom's due month is September, he may complete the event anytime in July, August, or September. This would then need to reset the "Due by" date (column D).
If the person does not complete the event, the original "due by" date should not change until the event is completed.
Last note: this attachment is from Excel for Mac, The actual sheet this will be used on will be windows (so no worries about the formula changes for Mac).
Recurring due date help.xlsx
I have an Excel table. Each row is a separate record. The user types in new records at the end of the table. One of the columns in the table contains a formula. Ideally the formula should apply for each cell in the entire column. Since I don't know how long the table will end up being, I simply ask that the user drag the formula down from the cell above. I do not want the user to accidentally change the formula though. At first I thought a Custom validation with a secret word that the user is unlikely to type in, would keep the user from changing the formula already in the cell. However, I can't stop the user from deleting the formula, which doesn't help. I then thought maybe locking (protecting with password) the column would do the trick. However, now the user can't drag down the formula from the cell above. I would therefor like help with one of 2 options:
1- have the formula apply to the cell automatically as a new record is created (ideal)
2- find a way to protect the cell so that the user can copy the formula down, but not change or delete it
I did find the following code online, which appears to go some ways to answer my first option, though I can't figure out why it only works if the formula is in column A (i.e. I have formulas in columns A, B, K, L, and AF to DL).
Is there any formula or formatting that will list in a seperate table all names that occur equal to or more than a specified number of times?
eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table on a seperate worksheet.
Gus
Bob
Mike
John
Gus
Nick
Mike
Gus
So Gus and Mike would be listed in the other table on the other worksheet.
How to hide repeated values in a column, and only display them ONCE.
See attachment. The names are in column "C" and the grey bar is not using any formula, just hardcoded. I want to apply conditional formula or something like that, to keep the names as they are, in the grey bar, but supress the names in the subsequent rows, if they are repetitions.
uaImV.png
How can I get EXCEL to display recurring events on one line? In the example below, I want all events of type PLAN to be on the same line.
Phase
Time
Duration
End
[Code].....
Is there any formula or formatting (not a macro) that will list in a seperate table all names that occur equal to or more than a specified number of times?
eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table.
Gus
Bob
Mike
John
Gus
Nick
Mike
Gus
So Gus and Mike would be listed in the other table.
I am trying to solve problem with finding cells (all searching cells are in 1 column ) witch contains only recurring characters like : 00, aaaaa, 444444444 , etc.
View 3 Replies View RelatedI have a spreadsheet with two columns of data. Column A consists of identifying 6-digit numeric code and column B is the dollar value associated with the code in column A. There are numerous cases where the column A code is repeated, but with a different dollar value in column B. The column A value could be repeated two, three, or many times. What formula could I use to create a new set of columns that rolls up the instances of recurrence?
Sample included.
I'm looking to find a solution to count the totals for values which are linked to a specific code. For example:
A b
ex1 22
ex4 18
ex3 10
ex4 3
ex1 23
ex2 11
So I want a formula that will count the totals for each group in column A
The answer would be
ex1 45
ex2 11
ex3 10
ex4 21
In one of my workbooks, I add a menu to the toolbar with the following in the "ThisWorkbook" module:
Private Sub Workbook_Activate()
Run "AddMenus"
End Sub
It gets deleted with the following:
Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub............
Using Excel 2007, how can I associate a recurring name in one column with one and the same number in another?
Ex.: "Jones" in C8 = "1" in G8
"Smith" in C9 = "1.5" in G9
"Williams" in C10 = "2.5" in G10
I hope my question is clear enough.
I would like to create a calendar within excel that would be linked to a series of recurring events. Those events are happening every 15 days, and expected to occur for a period of up to 15 months (so 30 times in a row). We have about 1000 events, each named with an ID code (a01,a02,a03...) We have a database that contains the id code, the date of first manipulation and the recurring dates after that, this file is updated daily, as some schedule might have been moved 1 day as it is meant to be flexible. The difficulty being that a single day may have up to 50 events, hence 50 ID code.
The database (Sheet DB) is organized as follows first row for the headings, data starting from row 2 till row 1205.
Column A is blank
Column B is for the Department ID (Drop Down Menu).
Column C is for the ID code
Column D for the first date (encoded by coordinator).
Column E and forth (AF) for the due dates (formula based on column B).
Now for some instance we have events up to column CC (schedule is flexible and prone to change).
Due dates are linked and will update themselves automatically if any previous date is modified.
For the result (Sheet Cd), the Coordinator needs to see for a specific date all ID codes due for manipulation.
Cd is arranged as follows:
Column A is blank
Column B is the date range that is from 7 days prior today() (row 2) till today()+30 days (row 39).
Column C to BN is supposed to allow the coordinator to see a whole month schedule, due events of manipulation, and monitor past week schedule (in case of delayed manipulation as I stated a possibility of schedule being moved). If this would work, the coordinator would need not update the summary but merely update the database.
A conditional formatting with the formula $A2=TODAY() as condition in order to highlight the present day (faster to spot) is used.
The formula used in C2 is as follows:
=IFERROR(INDEX(DB!$B$2:$B$459, SMALL(IF(Cd!$B2=DB!D$2:D$459, ROW(DB!D$2:D$459)-MIN(ROW(DB!D$2:D$459))+1, ""), ROW($A$1))),"")
I stopped at row 459 here, but eventually it should go to row1205. Above formula being an array is entered as "command+ return" (using a mac, excel v14.1.4).
I was hoping to have each corresponding ID code returned for a specific date regardless of their position in the table DB. DB is sorted per ID code.
Right now the formula is working, as long as we have NO duplicated in Sheet Cd Column D. Also the results are scattered along the rows, and will require scrolling.
I am creating an employee work register to record working days and hours both in the office and remotely. I am doing this using Excel, where one spreadsheet represents one month and each cell represents a day per person. I want to enter everyone's working hours for the first week of the first month and then use a formula or another feature to replicate that pattern across the entire month/year, i.e. add a recurring event.
At the moment, I have twelve worksheets in a workbook for each month and each worksheet looks like this:
DAY | DAY | DAY >> (all the way to the end of the month)
NAME
NAME
NAME
NAME
NAME
I have created an monthly budget spreadsheet on excel with a calendar. Formula to show the recurring expenses in the calendar? (Example: Rent/Mortgage on the 1st of every month).
This is what I have : Monthly Budget.xls
code that will count the number of cells under a "title cell" that is recurring in a column, and then divide the result by 2. The result will then be displayed in another column preferably aligned to the "title cell" (in this case "Items") in column A.
For example:
Before code is applied
A1: Items
A2: Items
A3: four-legged
A4: dog
A5: two-legged
A6: chicken
A7: Items
A8: four-legged
A9: cat
[code]....
After code is applied to column A
A1: Items B1: 0
A2: Items B2: 2
A3: four-legged
A4: dog
A5: two-legged
A6: chicken
A7: Items B7: 1
A8: four-legged
A9: cat
[code]....
follows in paragraph 5 - but first, background!
I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate.
Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET($A$1,0,0,Term,1))+1))
(Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: $89,632 (rounded))
This formula assumes that the payment is made at the beginning of the period.
Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis.
Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come.
Two previous posts of mine that dealt with somewhat different issues on the same formula are:
Determine Present Value From Future Value
and
[url]
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
I have a set of data with daily total returns (TR) for 218 companies on the FTSE350 between 2007-2013. The Data looks as follows:
Company ..............Date(daymonthyear)..... TR
1........................... 112007 ..........................10.4
1 .......................... 212007 ..........................10.2
1 .
1 . .
1 .......................... 30102013 .........................12.3
. . . .
. . .
. . .
2............................112007 .......................... 4.5
2 ............................212007 ..........................4.6
2 .
. .
. .
Etc.
. .
. .
218.......................30102013 .....................13.2
I want to perform a dickey fuller test to see if there is a unit root, but for this I can’t have repeated time values, thus I need to create a daily average TR for each day (Thus summing up all TR values for all companies that have data on TR available on that day and averaging them).
How in excel can create an average value for TR every time a series of numbers in the “date” column take the same value? The best thing possible would then be if this could appear so that I have a data set that looks like
Date (daymonthyear)........ TR
112007 ..............................13.2
.
.
.
.
30102013............................ 9.4
First and foremost I would like to congragulate you on this wondeful piece of code in the below link...
The query was to get a future date excluding Fridays and Holidays...
http://www.excelforum.com/excel-work...rkingdays.html
I have a similar query and therefore I pasted this link...
I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..
Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..
The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies View RelatedI'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc. It should look like:
Monday Date:
10/13/08
October 19 October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as Ill have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
i have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC