Apply a simple formula to multiple rows?
 

Hello Esteemed Gurus :-)

I have a formula for rows in the form

=A10*DJ+E10*HJ+I10*LJ+M10*PJ+Q10*TJ

The J is a variable that represents the row and there are over 100 rows, so
it would be extremely boring
to have to input this formula on every line. I'm exhausted after doing just
10 ;-)

X(J)=A10*D(J)+E10*H(J)+I10*L(J)+M10*P(J)+Q10*T(J), J= Jstart, Jend ???,
where X is the column of results???

Tried =SUM(A10*D+E10*H+I10*L+H10*P+Q10*T) on col X but no success.

Any shortcuts?

TIA

rf

Have a look at HELP index for INDIRECT

--
Don Guillett
SalesAid Software

"RedFox" wrote in message
link.net...
Hello Esteemed Gurus :-)

I have a formula for rows in the form

=A10*DJ+E10*HJ+I10*LJ+M10*PJ+Q10*TJ

The J is a variable that represents the row and there are over 100 rows,
so
it would be extremely boring
to have to input this formula on every line. I'm exhausted after doing
just
10 ;-)

X(J)=A10*D(J)+E10*H(J)+I10*L(J)+M10*P(J)+Q10*T(J), J= Jstart, Jend ???,
where X is the column of results???

Tried =SUM(A10*D+E10*H+I10*L+H10*P+Q10*T) on col X but no success.

Any shortcuts?

TIA

rf

Thank you Don.

I have Excel 2000 v 9.0 and there is no Indirect in the help index..

RF

"Don Guillett" wrote in message
...
Have a look at HELP index for INDIRECT

--
Don Guillett
SalesAid Software

"RedFox" wrote in message
link.net...
Hello Esteemed Gurus :-)

I have a formula for rows in the form

=A10*DJ+E10*HJ+I10*LJ+M10*PJ+Q10*TJ

The J is a variable that represents the row and there are over 100 rows,
so
it would be extremely boring
to have to input this formula on every line. I'm exhausted after doing
just
10 ;-)

X(J)=A10*D(J)+E10*H(J)+I10*L(J)+M10*P(J)+Q10*T(J), J= Jstart, Jend ???,
where X is the column of results???

Tried =SUM(A10*D+E10*H+I10*L+H10*P+Q10*T) on col X but no success.

Any shortcuts?

TIA

rf

From my Excel 97

INDIRECT

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use
INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text,a1)

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a
reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the
#REF! error value.

A1 is a logical value that specifies what type of reference is contained in the cell ref_text.

· If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
· If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Remarks

· If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source
workbook is not open, INDIRECT returns the #REF! error value.

Examples

If cell A1 contains the text "B2", and cell B2 contains the value 1.333, then:

INDIRECT($A$1) equals 1.333

If you change the text in A1 to "C5", and cell C5 contains the value 45, then:

INDIRECT($A$1) equals 45

If the workspace is set to display R1C1-style references, cell R1C1 contains R2C2, and cell R2C2 contains the value
1.333, then:

INT(INDIRECT(R1C1,FALSE)) equals 1

If B3 contains the text "George", and a cell defined as George contains the value 10, then:

INDIRECT($B$3) equals 10

When you create a formula that refers to a cell, the reference to the cell will be updated if the cell is moved by using
the Cut command to delete the cell or if the cell is moved because rows or columns are inserted or deleted. If you
always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is
moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following
syntax:

INDIRECT("A10")


HTH









"RedFox" wrote in message link.net...
| Thank you Don.
|
| I have Excel 2000 v 9.0 and there is no Indirect in the help index..
|
| RF
|
| "Don Guillett" wrote in message
| ...
| Have a look at HELP index for INDIRECT
|
| --
| Don Guillett
| SalesAid Software
|
| "RedFox" wrote in message
| link.net...
| Hello Esteemed Gurus :-)
|
| I have a formula for rows in the form
|
| =A10*DJ+E10*HJ+I10*LJ+M10*PJ+Q10*TJ
|
| The J is a variable that represents the row and there are over 100 rows,
| so
| it would be extremely boring
| to have to input this formula on every line. I'm exhausted after doing
| just
| 10 ;-)
|
| X(J)=A10*D(J)+E10*H(J)+I10*L(J)+M10*P(J)+Q10*T(J), J= Jstart, Jend ???,
| where X is the column of results???
|
| Tried =SUM(A10*D+E10*H+I10*L+H10*P+Q10*T) on col X but no success.
|
| Any shortcuts?
|
| TIA
|
| rf
|
|
|
|
|
|
|

One way, if I've read your post correctly ..

Assuming the variable "J" is starting on the same row 10,
and extending down by 100 continuous rows

Think you could try in say, X10, either:

=SUM(A10*OFFSET(A10,,3,100),E10*OFFSET(E10,,3,100) ,I10*OFFSET(I10,,3,100),M1
0*OFFSET(M10,,3,100),Q10*OFFSET(Q10,,3,100))

(Array-enter the formula above, i.e. press CTRL+SHIFT+ENTER)

Or the slightly longer, but non-array entered SUMPRODUCT version

=SUMPRODUCT(A10*OFFSET(A10,,3,100)+E10*OFFSET(E10, ,3,100)+I10*OFFSET(I10,,3,
100)+M10*OFFSET(M10,,3,100)+Q10*OFFSET(Q10,,3,100) )

(normal ENTER the formula above will do)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"RedFox" wrote in message
link.net...
Hello Esteemed Gurus :-)

I have a formula for rows in the form

=A10*DJ+E10*HJ+I10*LJ+M10*PJ+Q10*TJ

The J is a variable that represents the row and there are over 100 rows,
so
it would be extremely boring
to have to input this formula on every line. I'm exhausted after doing
just
10 ;-)

X(J)=A10*D(J)+E10*H(J)+I10*L(J)+M10*P(J)+Q10*T(J), J= Jstart, Jend ???,
where X is the column of results???

Tried =SUM(A10*D+E10*H+I10*L+H10*P+Q10*T) on col X but no success.

Any shortcuts?

TIA

rf

Clarification: The interp/suggestion presumes that what you're after is the
single result summation of all the 100 lines, viz.:

A10*D10+E10*H10+I10*L10+M10*P10+Q10*T10
A10*D11+E10*H11+I10*L11+M10*P11+Q10*T11
A10*D12+E10*H12+I10*L12+M10*P12+Q10*T12
.....
A10*D109+E10*H109+I10*L109+M10*P109+Q10*T109

where A10, E10, I10, M10 and Q10 are constants

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Big thanks to Rodney and Max

I need to study Rodney's input.
Max is very close. Each line you have is correct. I want a sum for each line
and the column on the right (result column) will contain all the sums I
need. I also discovered an interesting and seemingly simple Fill command.

In the result column I highlight the last sum I previously calculated, with
the formula showing under the menu bar, and the column down to the last row.
Then I go to Edit | Fill | Down. However, I end up with all zeroes in the
calculations. Back to the drawing board :-)

Still trying. Back soon.

Have an enjoyable and successful week :-)

RF
..

"Max" wrote in message
...
Clarification: The interp/suggestion presumes that what you're after is
the
single result summation of all the 100 lines, viz.:

A10*D10+E10*H10+I10*L10+M10*P10+Q10*T10
A10*D11+E10*H11+I10*L11+M10*P11+Q10*T11
A10*D12+E10*H12+I10*L12+M10*P12+Q10*T12
....
A10*D109+E10*H109+I10*L109+M10*P109+Q10*T109

where A10, E10, I10, M10 and Q10 are constants

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

... I want a sum for each line
and the column on the right (result column)
will contain all the sums I need.

If the earlier interp was quite correct, to achieve the above,
think you could also put in say, X10:
=$A$10*D10+$E$10*H10+$I$10*L10+$M$10*P10+$Q$10*T10
and just copy X10 down to X109
(assuming, like before that you have 100 continuous rows to summate from
row10 down)

This will give you the sum for each line in X10:X109

And then just put in say, X9: =SUM(X10:X109)
to get the total for the lot

... Edit | Fill | Down. .. I end up with all zeroes in the calculations
You probably didn't fix the 5 "constant" cells: A10, E10, I10, M10, Q10
with dollar signs, e.g.: use $A$10 instead of A10
before filling down from row10
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Right on the button Max

Everything works like you said it would.

Enjoy the week :-)

rf

"Max" wrote in message
...
... I want a sum for each line
and the column on the right (result column)
will contain all the sums I need.

If the earlier interp was quite correct, to achieve the above,
think you could also put in say, X10:
=$A$10*D10+$E$10*H10+$I$10*L10+$M$10*P10+$Q$10*T10
and just copy X10 down to X109
(assuming, like before that you have 100 continuous rows to summate from
row10 down)

This will give you the sum for each line in X10:X109

And then just put in say, X9: =SUM(X10:X109)
to get the total for the lot

... Edit | Fill | Down. .. I end up with all zeroes in the calculations
You probably didn't fix the 5 "constant" cells: A10, E10, I10, M10, Q10
with dollar signs, e.g.: use $A$10 instead of A10
before filling down from row10
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome !
Glad it worked for you.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"RedFox" wrote in message
link.net...
Right on the button Max

Everything works like you said it would.

Enjoy the week :-)

rf