I know enough of Excel to do what I need to do for my job and am completely self taught so there’s no book for me to reference here. The help function offers me nothing. Here’s what I’m trying to do:
Let’s say I have values in the cells such that
in A1 has a 3 ** and B1 ** has a 5 A2 has a 6 and **B2 ** has a 4
Now I want to find the product of A1 and B1 which is **=product(A1:B1) **
this gives me a product of 15 in cell C1.
Then I move down to the next row and I have to to the same with 6 and 4.
Ok, easy enough. The problem is I have 680 lines in my document and I have to find the product on each one. I cannot, for the life of me, figure out how to repeat a function or set up some one-click function so that Excel knows that when I am in the “C” column that it is supposed to give me the product of A# and B#. I have to go through and set up each line manually…680 times. I am ready to gouge out my eyes with a mellon baller. I can’t believe that what I believed to be such an extensive program to do all these magical things I’ve needed , has now decided to defy my simple commands. Chao get angry!!!
Eh? All you need to do is copy and paste your formula down the column, and it will automagically update the formula appropriately (well, unless you’ve added some spurious $ signs in your formula).
Select the cell with the original formula (C1 in your example). Copy using ctrl-c. Select all the other cells in which you want answers (C2, C3 etc.) and paste the formula with ctrl-v. The formlae in the target cells should be adjusted for the row they are on.
Even easier, once you have the product formula in cell C1, move your pointer to the black dot on the bottom right corner of the cell C1. Hold down your left mouse button and drag all the way down.
Also, take a beginner’s class on Excel. It will save you sooooo much time. And then you can use that time for more important things like spending more time at the SDMB.
And for even further info. If your formula is =PRODUCT(A1:B1) it will update the cells correctly down the rows, i.e., the formula stays the same relative to the cell it’s written in. If you use $ signs like this =PRODUCT($A$1:$B$1), then the formula will always refer to A1 and B1.
And sometimes it’s handly to use a single $ to limit relative cells, like =A$1 + B$1. For example, you could start a new column while retaining references to the same starting column.
Even better than dragging (because dragging to fill 680 cells might be a pain for the truly lazy, like me…that takes what, 4 seconds?), you can double click the fill handle. That will autmatically fill the formula down for all the cells that have something beside them on the left.
So put the formula in C1. Select the cell, double click the fill handle, and it will fill all the way to C680. Once it sees there’s nothing in B681, it will stop.
I’m not sure what versions of Excel this works in, but at least everything since 2000.
The Chao Goes Mu
It would behoove you to take a quickie course in Excel, read a book on Excel, or have an experienced user sit down with you for 30 minutes. What you asked about is usually covered in the first 10 minutes of a course or the second or third page of an Excel book. You will come to realize that the power of a spreadsheet is not that you can type in formulas, but that it will save you a lot of work in retyping formulas and to do recalculations for you automatically. Another thing … if you’re going to be copying formulas from one cell to another (either by copying or dragging cells), learn the difference between relative and absolute cell references. That’ll save you a lot of work too. Frankly, it’s kind of mind-boggling how people did any kind of analytical work without spreadsheets in the dark ages before the late 70’s.
I made some good money writing custom software in the late 70s and (very) early 80s doing things that can now be built using a spreadsheet program in 15 minutes. Darn those VisiCalc people, anyway!
Thank you all so much for the help! My boss was thrilled that I was able to complete the report in just a couple of minutes. (He knows nothing at all about operating Excel) The crazy thing, I almost had it figured out but kept doing some brainless damn thing with “auto fill” and screwing it up. :smack:
Although a class would be the most efficacious thing to do, I am a stubborn DIY type and learn best when thrown to the wolves to figure it out on my own. When I get stuck in life I just turn to folks like my fellow Dopers to get me over the speed bump.
I used to have an Excel book to reference but damned if I didn’t lose the thing somewhere at work. I’m too cheap to run to the store and buy another.
At any rate, thanks again for the info and advice.
Is there something about the product function that can’t be achieved by simple multiplication here? I can understand it might be useful if you have more than two values to multiply - i.e. =product(range), but for the example in the OP, =A1*B1 will do just as well, won’t it?
Another useful way of moving around sheets / selecting is to use the END then arrow key key combination, which moves the cursor in the direction of the arrow, until a blank cell is found (if the cursor started on a filled cell), or until a filled cell is found (if the cursor started on a blank cell).
I use the key combination SHIFT+END (down arrow) all the time. It’s fast.
