Using one row for multiplication
Posted by Jason on January 17, 2002 7:10 AM
I am trying to find an easy way to set up the following.
ROW 1 is a row of prices
In each column there is the number of items ordered
I need to multipy the number of items ordered by the corresponding price for that column. I have to go back to the same ROW 1 for the prices for ever one of them.
basically I need to do =A1*A4+B1*B4+C1*C4 at the end of one row and for the next row, =A1*A5+b1*B5+C1*C5. Does anyone have any ideas about how to do this quickly for each row?
Posted by Eric B on January 17, 2002 7:18 AM
Try "=A$1*A4+B$1*B4+C$1*C4" (no quotes), and copy down as needed (NT)
Posted by Mark W. on January 17, 2002 7:18 AM
Use =$A$1*$A4+$B$1*$B4+$C$1*$C4 or the array
formula, {=SUM($A$1:$C$1*$A4:$C4)}, and copy
down as far as needed.
Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.
Posted by Mark W. on January 17, 2002 7:21 AM
More elegant than my other recommendations...
ERR
Posted by Mark W. on January 17, 2002 7:23 AM
Oops!... Made a typo... here's the correction...
Posted by Jason on January 17, 2002 7:52 AM
I trust that you understand that you can't use
3-D references in array formulas and that COUNTIF
cannot be used with 3-D references. If the cells
in question are either blank or contain only "Y"
then use the =COUNTA(Sheet2:Sheet50!C7). If the
cells contain a "Y" or "N" then you'll have to
rethink your worksheet design. You could change
the Y's to 1 and the N's to 0, format the cells
as [=1]"Y";[=0]"N" and use =SUM(Sheet2:Sheet50!C7).
