The Sanctuary
Technology => Technophiliacs & Technophiles => Topic started by: RustyShackleford on November 05, 2014, 09:07:37 pm
-
So say I have three collumns: A B C
How can I make it so that A1, B1, C1 are headers and C2 is the product of A2 and B2, C3 is the product of A3 and B3, etc. Essentially Cx = Ax * Bx. I haven't used excel in ages and I honestly can't even put this into a statement that I can google. Surely this is possible?
I can do it manually to each individual row, but I am looking for autonomy.
-
Click in C2, hit =, select PRODUCT from the list of functions, then it should automatically grab the two cells next to it. If not, select A2 and B2 for the cells to multiply. That gets you the first one.
For the rest of the column (auto copy formatting), click in the cell, hover on the lower right corner of the cell until you see a dark fat + instead of your pointer. Drag this down as far as you want to copy the format. It'll substitute the proper row numbers in the PRODUCT function.
-
You can also type in C2,
=A2*B2
and that will do the same thing as the PRODUCT function. Then drag the formulas down as RustyShackleford mentioned. You can also double-click on the little box on the lower right corner of the cell instead of dragging it and it should auto-populate the rest of your array, assuming there are no gaps.
-
Perfect, thanks guys. Dragging / double clicking the little black box was what I was missing.
-
So I have another question. Say I wanted to make the formula Z += Ax * Bx until reaching some specified or unspecified value of x. In excel speak the first iterations would be (A1*B1)+(A2*B2), I'm not sure how to tell excel that I want to pull information from the entire columns - I can't just drag as I did in the last example. I tried doing something like SUM(A1:A5 * B1:B5), but this doesn't work.
-
So I have another question. Say I wanted to make the formula Z += Ax * Bx until reaching some specified or unspecified value of x. In excel speak the first iterations would be (A1*B1)+(A2*B2), I'm not sure how to tell excel that I want to pull information from the entire columns - I can't just drag as I did in the last example. I tried doing something like SUM(A1:A5 * B1:B5), but this doesn't work.
I don't know if you have to do it this way, but my approach would be to make a column whose cell's values are Ax * Bx where x is the row of the cell and then get the number you're looking for by taking the sum of that column. You can just hide the col if you don't want or need to see the intermediary values
-
I'd do the sums into a separate set of cells and then run the calculation on them.