How To Add Values Based On Date They Occur
Mar 15, 2014
I'm trying to create a master template for doing cash flow projections for a number of years. Ideally I would like have a cell for the number of years I want (10 for example) and have excel populate a group of cells based on that number. Each row of cells would be one year and each column would be one month. The dates would be calculated based on a starting date that I enter into a cell.
After that I would like to have a cell for starting cash flows ($1,000 for example) and then another cell that I can plug increases into. For example cash flows may increase to $1,250. I would also need a cell for the date cash flow increases go into effect (1/2019 for example).
View 1 Replies
ADVERTISEMENT
Feb 26, 2009
My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.
View 6 Replies
View Related
Aug 4, 2014
I am a behavior specialist and I have created a spreadsheet to determine what possible antecedents trigger certain behaviors. In other words, I want to know what occurs, like being around an unfamiliar person, right before a person engages in an "acting out behavior" like becoming verbally or physically aggressive. In my spreadsheet I have a number of possible antecedents with a number of possible behaviors. It is tracked by the date that the behavior occurred.
View 6 Replies
View Related
May 5, 2008
I will say exactly what i want.
I want to keep statistics of roulette.
More specifically, i want to count how many times a dozen lates to come.
The range from 1-12 is 1st dozen, from 13-24 is the 2nd and 25-36 is the 3rd.
I want to count among these numbers, how many times one of three dozens appear every 1 time, how many times appears every 2 times, .... until 20 times.
For example: I have put in a column 500 numbers from 0 to 36.
The output must be like this:
1st Dozen: Every 1 time: x
Every 2 times: n
Every 20 times: m
The same for 2nd and 3rd dozen.
View 9 Replies
View Related
Jun 29, 2012
I have a spreadsheet where I tack daily expenditure. On a given day I could spend on several items. At the end of that day I want the total of that day's spending in the last row that bear the same date.For example in the table below, I have the date, amount and total columns.
I want to be able to have a formula in the totals column that look at the dates in the date column, and where the date in a row is different from the date in the row immediately below, sum the values of amounts in rows with the same date and give the total in the last row with the same date.
Date
Amount
Total
10-Mar-12
100
10-Mar-12
112
[Code] .......
View 4 Replies
View Related
Oct 19, 2009
I am looking for a way to sum up values segregated on specific dates.
I like to sum up values from column C based on dates in column A. Data I have to look at are based on groups of 7 weekdays. Data to be sum up always goes from Monday too Sunday
Example: If date is equal to or lower than 31-12-2009 then sum values C2:C5 and if date is equal to or larger than 01-01-2010 then sum values C6:C8.
A
B
C
1
Date
Weekday
Value
2
28-12-2009.......
View 9 Replies
View Related
Mar 12, 2009
I have data that contains all the days in a year G7:I372. I want to lookup a value based on month, date and return the result. The sheet I am using contains a list in A3. As I change the value of A3, i want the dates to update. See attached sheet.
View 5 Replies
View Related
Feb 19, 2008
I want to populate two cells (the yellow ones) with the start and end date values from the row where a match is found in the 'period' column for the value in the left-hand green cell and in the 'week' column for the value in the right-hand green cell. Is anyone else on the board firing on all/most cylinders today?
View 5 Replies
View Related
May 19, 2014
I have a VBA or function query.
I have a spread sheet with the following.
Column A contains asset names
Column H to JZ contains cash outflows for each asset
Row 17 contains a Month&Date value (ie. July2013)
What i want to do is, from another sheet, is to calculate the sum of all cash outflows for the asset (determined by a cell value) for a date range entered. In other words, if i say 1/7/2012 to 30/6/2014, it will look the asset up, and sum the value of cashflows, in colums that fall between those dates for that asset.
View 2 Replies
View Related
Oct 31, 2011
I have a workbook which contains lots of formula and is therefore very large (about 70 mb). The sheet pulls information from field instruments every hour of every day for the entire year.
I want to write a macro, that loops through a range (B5:B8764) and copies the entire row if the value in column B is less than =NOW() - 7 days. The copied rows should then be pasted with values only exactly where they were located e.g. row 5 should be pasted back to row 5 the only difference being only the values will be pasted.
View 2 Replies
View Related
Aug 16, 2013
I'm trying to find a formula that will allow me to show a value based on whether certain dates have passed. This is for the purposes of calculating PTO hours and removing unpaid holiday hours from the equation.
I've got "slave" cells set up with the dates and amount of hours for each holiday, but I'm having some trouble.
I've been trying something to the effect of:
=IF(B4>B14,8 IF(B4>B15, 13))
Now, I can get it to show the "8" if I keep only the first IF function, but adding additional F functions like in the formula above only returns errors.
My goal is to get B7 to show the sum (through formula or manual entry) of C14-C21 based on whether B4 [=NOW()] is before or after the corresponding date in B14-B21.
I need to clarify any terminology or other cell values.
View 2 Replies
View Related
Nov 6, 2008
summing monthly values based on different date ranges. For example if I have multiple contracts with different start and end dates and based on those date ranges, I want to sum the respective monthly production totals, how would I do that in a formula. I started to set up the table to include "start date month" and "# of months in contract". Is there a way to sum based on the start month and # of months in contract.
For example if hte contract started in Jan08 and ends in May08, the calculation would start with month "1" and sum over 4 months.
Here is an example: ....
View 9 Replies
View Related
Apr 27, 2009
I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.
Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.
The reference cell is H5, the Date ranges are J2 - J12, and the principal fields are: K2 - K12.
In the next post I will include the Amortization schedule.
In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).
Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6H7H8H9H10H12H13H14=FGHIJKLM1 v 1.0 PaymentApply DatePrincipal + InterestAdditional PrincipalInterest Only2 1/1/2009 833.333 2/1/2009 833.334Quarter Close Backward 3/1/2009 833.335 Close Date9/1/2009 4/1/2009 833.336 Days of Interest0 5/1/2009 833.337 Beg Principal Balance$100,000.00 6/1/2009 833.338 Paydowns$980.41 7/1/20091,321.51 9 End Principal Balance$99,019.59 8/1/20091,321.51 10 Net Principle Due$3,562.50 9/1/2009 829.2711 Interest Due & Accrued 10/1/20091,321.51 12 Interest Earned (cummulative)$7,350.66 11/1/20091,321.51 13 Interest pymts (cummulative)$7,350.66 14 Net Interest Receivable$0.00 TEST [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 10 Replies
View Related
May 22, 2014
I'm using the following code (found here: [URL]......) populate dates in row 13 for a dynamic Gantt Chart...
[Code] ....
It works perfectly to display daily dates in the mm/dd format. Now I want to have an overarching month/year displayed over the entire range for any particular month by merging and centering the cells above the month's dates the above code populates, and pull in the month/year formatted as mmm-yyyy. I've attached a mock up I did manually to convey the desired result.
MergePic.JPG‎
View 1 Replies
View Related
Nov 25, 2009
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View 3 Replies
View Related
Feb 18, 2014
I have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
View 11 Replies
View Related
Dec 5, 2013
Below the two columns represent Column A (date and time) and Column B is temperature.
How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?
5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4
View 2 Replies
View Related
Dec 22, 2008
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
View 5 Replies
View Related
Mar 14, 2008
I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.
E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1
View 13 Replies
View Related
Jan 13, 2010
I would like a formula (if it is possible) that will list which months occur between two dates;
i.e
Start Date (Cell ref A2) = 01/01/2010 (in the dd/mm/yyy format)
End Date (A3) = 02/05/2010
In cells D2:O2 I have the months Jan-Dec. In cells D3:O3 I would like a "Yes" to appear if the above month occurs between the dates in A2 & A3. In this example would like a "Yes" to appear in cells D3, E3, F3, G3 & H3 but not in the other 'Months' appropriate cells.
View 2 Replies
View Related
Aug 19, 2012
In a cell i need this info when
column a = month
column o = staff member
column m = discount given
if no discount is given column m will show 100%
i need the total of all sales made with a discount i.e not 100% and not blank, in a certain month, by a certain member of staff
step 2: i need the average of all these for each member of staff shown in a different cell
i already have the total sales counted per staff member so this will show me who is doing deals and who is doing the biggest deals.
View 4 Replies
View Related
Sep 19, 2007
i need to delete rows from sheet2 which contains source data for Vlookup on sheet1.
that is i need to run a vlookup for a value in sheet1 and after the value is obtained , delete the row which has that value in the source data......
i am sure a simple for loop will help but it needs to do the events in sequence:
vlookup for row1> fetch / place value (sheet1) > delete data in the source table (worksheet2) > go back to row2 and repeat till the the last.. or till the end of a predefined range..
View 9 Replies
View Related
Jul 24, 2009
Basically my sheet links may appear as a cell in another tab or it might be to an http address... Here is my formula I have setup up right now.. Only problem is, no matter what I put into the logical test to get a "true" result for the http addresses it still says false..
View 2 Replies
View Related
Nov 20, 2013
I have a spreadsheet with over 15 columns showing drivers (names numbers etc) and their duties with more inf.
What I need is to be able to find when a certain type of driver in column A (drivers belong to different rutes) say from route A is at rest (this is shown as RD) which is shown in column B and then be able to count how many times those two exact events occur.
View 1 Replies
View Related
Jan 14, 2013
I am trying to determine whether or not 26 blanks occur in succession in a row. Two example rows of data are listed below, where "_" refers to a blank cell:
2 _ _ 3 _ _ _ _ _ _ _ 4 5 6 3 4 _ _ _ _ _ 4
_ _ _ _ _ _ 7 8 13 2 3 _ _ _ _4 5 6 11 10 2
Hopefully you can picture the data from above. Anyway, there are multiple locations where a string of blanks occur. I'm looking for a formula that can test as to whether or not a certain number of blanks occur in succession during any part of the row.
View 10 Replies
View Related
Oct 27, 2008
I am sure that most of you will probable know what i am doing wrong here straight away so rather than me waste another 1/2 hr stuffing around. I am trying to find count the number of times two events occur simultaneously.
=SUMPRODUCT(--('5. Financial'!AX4:AX134=A5)*('5. Financial'!BQ4:BQ134>0<0.1))
The first part is fine I think but in the second part I am trying times only when cell is between zero & 10%.
View 3 Replies
View Related
Jan 25, 2012
I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)
So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?
View 1 Replies
View Related
Jan 18, 2014
Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).
Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line
VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer
[Code].....
View 5 Replies
View Related
Feb 26, 2014
Attached is an example of an excel spreadsheet I have which details all the referrals created within my organisation over 2013. I want to be able to create two excel formulas which basically looks at a duplicate tag and then determines if the duplicate referral was created within 30 days of other referrals created for the same patient and then another formula to check to see if the duplicate referral is for the same medical condition. Also if possible I would love a third formula which would say check duplicate, 30 days and same condition and enter a "Yes" in a forth column.Referrals.xls
View 8 Replies
View Related
Sep 25, 2007
The file is a simple sheet which upon entering the actual/Invoice cost (C5), calculates the estimated landing/final cost (C8).
In between the process involves changing the currency from US$ to PKR, make some calculations, and changing back the currency again to US$.
The default rate of currency exchange is set to ave 60 (C12). However if the user knows the current rate he can put it manually in C6 and sheet will make all the calcs on this instead of using the default rate.
Problem:
Everything is working just too perfect. But probs occur when i manually enters the exchange rate.
It does successfully changes the US$ to PKR and calculates everything perfectly but doesnt reverts the final cost back to US$ successfully instead it keeps using the default value instead of user's value
View 7 Replies
View Related
