multiplying two columns

**COLC** · 03-20-2008, 02:28 AM

Be nice to me I am an Excel Dummy.
I would like somebody to advise me on the following.
Assume column B is 31 cells long with a value in each cell all the way down.
I wish to progressively add a value to column A (say on a daily basis) which is to be multiplied by the constant value in Column B and showing a total in Column C, at the same time showing a total at the bottom of A and C.
Any assistance would be appreciated, please forgive my ignorance.

**Airman** · 03-20-2008, 08:22 AM

Hey COLC, Just in the door myself...

Presume you'll start at A1, B1 & C1 and are using numbers only.
Go to C1 and type this: =IF(A1="","",(MMULT(A2,B2)))
What this means is: IF A1 is Blank then do nothing, otherwise multiply A1 x B1. Next click on C1 and hover over the small black square at the bottom right of the cell (the "Cursor" will change when you are on it), hold the mouse button and drag it all the way down to C31. (This repeats the formula for all the selected cells. (Don't write in these...you'll delete the formulas)

Now go Col A32 (Where you want to total A) and type: =SUM(
Select A31, hold the mouse and drag it up to A1 (You'll see a dotted box covering the selected cells that you want.) let go and hit Return/Enter.this adds A1, A2, A3....A31

Do the same for Col C

How'd I do?

**darkyam** · 03-20-2008, 09:25 AM

The formula in column C could be shortened to =A1*B1. If nothing is in A1, it will just return 0.

**oldchippy** · 03-20-2008, 09:32 AM

Or even

=IF(A1="","",A1*B1) if you didn't want to display a zero

**COLC** · 03-20-2008, 07:44 PM

A B C
1 0.5081644 1.0163288
2 0.5081644 2.540822
5 0.5081644 2.0326576
4 0.5081644 3.0489864
6 0.5081644 2.0326576
4 0.5081644 #VALUE!
Thanks Guys,
I seem to have struck a hurdle at the first!, I have only tried the first part of the formula A X B and as can be seen above the Column C total does not correspond correctly ie A1 X B1 should be 0.5081644 and so on.
Either I have not explained my problem adequately or made an error somewhere.
Any clues?

**darkyam** · 03-20-2008, 08:08 PM

Just a hunch, but it looks like your formula is =A2*B1. If you have text in A7, this would also explain the error.

**COLC** · 03-20-2008, 08:20 PM

The formula used is exactly as provided by airman, (cut and pasted).
No text anywhere at this stage.
I told you I was a Dummy!

**darkyam** · 03-21-2008, 08:48 AM

You should probably try the simpler formulas suggested by oldchippy or myself. MMULT is best used for multiplying one array of cells by another, rather than a single cell by another. If this still doesn't work, please post your workbook.

**oldchippy** · 03-21-2008, 12:06 PM

Hi COLC,

Does this do what you want?

**COLC** · 03-21-2008, 07:21 PM

Many thanks guys problem solved, easy isn’t it when willing hands are available?
My final problem with his project is as per following example.
Column A is a meter reading which increases daily, column B
Is the daily total i.e. the difference between A1 and A2 etc.
Can Excel auto calculate column B from data entered into
column A

25.5 1
27 1.5
28.5 1.5
30 1.5
45 15
66 21
75 9
89 14
101 12

**darkyam** · 03-21-2008, 08:13 PM

In B2, =A2-A1

**COLC** · 03-22-2008, 12:37 AM

Thanks Darkyam, I tried your suggestion however I could not get it to work, probably because there is a conflict due to column C being multiplied by D which is multiplied by E which is multiplied by F.
Am I correct in assuming that the correct formula be applied as a whole rather than individually.
If so further info would be appreciated, as you can see the first row is complete.
Thanks heaps I assure you I am not normally this thick!!

A--------B---------C-------D--------E-------F---------G
DATE READING C MTRS X 38.3= Mjoules TARIFF DAILY $$$
1 26 4 38.3 153.2 0.508164 2.032656
2 38.3 0 0.508164 0

**oldchippy** · 03-22-2008, 06:18 AM

Hi,

Can you post an example of your data, it may be easier to understand what you are trying to achieve.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**COLC** · 03-22-2008, 07:32 AM

Thanks for that OC, I have dudded up a worksheet and attached as suggested.
This is exactly what I am after with the exception that when I add a value to Col B I require the difference between the new cell and the previous cell entered in Col C, hope that is clear enough.