You are here:

Excel/Complex Automatic Math Functions in Excel

Advertisement


Question
QUESTION: Hi,

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.

Best,

Nick

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,


Nick

Answer
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!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.