Extend Rows and related formula downwards using functions not macros

I have a simple worksheet to amortise a loan. I would like to use a
formula to extend the rows downwards to the number of payments (Number
of years x Number of payments per year) using a formula.

For example, if there were 26 payments per year over 1 year, there
should be 26 rows. Now if I change the number of years to 2 years, two
things should happen:

1. The number of rows should expand to 52 from 26
2. The sum of the interest paid should include these extra rows.i.e
=Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to
rownumber 26).

Currently I am using a simple formula along the lines of
=IF(A47>=Term,"",A47+1)

This just provides the numer of payments and then I copy the formulas
down.

Thanks in advance, NR

0
whats (2)
6/27/2005 7:32:43 PM
excel.newusers 15348 articles. 2 followers. Follow

0 Replies
574 Views

Similar Articles

[PageSpeed] 45

Reply:

Similar Artilces:

Macro help #12
Hi, I am working on a macro in Excel 2003 Pro, and everytime I go to sort a column of data. There are about 10 rows of data in about 200+ columns. I can only get the macro to sort the column that I edited it with. How can I create the macro, so each time I click on the first row of each column, click the macro button I will assign to the toolbar, then that row will be sorted, and do this for each column? Any help at all would be greatly appreciated, Jeff Garrett (user_jeff@hotmail.com) Hi Jeff please post your current code and then we can suggest changes to it. Cheers JulieD "...

Message "class not registered" opening sheet with macros Excel 97
I have created a document with Macros and is password protected. I have sent it to several people who are able to use it - the one who can't is using Excel 97 SP2. They are getting error message "class not registered" - could it be the version they are using and how do I make sure they are able to use it. This could simply be a version issue if you developed on a later version and used controls from that versions object libraries. You should always use the lowest version to develop on. If this is not the case look in the VBE on the faulty machine and check tools>ref...

Inserting a Row from a UserForm
Hi guys- I'm kind of a self-taught VBA programmer and I can usually blunder around and get a macro to work, but I'm having trouble with a rather difficult one right now. I've created a UserForm that has 6 fields the user has to fill in for a database. Once the user hits the "OK" button, I want the macro to look for the last entry in the database that is identical to what the user filled in Field 1 of the UserForm and insert a row beneath it and fill in the cells with the data from the UserForm. So, i.e: Apple Apple Orange Orange Orange Banana Grape Grape If I type &...

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

exchange 2003
I just set up a mail server at things are working fine in the network where it resides. The issue is that I have 5 other buildings that are connected via a VPN, and they can not connect. Do I need to change settings at their locations? DNS?? WINNS??? It depends on type of client, but generally proper DNS setup is required. Also make sure that no firewalls interfere. laker18 wrote: > I just set up a mail server at things are working fine in > the network where it resides. The issue is that I have 5 > other buildings that are connected via a VPN, and they > can not co...

Using different form strings to filter
Hello. Is there a way to code a query to have it use a filter string if a form is open, or ignore it if the filter is not open? Example: Query Name: Query1 Form: Form1 and Form2 Query fields: Field1 and Field2 I have a string in field 1 and 2 Forms!Form1!Field1 in the first field of the query, and Forms!Form1! Field2 in the second field. Can I also have Forms!Form2!Field1 in the query as well? I have tried this, but a box pops up and asks for the information from form 2 when it is not open. Thank you. hi, nybaseball22@gmail.com wrote: > Hello. Is there a way to code a query to...

Formulas not updating ?
I have a problem with my spreadsheet under Excel XP. The problem has to do with cell references not updating when a row is inserted into the worksheet. The formulas in the cells are relatively simple: =B13 and =DATE(YEAR(B10),MONTH(B10),DAY(B10)+7) I can copy a row and insert it elsewhere or just insert a blank row and the cell references in rows below are not updated in all cases. It seems that rows that are separated from the inserted rows by a blank row are updated. Is this a known issue or does anyone have any suggestions on how to fix this? The spreadsheet is intended to p...

Macros #50
How do I move macros from one computer to another? In the VBA editor, right click on the sheet name or module name (in the left panel)that contains the macro and click on "Export File" -- Regards, Dave "Neal" wrote: > How do I move macros from one computer to another? Macros are always stored in workbooks, so you can simply copy the workbook containing the macros from one machine to another. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Neal" <Neal@discussions.microsoft.com> wrote in mess...

Algrebraic Functions
How would you solve a function with input 1 output 8. Then input 2 output 14. next input 3 output is blank then input is blank Output is 24. the input is blank and the out put is 28. Finally, the input is 6 and the output is blank. It occurred to me that if you assume the input blanks are 4 and 5, then you could use the Add Trendline option within Excel charts to show you what the potential functions could be. Try plotting x: 1, 2, (blank cell), 4, 5 against y: 8, 14, (blank cell), 24, 28 Use an x-y scatter chart and, when plotted, right click on a data point an...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

Locking Formulas to Cells
I don't know if I am using the correct terminology but this is what want to do: I have placed formulas in multiple columns that calculat my sales numbers for a bid. The problem I am running into is that change the bids for every person and when I clear a cell is clears th formula from it as well. Is there a way for me to clear cells withou deleting the formula I have placed inside it? I know that I can jus grab the first cell in the column and drag it down to re-load th formula in that column but I don't want to have to do that. I want th formulas permenant and the data I enter...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

using beforeprint
I am trying to get an application to append a file on a sheet printout. I am using Workbook_BeforePrint. It prints fine, but doesn't seem t execute the sub. What's wrong -- Message posted from http://www.ExcelForum.com Hi you may post your existing code :-) Also check that you have put this code in your workbook module ('ThisWorkbook') -- Regards Frank Kabel Frankfurt, Germany > I am trying to get an application to append a file on a sheet > printout. I am using Workbook_BeforePrint. It prints fine, but > doesn't seem to execute the sub. What's wrong?...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

Formulas don't work in certain cells #2
nope, the cells are formatted as numbers. I simply cannot figure thi out. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27423 Being formatted as numbers doesn't automatically mean they are numbers. Copy an empty cell formatted to General. Select your range of numbers and Paste Special>Add>OK>Esc Gord Dibben Excel MVP On Mon, 1 Nov 2004 11:59:46 -0600, kalik247 <kalik2...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Print Macro #2
Trying to write a macro to print a spreadsheet where the number of rows of data varies. What's wrong with this code? Dim rng As Range Set rng = Range("A1").End(xlDown).End(xlToRight) With ActiveSheet.PageSetup .PrintArea = rng End With Thank you. Pam Hi Pam Leave your PrintArea empty Excel will print all cells with a value on the sheet -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Pam" <gasp0225@juno.com> wrote in message news:16e401c38521$a7d25420$3501280a@phx.gbl... > Trying to write a macro to ...

Page Number in Repeat Rows area
Is there a way that I can put a Page Number in the Repeat Rows area of a sheet - and get it to update when I print? I have found one piece of VBA that puts a page number within a cell but it is only updated if the cell is outside the Repeat Row area. When it is within the Repeat Rows area I just get a page number of 1 corresponding to the original row location.. I do some VBA programming but am not an expert. TIA cheers Chris Nothing comes to mind that doesn't require VBA code. You can paste the following in the sheet header (File | Page Setup) and page x of y will print on ea...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

Using Ghost as OS Backup
I attended a class a while back where the exchange admins said they used Ghost to clone the OS drive. I bought the product and it says that during the clone process it temporarily removes the server from the domain. Has anyone done this or had any problems? Thanks, Fred On Mon, 21 Nov 2005 05:26:28 -0600, "Fred T" <fredt2@mail.com> wrote: >I attended a class a while back where the exchange admins said they used >Ghost to clone the OS drive. I bought the product and it says that during >the clone process it temporarily removes the server from the domain. ...

DPM sometimes uses too many tapes
Hi, i have problem with DPM long term backups on tape. I have collocation enabled so that is not a problem. Sometimes DPM uses too many tapes for same protection group, sometimes it uses 1, and then sometimes 2 - 4. Amount of data is always same. Can anybody help me please, what could be the problem? Hi Tornado, Can you explain protection group configurations as well as tape usage in detail, so that we can analyze on what is happening? Thanks, Jyothsna[MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Tornado" <Torn...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

Using paste in macro but with variable references.
Hi all, I am trying to use the paste function in a macro but it needs to be variable. I have split a list of data (team workloads) into the seperate teams and by days of the week. I now need the macro to compile one book with all the data in order. The headings for each team are already set on a worksheet so the data needs to be entered under each heading For example: Team A Day 1 under the heading "Team A Day 1", Team B Day 1 under the next heading on the same sheet "Team B Day 1". I hit problems as the workload ammount will alter from week to week. So of course asking...