Repeat formula in new rows

I have created a simple spread sheet to keep track of finances.  Each day I 
want to enter a stock price at the beginning of a new row and then have 
about 8 columns adjust their amounts in the new row.  When I enter a new 
stock price at the beginning of a new line and press enter, only two of the 
8 columns tabulate and produce new amounts.

The two columns that do act correctly have these formulas on line 14 for 
example:

F14 has: =50000*B14
J14 has: =F14-187492.97

Some of the ones that do not adjust and stay blank have these formulas on 
line 13:

C13 has: =B13-B12 but there is nothing in C14
D13 has: =(C13/B12)*100 but again nothing in D14
G13 has: =F13-F12 but nothing in G14

* All the columns have the same formulas repeated at least for 9 rows before 
the non-working areas.
* Extend data range formats and formulas is checked. 

0
Back (5)
7/13/2008 7:44:45 PM
excel 39879 articles. 2 followers. Follow

4 Replies
484 Views

Similar Articles

[PageSpeed] 54

I, for one do not understand. If desired, send your workbook to my address 
below along with a clear explanation and examples of what you want.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"~~~AAA~~~" <back@one.com> wrote in message 
news:487a5b2d$0$4998$607ed4bc@cv.net...
>I have created a simple spread sheet to keep track of finances.  Each day I 
>want to enter a stock price at the beginning of a new row and then have 
>about 8 columns adjust their amounts in the new row.  When I enter a new 
>stock price at the beginning of a new line and press enter, only two of the 
>8 columns tabulate and produce new amounts.
>
> The two columns that do act correctly have these formulas on line 14 for 
> example:
>
> F14 has: =50000*B14
> J14 has: =F14-187492.97
>
> Some of the ones that do not adjust and stay blank have these formulas on 
> line 13:
>
> C13 has: =B13-B12 but there is nothing in C14
> D13 has: =(C13/B12)*100 but again nothing in D14
> G13 has: =F13-F12 but nothing in G14
>
> * All the columns have the same formulas repeated at least for 9 rows 
> before the non-working areas.
> * Extend data range formats and formulas is checked. 

0
dguillett1 (2487)
7/14/2008 12:17:13 PM
Are there formulas continuously across the rows from C to J?
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"~~~AAA~~~" <back@one.com> wrote in message 
news:487a5b2d$0$4998$607ed4bc@cv.net...
>I have created a simple spread sheet to keep track of finances.  Each day I 
>want to enter a stock price at the beginning of a new row and then have 
>about 8 columns adjust their amounts in the new row.  When I enter a new 
>stock price at the beginning of a new line and press enter, only two of the 
>8 columns tabulate and produce new amounts.
>
> The two columns that do act correctly have these formulas on line 14 for 
> example:
>
> F14 has: =50000*B14
> J14 has: =F14-187492.97
>
> Some of the ones that do not adjust and stay blank have these formulas on 
> line 13:
>
> C13 has: =B13-B12 but there is nothing in C14
> D13 has: =(C13/B12)*100 but again nothing in D14
> G13 has: =F13-F12 but nothing in G14
>
> * All the columns have the same formulas repeated at least for 9 rows 
> before the non-working areas.
> * Extend data range formats and formulas is checked. 


0
bliengme5824 (3040)
7/14/2008 1:00:25 PM
I have shares in one stock.  Each day I keep track of how much it goes up or 
down, in both percent and dollars, and both daily and year to date.  I also 
keep track of how much my entire holding has gone up both daily and year to 
date.

I have a simple spreadsheet with eight columns. In the first I enter the 
closing price. Then, the idea is that in the other columns, calculations are 
made based on the price has been entered in column 1. In one column I have a 
formula to calculate the price's change from the previous day in percentage, 
another in dollars.  Then I have columns to indicate my total value change 
in percentage and then another in dollars, etc.

The problem I am having is that, as I understand it, when I create a new row 
by entering a closing price in the first column and then press enter, the 
remaining seven columns should calculate based on what I have just entered, 
since their formulas should extend down to the new row.  My problem is that 
only two of the remaining seven columns do that.  They provide new numbers 
in their respective cells on the newly created rows but the other five cells 
in the new row remain blank.

If I first copy the last full row from the previous row into the next 
available new row I will have the last two rows identical. I can then change 
the price in the first cell of this new row and the other cells change 
according to their respective calculations.  But it is my understanding that 
when creating a new row, the formulas should each carry down automatically 
into the new row, provided that these formulas exist in at least five of the 
above rows of each column.  At least that is the way it is working with the 
two successful columns.  But I can't figure out what is different about the 
remaining five columns that their formulas do not carry down to the next row 
when it is created.

***Each column has a descriptive title in the first three rows/cells. Then 
there is a formula in the fourth cell for each column. Each column's formula 
remains constant as each new row is created. Each column has a different 
formula...change in dollars, change in percent, daily, YTD, etc.  I am at 
about the fourteenth row now meaning that the cells in each column have the 
same formula in them from rows four through fourteen.

Why do only two of the columns carry down their formulas into the new row 
and return results in their cells of the new row based on what I enter in 
the first cell of the new row? I simply input the new price in cell one of 
the next new row at the bottom, and press enter,  I get results in the cells 
of only two columns.  If I highlight either of these two cells and look 
about in the formula area I see that the formula exists and has carried 
down.  But if I high any of the other cells in the newly created row and 
look above I see that they are blank and that their formulas did not carry 
down, or extend, or however it is termed.

I will send along my workbook if the above does not sufficiently explain my 
situation. Thank you.






"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:OJPsXGb5IHA.1196@TK2MSFTNGP05.phx.gbl...
> Are there formulas continuously across the rows from C to J?
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "~~~AAA~~~" <back@one.com> wrote in message 
> news:487a5b2d$0$4998$607ed4bc@cv.net...
>>I have created a simple spread sheet to keep track of finances.  Each day 
>>I want to enter a stock price at the beginning of a new row and then have 
>>about 8 columns adjust their amounts in the new row.  When I enter a new 
>>stock price at the beginning of a new line and press enter, only two of 
>>the 8 columns tabulate and produce new amounts.
>>
>> The two columns that do act correctly have these formulas on line 14 for 
>> example:
>>
>> F14 has: =50000*B14
>> J14 has: =F14-187492.97
>>
>> Some of the ones that do not adjust and stay blank have these formulas on 
>> line 13:
>>
>> C13 has: =B13-B12 but there is nothing in C14
>> D13 has: =(C13/B12)*100 but again nothing in D14
>> G13 has: =F13-F12 but nothing in G14
>>
>> * All the columns have the same formulas repeated at least for 9 rows 
>> before the non-working areas.
>> * Extend data range formats and formulas is checked.
>
> 

0
Back (5)
7/14/2008 1:41:39 PM
Although Excel Help does not say this, my impression is that this only works if the references in the formula are either to the 
same row or to an absolute row.
So if a formula points to the data entry cell and the row above, it will not be extended automatically.


-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"~~~AAA~~~" <back@one.com> wrote in message news:487a5b2d$0$4998$607ed4bc@cv.net...
|I have created a simple spread sheet to keep track of finances.  Each day I
| want to enter a stock price at the beginning of a new row and then have
| about 8 columns adjust their amounts in the new row.  When I enter a new
| stock price at the beginning of a new line and press enter, only two of the
| 8 columns tabulate and produce new amounts.
|
| The two columns that do act correctly have these formulas on line 14 for
| example:
|
| F14 has: =50000*B14
| J14 has: =F14-187492.97
|
| Some of the ones that do not adjust and stay blank have these formulas on
| line 13:
|
| C13 has: =B13-B12 but there is nothing in C14
| D13 has: =(C13/B12)*100 but again nothing in D14
| G13 has: =F13-F12 but nothing in G14
|
| * All the columns have the same formulas repeated at least for 9 rows before
| the non-working areas.
| * Extend data range formats and formulas is checked.
| 


0
nicolaus (2022)
7/14/2008 1:49:47 PM
Reply:

Similar Artilces:

I need a formula
I need a formula that would allow me to copy one cell to another as well as remove the first three characters. example cel a - 12321232 would copy to cell be as - 21232 If anyone could help me that would be soo awesome Hi, Try one these =RIGHT(A1,5) If the lenght of your data is fluctuating then =RIGHT(A1,LEN(A1)-3) If you need them to return a number instead of text then tack *1 on the end of them. HTH Martin "RBD" <rdickiejr@gmail.com> wrote in message news:bab5ed6a-ba7d-48e9-8cc0-ad7d05e7ac73@r15g2000prh.googlegroups.com... >I need a formula that would allow me...

delete empy rows
Hi, I have a sheet with 3000 lines, I am using only thre coomns A, B, C How can I delete all rows that has empty cells in colomn C i.e. row 5 contains Sam in colomn A , 2 in colomns B , nothing in colomn C. I want to delete this row row 6 contains 3 in colomn A, nothing in colomns B, 34 in colomn C. I want to keep this row. can this be done Khalil Try this on a *spare* copy .. Select col C Press F5 > Special > Blanks > OK Right-click on the selection > Delete > Entire row > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>y...

new mail not showing in Inbox in O2002
Hi. I find that emails do not show in my Inbox until I go to the Calendar, or other 'space' in Outlook. As soon as I leave the Inbox, I get the signal that new email has arrived and sure enuf when I go back to the Inbox, there are the emails. This has happened repeatedly. I'm sure that my being in the Inbox is the problem. Anyone have a fix? Thanks, Rob I have same problem plus no sound when they do arrive? Same with you? Are you web base mail? >-----Original Message----- >I have the exact same problem and it seems to be very >consistent. If you find any t...

Unhiding rows
In Excel 2000, someone has hid the first 45 rows of the worksheet. I can't unhide them. The sheet is NOT protected. I tried various things including Edit/Go to/A1/Format/Row/Unhide but nothing works. Any clues? .......Baffled in Ontario row height? -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Connie" <marverncon@hotmail.com> wrote in message news:0b4001c35c24$077545d0$a601280a@phx.gbl... > In Excel 2000, someone has hid the first 45 rows of the > worksheet. I can't unhide them. The sheet is NOT > protected. I t...

Outlook 2003 New Install Config Issues.
Hi, I've just had my PC reimaged and Outlook 2003 reinstalled. My PST file has been imported and my mail is where it sholud me. Problems is the veiws had changed from what i had before. For example, all folders now have gridlines between emails. I know how to remove these in customise view but is it possible to change the veiw for all folders at once or do i need to do each folder individually? Cheers, D "moox7" <moox7@hotmail.com> wrote in message news:28b14a58-eee6-46c2-ad29-909e710ed2dd@r37g2000prr.googlegroups.com... > For example, all folders now have gridli...

New to RMS: Having trouble setting up 2nd terminal.....HELP
We have an existing customer who has 1 server and 1 POS terminal. I have recently installed a 2nd terminal and am trying to match the settings and templates of the register 1 to the new register 2. The problem is with the on-screen keyboard. I've chosen the option to use the touchscreen keyboard in manager for register 2 and picked the template for the pre-configured keyboard layout; however, register 2 just refuses to display the template like register 1 does. I know ive got to be missing something. By the way, i forgot to mention that yes, i have enabled the touchscreen keyboard ...

selecting rows using a variable
I am using excel 2003 and I need to select rows using a variable. Here is what I have now: Dim name As String name = Range("e800") ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select Selection.EntireRow.Hidden = True Dim Start As Integer Dim finish As Integer Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("t800:t881")) finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("u800:u881")) ActiveWorkbook.Sheets("individual stats&q...

select and delete all blank rows
I could save a lot of time if I knew how to select and delete all blank rows from a document, and also separate things. An example of separating things would be a list contains fruits, vegetables and meat. I could separate all the fruits from the list easily. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then clic...

Formula help! #2
hi all can someone help me where i have a row of numbers that go 0, 0, 0, 0, 0, 5, 10, 5 etc... from cells D2:K2 i want a formula to put in A2 that returns the first non- zero number, ie. it would return 5. it would also be useful to have a formula that returns which column the first non-zero number occurs in. any ideas? thanks JohnQ one way: First non-zero (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(rng,MATCH(TRUE,rng<>0,FALSE)) where rng is your range reference (e.g., D2:K2). Column the first-non-zero occurs in (also array-entered): =LEFT(ADDRESS(1,3+MA...

Formula Format problem
I am using the following format to query another sheet and return data. =(SUMIF('Assignments Formulas'!B$5:B$251,B4,'Assignments Formulas'!R$5:R$251))/2 It works great for part of my needs, but not for all. In the next column I need to do essentially the same thing plus divide R$5:R$251 by Q$5:Q$251 but everything I have tried comes back as an error. I'd really appreciate some help with the syntax since I am pretty sure it can all be done, but I just don't know what I am doing! If not- I realize I can add additonal columns and then hide them to simplify the fo...

How do I copy only subtotals to a new sheet?
Not including selecting each one individually. Select the range, press F5, select special and visible cells only, copy and paste Regards, Peo Sjoblom "darlinvee" wrote: > Not including selecting each one individually. Hi The way I do it is to set the grouping to display just the totals. Hit F5 and from Special select visible cells. Copy and Edit|Paste Special|Values where you want them to go! Andy. "darlinvee" <darlinvee@discussions.microsoft.com> wrote in message news:96DBD597-C934-4198-8407-370DB3369593@microsoft.com... > Not including selecting ...

new excell will not read old excell file
I have just installed office home and student into a new computer but when try to run one of my existing file it tells me it is corrupt. It runs fine in the old computer. please help What versions of Excel are involved? XL2007 can read and write both the new formats (xlsm, xlsx, xlsb) and the old format (xls). Unless you have the Office Compatibility Pack (http://support.microsoft.com/kb/924074/en-us) installed, XL2003 can read only xls files -- it cannot read the new file versions. Since 2003 has no knowlege of the new formats, it assume the file is corrupted. Of course, a file...

Reintstalling Office 03 on a new machine, Excel does not appear
Hi, Had to put a new hard drive in my notebook, and reinstall XP home and all my software. Reinstalled Office from original disks, and most of it works. Word is fine. However Excel does not appear in Start/All Programs or anywhere else. Tried three times. What's up? Mike Try Start>Run excel.exe Anything? When you re-installed was it a "typical" or a "custom"? Start>Settings>Control Panel>Add/remove programs. Scroll down to Office 2003 and change/remove. Select change. Follow your nose from there and you can see if Excel has been installed. Hav...

exchange 2003 mailbox can't read new mail
Hi all I have a problame - My Exchange 2003 sp2 hase started to get mail but i can't see the mail , Ihave chacked the mailbox DB' IT IS OK, I did disasterrecovery, but the problam is the same, i can see old mail but not the new mails ? What can it be ? ???? <@discussions.microsoft.com> wrote: > Hi all > I have a problame - My Exchange 2003 sp2 hase started to get mail but > i can't see the mail , Ihave chacked the mailbox DB' IT IS OK, I did > disasterrecovery, but the problam is the same, i can see old mail but > not the new mails ? > What can it be...

Rows to Columns
I have data arranged in rows across a spread sheet, for example B2, C2, D2, etc. How can I change that to a column, for example A1, A2, A3, etc without reentering all the data? Thank you --Select B2:D2. Copy the range --Select A1. Right click>PasteSpecial>Transpose>OK If this post helps click Yes --------------- Jacob Skaria "Walter Seaton" wrote: > I have data arranged in rows across a spread sheet, for example B2, C2, D2, > etc. > How can I change that to a column, for example A1, A2, A3, etc without > reentering all the data? >...

65536 Rows for how long????
Hi there! I was wondering: is Microsoft planning on expanding the 65536 row limit? They most deffinitely should! Best regards, Albert "Albert" <Albert@discussions.microsoft.com> wrote > I was wondering: is Microsoft planning on expanding the 65536 row limit? > They most deffinitely should! Why "certainly"? -- Bob http://www.kanyak.com Well, not "certainly", but its certainly my opinion. I think 65536 is not enough. I believe that, among other things, Excel was made for managing large amounts of information efficiently. I think that in this d...

Including number format in formula
I have a simple "if/then/else" formula that I would like to display the result of the "else" portion with no decimal places showing, instead of the default value of the cell. Can I include the format in the formula? Here is my formula: =IF(D12>75,D12/E12,D12) Thanks, -- Zilbandy - Tucson, Arizona USA <zil@zilbandyREMOVETHIS.com> Dead Suburban's Home Page: http://zilbandy.com/suburb/ PGP Public Key: http://zilbandy.com/pgpkey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Hi, Try =IF(D12>75,D12/E12,INT(D12)) or replace Int with roun...

change range of relative formulas to absolute
Excel 2003. I'd like to change an entire range of cell formulas from relative to absolute. Mark You would need VBA to make global changes to cell references. Here are four........ Sub Absolute() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsolute) End If Next End Sub Sub AbsoluteRow() Dim cell As Range For Each cell In Selection If cell.HasFormula Then cell.Formula = Application.ConvertFormula(cell.Formula, _ xlA1, xlA1, xlAbsRowRelColumn) ...

Barcode Formulas
I am trying to make a barcode that will enter a static time into a cell and move to the next cell (right) I have 3 of 9 barcode fonts. There is no = sign nor is there a symbol for it. Does anyone know how to do this. Other choice would be a button on a toolbar but would like to not have the person return to the computer each time to enter. Any help please. pgriff -- pgriff ------------------------------------------------------------------------ pgriff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26189 View this thread: http://www.excelforum.com/showth...

Formula entries get turned into textual entries
I received a workbook from another company and wanted to add a new column of formulas. However, even though I included the = sign and ensured the correct formula, my entry into the cell would suddenly convert it to text, showing a ' or ^ character in front of the = sign. I discovered if I clear all formatting and data entry from the cells, it would then allow me to enter formulas. I've also found that if I create new workbooks/spreadsheets, this does not happen. I experience the same odd behavior in 2003 and 2007. This suggests there's something wrong with the file the other compan...

Two formulas in on cell based on two numbers in another cell?
Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

Text Formula Question
In COL A, I have a list of 2 and 3 digit charactors. I would like to seperate each string into the three adjacent columns. KQ AJs TT 89s KQ K Q AJs A J s TT T T 89s 8 9 s I know that I can use the Left function for the first col, but cant figure out how to get just the middle or third letters. Thanks in advance, Andrew Check your last post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------...

adding a new user to email
okay so i have tried so many time to add my husband onto outlook email but for some reason it will not allow me to do so and it really is driving me mad! this is the steps i am taking... Does he have a separate account? Have you added this account from Control Panel->Mail Icon? What version of Outlook are you using? BTW - the steps you listed are going to elicit the same problem every time. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. A...

sending previous "contacts" to new "contacts" folder
I've attempted to send my old contact list to 'Windows mail' but have not been successful at this point. What am I doing wrong? Bonnie What do you mean by "send" your old contacts? Where are the old contacts located, on a different computer? If so, which mail program were you = using on the older computer? --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "Bonnie " <tallhyland@verizon.net> wrote in message = news:O66XmaUbKHA.4708@TK2MSFTNGP02.phx.gbl... > I've att...

How can I color every other row
Help please. I want to make it easier to use a large spreadsheet where two rows are used for each record. Filling in the background color of every second row prevents mistakes when entering data. I want to color only the used range, not the entire row. I recorded a macro and got the following: TheRange.Activate With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With I really appreciate the help. On way: Dim iCtr As Long With ActiveSheet.UsedRange For iCtr = .Row To .Rows(.Rows.Count)....