Calculate Number And Amount Of Payments
The example:
Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.
Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00
Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.
So the sort of result I'm looking for would be like
Assume todays date is 19-04-09
A B C
12-04-09 £5.00 Last Week 4 Payments Value £45.00
12-04-09 £10.00
13-04-09 £10.00
14-04-09 £20.00
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculate Future Value Of Monthly Recurring, Annually Increasing Payments
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]
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 Calculate Percent Change, Varied By Amount Of Months
I need to figure out a formula for cell F17 that will calculate a percentage change only for the months that have data in 2009. The way it is set up right now I have to go in every month and change the cell reference of the formula to include the latest data. Since the 2008 data is totally populated the formula gets messed up if I include the months of 2009 that have not yet occurred.
View Replies!
View Related
Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message. give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount? attached is the start of the assesment question. You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid. Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received: • If students pay the course fee less than 7 days prior to the course commencing then they receive no discount. • If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%. • If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%. • If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
View Replies!
View Related
Converting A Whole Number Into An Amount Of Time
field A1 & B1 are variable user input. The goal is to subtract B1 from A1, take the result and divide it by 176 (a fixed number) and display the result as minutes:sec. For example: A1 B1 14,000 (minus) 2,200 (= 11,800 /176 =) 67 I want to result (67) to display in field C1 as 01:07 (min:sec) I tried (A1-B1)/176 and formatted C1 to be min:sec but the result displays as 5:27 I have no idea what that number represents.
View Replies!
View Related
Prorate A Total Dollar Amount By A Selected Number Of Attendees
I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.
View Replies!
View Related
Management Of Payments
First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and pay once a week for their classes. I'm taking this one step at a time. At the initial interview of a particular client we will determine the payment per session due, and we will enter this amount in the column labeled "payment per session. We also need to keep track of how many sessions they have paid for. So in short, I'm looking for the following: When "amount paid" equals "payment per session" set "minimum payment due" to $0 and increase "classes paid" by +1 I figured out how to enter the payment and return the minimum payment due to "0" but how can I make the "classes paid" increase by 1 every time the minimum payment equals "0"?
View Replies!
View Related
Keeping Track Of Payments
i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess have any of you got any ideas of how i could simplify this and make it look neat and easy or alternatively is there some other software that may suit this application better?
View Replies!
View Related
Future Value Of Unequal Payments
Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.
View Replies!
View Related
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
Can Periodical Fixed Deductions/payments Be Inserted Automatically
Can periodical fixed deductions/payments be inserted automatically to the individual MONTH sheets of the workbook I uploaded ? To reduce the size of the file, I have left only ONE of the twelve month-sheets - the one for January. Rephrased : How to automate the entry of fixed amounts at regular intervals? My guess is that from time to time, a macro would have to be run - one of the macro's inputs would be the computer date and the other two inputs would be the value of the entry to be made and the value of the intervening period. In practice there would be more than one regular entry and the values would be both positive ones and negative ones. I guess that means one macro for each financial pattern. Feasible or feesible (a multi-million-dollar-fee task)?
View Replies!
View Related
Calculate The Separate Number
In ROW A1 I have the following: 200,400 - this is from a drop down list. What i need to do is then split the two numbers so as the 200 apperars in ROW B1 & the 400 apperars in ROW C1 This is so i can then do a simple calculation to the separate numbers could you give me the formula i need to get the 200 in row B1 then i can try and work out the C1 formula.
View Replies!
View Related
Calculate Number Of A In Particular Cells
I have one excel sheet with columns from A to LZ and number of rows. Each column have only letter (one of A,B,C,D,E). This if for weekly table: COLUMN define: Mon-Sun ROWS define: work type (A B C or D) For example; COLUMNS: Su-Mo-Tu-We-Th-Fr-Sa-Su-Mo-...... ROWS: A -A - B - B - B - B - B -C - C - ......till column LZ. Issue: I want a formula for any row, from which, I can calculate (for that particular row) number of B's on Mo-Tu right from column A till LZ.
View Replies!
View Related
Calculate Number Of Days Between 2 Dates
I have a start date formatted as dd/mm/yyyy in cell A5 I have an end date formatted as dd/mm/yyyy in cell E5 In cell F5 I would like the result of the number of days between E5-A5 (Excluding weekends) In cell G5 I would like the total number of days in the range F5:F25 In cell H5 I would like the total number of working days from A5 to Today I am using the following formula to get the result in F5 etc =SUM(INT((WEEKDAY(A5-{2,3,4,5,6})+E5-A5)/7)) I have copied this down and in the blank cells I get #NUM! In cell F? were there is no date entered in cell E? I get a minus number (which I can hide using conditional formatting), but the minus number obviously has an affect on the result in G5, as cell G5 gives me #NUM! instead of the result for the current numbers in row F
View Replies!
View Related
Calculate The Quarter To Date Number
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3-AL3. First - can anyone explain the formula so I may understand it and possibly modify it for other uses Second - wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3 - any suggestions on how to do this easily. By the way, the formulas are in column AO, staring on row 4. =SUM(OFFSET(A3,1,MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*3-2,1),3:3,0)-1,1,MATCH(Date!$C$6,3:3,0)-MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*3-2,1),3:3,0)+1))
View Replies!
View Related
Calculate Percentage Based On The Number
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out. Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
View Replies!
View Related
Calculate Number Of Months Between Two Given Date
Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April"
View Replies!
View Related
Calculate Number To Reach Target
I have a target to achieve every month in my department for the number of items completed named BC.I am trying to create a formula so that I know how many minimum items I need to complete every day in order to achieve this target by month end. The target to complete each month is calculated with multiple variables and therefore I am not sure how create a formula to calculate the minimum item to be completed everyday to achieve the target by month end. I have attached the spreadheet and appreciate help. The target to achieve is named "Target BC" in red. In addition, at the beginning of the month, we only have forecasted numbers and these numbers needs to be replaced by actual numbers everyday.
View Replies!
View Related
Calculate An If Value Based On A Number Of Conditions
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input. For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input). the formula I started out with was: =IF((A6=1),"$8.00") this worked for me if A6 did in fact equal 1. So I tried adding this equation to the formula. =IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula. I got the error #VALUE!
View Replies!
View Related
Calculate Number In Brackets/Parenthesis
Is it possible to extract a number in brackets specifically from a cell, then increase it by 15%, and return the result back into the brackets. For example the cell consists the following: Mary (78) so the aim is to get the 78 out for calculation, then change the content of the cell with the new result. Or is there a macros that can be created for this?
View Replies!
View Related
Calculate Week Number From Sheetname
Daily i import sheets into excel and the sheet name is uniformed to the following 20061017_BNKREC - 20061018_BNKREC - 20061019_BNKREC .......... just for clarity purposes [2006] = year, [10] = month, [17] = previous day, [_BNKREC] = report type I'll be creating a graph to which shows account balance by week, by account. The data will be coming in daily. i know i will need to create either a dynamic range or copy my data into a new sheet. My head is spinning because i need excel to somehow (either in a formula or VB) determine what WorkingWeek the sheet is in. I dont want to have to keep adjusting formulae or ranges when import a new sheet.. bare with me here as its hard to explain ................
View Replies!
View Related
Calculate Number Of Days Using 2 Sets Of Dates
I'm new here. I've searched these forums in the past for help, but never had to post, i've always been able to find something by searching. However, this time I couldn't find anything that addressed my specific need so I figured it'd be worth a shot. Alright, I have two sets of dates, and i'm trying to set up a formular that calculates the numbers of days from the first date range that overlap/fall within the second date range. For example: In A3 type "1/1/2006" In B3 type "1/1/2007" In C1 type "6/30/2005" In C2 type "6/30/2006" Now, in C3 i'd like to insert a forumla that tells me how many days in the date range of 1/1/2006-1/1/2007 fall in the date range of 6/30/2005-6/30/2006. Basically for this is should be half the year, however many days that is. Is this possible with a formula?
View Replies!
View Related
Calculate The Number Of Days From Start To End
I have 2 dates a Start Date and an End Date. I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column. Start End Yr1 Yr2 Yr3 11/01/99 11/01/00 11/01/01 10/31/00 10/31/01 10/31/02 Col A Col B Col C Col D Col E 10/31/00 07/05/01 365 117 0 I thought a series of IF statements but I am open to an easy solution...
View Replies!
View Related
Calculate Number Of Rows Used On Sheet/Worksheet
Calculate the number of rows used in sheet. for exa: i have A1:A100 i want that B1=100 and if i delete some of rows the number of rows in B1 auto-change. and can let B2 display the number of rows deleted. i have 2 sheets now, can the Cell C1 in sheet 2 display the number of rows in the sheets, B1 in sheet1 + B1 in sheet 2
View Replies!
View Related
Calculate Workload Based On Number Of Devices
I work in IT Service company and we need to calculate our workload based on a number of devices in our scope. Say, somebody wants to add 300 Windows Servers to our service desk. We know that each Win Server would need 1 Person on duty to manage. So ideally we would just multiply 1*300 and get the amount of people we need. Problem is that because the service is quite similar for each of these servers, we dont really need a dedicated person for each device so I want to make every additional device consume 20% less workload than the previous one. In our case it would be: 1 Server = 1 Person 2 Servers = 1.8 People 3 Servers ~ 2.5 People
View Replies!
View Related
Calculate Sum Of Digits In A Number String
I need to calculate the sum of digits in a numeric string. The function should keep on adding the digits of each result, till the final sum is a single digit. Please refer to the following example and result expected:- Supposed the number entered in Cell A1 is 456789 Result 1 = 4+5+6+7+8+9 = 39 Result 2 = 3+9 = 12 Final Result required = 1+2 = 3
View Replies!
View Related
Formula To Calculate Average Number Of Rebills Per Client
I need help with a formula to calculate average number of rebills per client. I don't know how to get excel to add the number of unique client in a given row. Example Column A Client 1 Client 1 Client 1 Client 2 Client 2 Client 2 Client 3 Client 4 Client 4 Client 4 Client 4 Client 5 Formula needs to calculate number of Unique clients. In this case, the answer is 5, but how can I get excel to calculate for me?
View Replies!
View Related
Formula Required To Calculate Number Of Days Remaining
A1, B1, C1 and D4 are cell in Excel worksheet and values are 2, 4 and 6 in A1, b1 and c1 respectively. A1 B1 C1 D4 2 4 6 C1-Formula is: =sum(A1+B1) Now I have to write formula for D4-cell: i.e. =sum(C1/Total days remaining of the month) Suppose Today is April 01, 2009, C1/should be devided by 30, Tomorrow will be April 02, 2009, C1/should be devided by 29.
View Replies!
View Related
Formula- Calculate The Number Of Days Since An Employee Worked
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
View Replies!
View Related
Calculate The Number Of Paychecks And The Dates For The Calendar Year
I am needing to create a formula that will calculate the number of paychecks and the dates for the calendar year. There is some employees that are paid bi-weekly and some bi-monthly. For Example: A1-Employee, A2-Hire Date, A3-Pay Frequency, A4-Number of Paychecks Based on the hire date and pay frequency it will calculate the number of paychecks in A4. Is this something that can be calculate with excel or would it need to be done manually.
View Replies!
View Related
Calculate Pay For Mileage While Factoring In The Number Of Participants
I'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be $35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that). The rest of the if statements simply follow that same formula, but for different mileage/pay rates.
View Replies!
View Related
Calculate Number Of Dates Within A Column Based On Month
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything. So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View Replies!
View Related
Calculate Number Of Hours Between Two Date/time Combinations
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about. For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
View Replies!
View Related
Calculate The Number Of Days Between Two Dates Using The Days360 Function
I was calculating the number of days between two dates using the Days360 function. However, now it is not accurate enough for me... Is there a function that is more accurate? based on 365 days and includes leap years... that I could use as I need this calculation to be exact? For example, number of days between April 1st, 2010 and August 31, 2012 is 884, but the days360 function returns 870.
View Replies!
View Related
|
TRACKER
