Balls and Walnuts
more than you ever wanted to know
As I wipe the spittle off my chin
Cue Deliverance music.
I can’t figure out Excel. I’ve never used a spreadsheet, but I figure, how tough can it be? (Answer: too tough for me, apparently.)
Here’s all I need to do: enter two columns of numbers (A and B) and automatically have each C = A*B. Thus, C1 = A1*B1, C2 = A2*B2, and so forth. I do NOT want to have to do this line by line, since I have a lot of data to enter.
At the very end, I’ll want to sum up all of my C values. I can’t figure out how to define my C column so that every row of C is the product of that row’s A and B.
The in-program help for “function” and “product” doesn’t give me my answer. Any ideas?
D.
Sorry. Can’t help you there. I’ve only done that in Numbers, not Excel.
I have “Works Spreadsheet” too, fwiw.
Did you try the Calculator tool? I just tried it, and it helped my put the sum of A1 & B1 into every C column.
On second try, it put the sum of A1, B1, A2, B2 into every C column.
Did Bill Gates use Excel to create his earliest invoices? Maybe that’s how he got rich. 😉
Do you have Perl? You could export the data in a tab-delimited format, write a Perl script to do the math and save the data in a tab-delimited format, than reimport all that into Excel.
Fill out your As and Bs. Then do =A1*B1 in cell C1.
Then copy cell C1 (Ctrl C) and paste into all your other Cs (Ctrl V). You should be able to paste into the entire column just by highlighting all the cells you want to copy into.
Then at the bottom type =SUM(C1,C?) with the ? the last row of data.
DING DING DING!
Keven, you rock. It worked.
That was not intuitive. But who cares, it’s working now.
Thanks!
In Excel you can also grab the little black dot in the lower right corner with your mouse and drag it down. That means “copy this formula into the cell(s) below it”. It will automatically increment the rows (unless you use $ for non-relative references, but that’s a whole different lesson.)
Ah Kevin got it.
Excel is too smart by half. It somehow knows what you want to do before you do it.
Do you know how to use the “Sigma” tool? If you do not need to know the specific line by line value of A + B = C, then you can just highlight Columns A and Columns B, leaving a blank space at the end of each column, then just click on the “Sigma” icon on the toolbar and it will automatically “Sum” the columns and enter the Sum of each column in the blank space you left open. Hope this helps.
Hey, Doug, I’ve got Excel running 95% of the time I’m at work. Anytime you’ve got a question, feel free to drop me a note – I’ve usually got gmail open so I’ll see it pretty quick.
I LURVE designing spreadsheets. If you ever need help and Chris isn’t at his desk, drop my a line, too.
HER desk, Maureen. That’s the sxKitten yer talkin about. And where have you been, my dear?
I may get in touch with you folks for more info. I’m wondering how to import data from a government spreadsheet that’s in a pdf format — is that even possible?
PDFs are nasty. It may be possible to pull little bits of data out of them, but only via the cut-n-paste method, which is fraught with peril. Also, I believe that many PDFs have copy disabled. It’s one of the reasons people use them. With certain options on, you can send out a document and be reasonably certain that people can’t make changes to it or copy parts of it without a lot of work.
Yeah, that’s what I was afraid of. I did manual data entry for my task.
If you have Adobe Writer, and the original file was also Excel, and it’s not locked, you have a chance of importing the data. Otherwise? Not worth the effort, I’m afraid.
I have adobe writer, so email it to me if it isn’t sensitive or private information and I’ll see what I can do. No promises, though.