Repeat formula in new rows

[~~~AAA~~~]

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.

 

[Don Guillett]

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.

 

[Bernard Liengme]

Are there formulas continuously across the rows from C to J?

 

[~~~AAA~~~]

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.

 

[Niek Otten]

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

|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.
|