Excel/Complex Automatic Math Functions in Excel
My name is Nick. I have an excel question. It would be wonderful if you could help.
I would like to be able to input a number in A1, then input another number in B1, and have the product of those two numbers (A1*B1) automatically populate in C1. By automatically, I mean without me having to type the numbers again in C1, or type an asterisk in C1, or anything like that.
For example, I would like to be able to type “5” in A1, then “10” in B1, and have “50” automatically populate in C1, while “5” still remains in A1 and “10” still remains in B1.
As an additional matter, on the same spreadsheet, I would like all the numbers in A1, A2, A3, etc, to automatically get added (not multiplied) together, with the resulting sum populated in a lower cell (A20 should be good). As well, the numbers in C1, C2, C3, etc, should automatically get added together, with the sum automatically populated in C20. I do not care about the sum in column B.
Finally, I would like the figure in C20 (the sum of all figures from the C column) to be automatically divided by the figure in A20 (the sum of all figures from the A column). The resulting figure should automatically be populated in another cell, say D20. So, if the figure in C20 is “10”, and the figure in A20 is “5”, then D20 will automatically populate with “2.”
Can something like this be done?
I have excel 2003 at home and excel 2007 at work. I would prefer an answer that works in 2003, but can obtain other versions of excel if my needs require it.
I fear that this is a difficult question. Much thanks in advance if you are able to offer any help with it.
ANSWER: IF you mean that you would like excel to know automatically what you need to do to numbers, then that won't be possible - however, if you mean that you want to design a sheet that will do this for you then that is EXACTLY what excel does. In C1 you would enter = a1*b1
in A20 put =sum(a1:a19) Copy this cell to C20. In D20 enter =c20/a20
All of this is version independant. Were I designing such a sheet I would also make it clear where data should be entered and use protection to stop my formulas being overwritten.
---------- FOLLOW-UP ----------
QUESTION: Brilliant. It works exactly how I wanted it to.
A couple more questions if I may. And I do plan to donate for your kind efforts.
1st: I would like my sheet to stop at "D", so that if I press "tab" or "right arrow key" in the D1 cell, the cursor automatically highlights the A2 cell, instead of the E1 cell.
2nd: How do I protect my formulas from being overwritten?
Thank you in advance for your help,
Sorry for the delay in replying - it was my daughters birthday party yesterday and the preparations took longer than I'd planned!
By default, all excel cells are set to protected status BUT the sheet is set to unprotected - so to do the first part, select the cells you DO want to edit and then you will need to format the cells - in Excel 2003 this is under the format menu, then cells - and you need to use the protection tab at the end of this dialog box to mark the cells unlocked (no tick in the locked checkbox). This is also the answer to the second part of the question, as you won't have unlocked the cells with formulas - to enable the protection use tools, protection, protect sheet - you have the option to set a password if you wish, or just leave it blank - no password stops inadvertent errors but makes it very easy to unprotect. A password makes it harder to unprotect so if this is being rolled out to a workplace environment where users may try to adapt things, a password is a good idea!
I hope this is clear but let me know if I can help further with this - or any thing else!