I am trying to come up with an easy to repeat formula (e.g. when adding a new row the formula is repeated) for calculating the totals in the row highlighted yellow (see attachment).

I am currently using the formula (below) in row '29'. The formula is specific to column 'B' for total in 'B29' (highlighted green in the attachment).

Currently I am having to retype the formula for each new row I add and I am pretty sure the formula is in a 'bad' format.

"=L15(B3*$I$3)+(B4*$I$4)+(B5*$I$5)+(B6*$I$6)+(B7*$ I$7)+(B8*$I$8)+(B9*$I$9)+(B10*$I$10)+(B11*$I$11)+( B12*$I$12)+(B13*$I$13)+(B14*$I$14)+(B15*$I$15)+(B1 6*$I$16)+(B17*$I$17)+(B18*$I$18)+(B19*$I$19)+(B20* $I$20)+(B21*$I$21)+(B22*$I$22)+(B23*$I$23)+(B24*$I $24)+(B25*$I$25)"

Any help much appreciated.

Thanks
James

"jamesplaw" wrote:
I am currently using the formula (below) in row '29'. The
formula is specific to column 'B' for total in 'B29'[....]
Currently I am having to retype the formula for each new row
I add and I am pretty sure the formula is in a 'bad' format.
"=L15(B3*$I$3)+(B4*$I$4)+[....]+(B24*$I$24)+(B25*$I$25)"

I don't know what operator is missing after L15, but you might try something
like:

=L15*SUMPRODUCT(B3:B25,$I$3:$I$25)

If you are careful to __insert__ rows after row 3 and before row 25, Excel
will adjust the ranges in the formula automagically.

On Tue, 17 Jan 2012 15:57:15 +0000, jamesplaw wrote:


I am trying to come up with an easy to repeat formula (e.g. when adding
a new row the formula is repeated) for calculating the totals in the row
highlighted yellow (see attachment).

I am currently using the formula (below) in row '29'. The formula is
specific to column 'B' for total in 'B29' (highlighted green in the
attachment).

Currently I am having to retype the formula for each new row I add and I
am pretty sure the formula is in a 'bad' format.

"=L15(B3*$I$3)+(B4*$I$4)+(B5*$I$5)+(B6*$I$6)+(B7* $I$7)+(B8*$I$8)+(B9*$I$9)+(B10*$I$10)+(B11*$I$11)+ (B12*$I$12)+(B13*$I$13)+(B14*$I$14)+(B15*$I$15)+(B 16*$I$16)+(B17*$I$17)+(B18*$I$18)+(B19*$I$19)+(B20 *$I$20)+(B21*$I$21)+(B22*$I$22)+(B23*$I$23)+(B24*$ I$24)+(B25*$I$25)"

Any help much appreciated.

Thanks
James


+-------------------------------------------------------------------+
|Filename: excelformula.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=277|
+-------------------------------------------------------------------+

It looks to me as if the formula is in B28, but that is irrelevant to the solution.

The formula posted above can almost be replaced by something like:

=SUMPRODUCT(B$3:B25,$I$3:$I25)

And if you add columns or rows by Inserting them; or if you drag the formula itself, all the factors should self-adjust.

HOWEVER, your formula as posted is illegal because of that leading L15.

You have, in part: =L15(B3*$I$3)...

But I have no idea what the L15 is doing -- there is no associated operator-- or how that will need to be adjusted when you insert rows or columns.

"Ron Rosenfeld" wrote:
On Tue, 17 Jan 2012 12:35:21 -0800, "joeu2004" wrote:
[....]
Wierd. I did not see your post when I posted mine,
but I see the time stamp is 38 minutes earlier on yours.

Displayed timestamps can be deceiving. The clock on my system could be
wrong. For example, this message will appear to be posted on 18 Jan 2012
01:23 AM, apparently a full day before your posting to which I am replying.

(You might even miss it altogether, depending on how you sort things in your
newsreader.)

Fortunately, my newserver adds a header of the form Injection-Date: Thu, 19
Jan 2012 15:44:07 +0000 (UTC). So you might be able to determine the true
date/time when I posted.

But of course, that does not explain what you experienced.

The real answer is: we seem to use different newservers. Delays arise due
to the frequency in which one newserver polls the other. This is not
uncommon.

Ron

Just a guess, It maybe that the OP is using L15 as the value to define
the sum of each of the cells along row 28.

Using this I created the following.

=SUMPRODUCT(($I$3:$I$25=$L$15)*(B$3:$B$25))

Each time the user enters the desire interest rate in L15 it will change
the totals along row 28.

We will have to wait till the OP gets back with a clearer explanation.

Cheers
Mick.

On 18/01/2012 8:13 AM, Ron Rosenfeld wrote:
On Tue, 17 Jan 2012 15:57:15 +0000, wrote:


I am trying to come up with an easy to repeat formula (e.g. when adding
a new row the formula is repeated) for calculating the totals in the row
highlighted yellow (see attachment).

I am currently using the formula (below) in row '29'. The formula is
specific to column 'B' for total in 'B29' (highlighted green in the
attachment).

Currently I am having to retype the formula for each new row I add and I
am pretty sure the formula is in a 'bad' format.

"=L15(B3*$I$3)+(B4*$I$4)+(B5*$I$5)+(B6*$I$6)+(B7*$ I$7)+(B8*$I$8)+(B9*$I$9)+(B10*$I$10)+(B11*$I$11)+( B12*$I$12)+(B13*$I$13)+(B14*$I$14)+(B15*$I$15)+(B1 6*$I$16)+(B17*$I$17)+(B18*$I$18)+(B19*$I$19)+(B20* $I$20)+(B21*$I$21)+(B22*$I$22)+(B23*$I$23)+(B24*$I $24)+(B25*$I$25)"

Any help much appreciated.

Thanks
James


+-------------------------------------------------------------------+
|Filename: excelformula.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=277|
+-------------------------------------------------------------------+

It looks to me as if the formula is in B28, but that is irrelevant to the solution.

The formula posted above can almost be replaced by something like:

=SUMPRODUCT(B$3:B25,$I$3:$I25)

And if you add columns or rows by Inserting them; or if you drag the formula itself, all the factors should self-adjust.

HOWEVER, your formula as posted is illegal because of that leading L15.

You have, in part: =L15(B3*$I$3)...

But I have no idea what the L15 is doing -- there is no associated operator-- or how that will need to be adjusted when you insert rows or columns.

On Tue, 17 Jan 2012 12:35:21 -0800, "joeu2004" wrote:

I don't know what operator is missing after L15, but you might try something
like:

=L15*SUMPRODUCT(B3:B25,$I$3:$I$25)

If you are careful to __insert__ rows after row 3 and before row 25, Excel
will adjust the ranges in the formula automagically.

Wierd. I did not see your post when I posted mine, but I see the time stamp is 38 minutes earlier on yours.
-- Ron

"Ron Rosenfeld" wrote:
On Tue, 17 Jan 2012 12:35:21 -0800, "joeu2004" wrote:
[....]
Wierd. I did not see your post when I posted mine,
but I see the time stamp is 38 minutes earlier on yours.

In case you overlook it, see my response purposely dated incorrectly as Wed,
18 Jan 2012 01:23:09 -0800.

On Wed, 18 Jan 2012 01:23:09 -0800, "joeu2004" wrote:

Displayed timestamps can be deceiving. The clock on my system could be
wrong. For example, this message will appear to be posted on 18 Jan 2012
01:23 AM, apparently a full day before your posting to which I am replying.

(You might even miss it altogether, depending on how you sort things in your
newsreader.)

Fortunately, my newserver adds a header of the form Injection-Date: Thu, 19
Jan 2012 15:44:07 +0000 (UTC). So you might be able to determine the true
date/time when I posted.

OK, trying to sort this out.

Your message, with the OP's solution appears to be posted at:
17 Jan 2012 3:35 PM

By viewing headers, I can see:
Injection-Date: Tue, 17 Jan 2012 20:35:22 +0000 (UTC)

My response appears to have occurred at
17 Jan 2012 4:13 PM

By viewing the headers on that message, I see:
NNTP-Posting-Date: Tue, 17 Jan 2012 15:13:33 -0600
which I would think would be 21:13:33 UTC.

So it appears that the displayed times are probably for real.

I guess it just took a while for your response to get to my news server. Odd.