SOLVED

Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2705211%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2705211%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20tabulation%20of%20values.%3C%2FP%3E%3CP%3EFor%20each%20line%20in%20the%20table%20I%20am%20wanting%20to%20multiply%20two%20cells%20together%20and%20then%20add%20the%20result%20for%20all%20the%20lines%20in%20the%20table.%26nbsp%3B%20The%20total%20result%20is%20then%20to%20be%20divided%20by%20another%20cell%20in%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERather%20than%20laboriously%20selecting%20each%20cell%20individually%20I%20am%20hoping%20to%20speed%20up%20the%20process%3A%26nbsp%3B%20the%20cells%20chosen%20from%20each%20line%20are%20in%20the%20same%20column%20each%20time.%26nbsp%3B%20(I%20have%20forgotten%20the%20procedure%3B%20I%20am%20sure%20there%20is%20one.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20assistance%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2705211%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2705438%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2705438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142939%22%20target%3D%22_blank%22%3E%40John_Dickson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20a%20fairly%20vague%20description%2C%20specific%20in%20some%20ways%2C%20but%20overall%20quite%20vague.%20So%20I'll%20give%20it%20a%20try%2C%20but%20you'd%20help%20us%20help%20you%20if%20you%20could%20(a)%20be%20more%20specific%2C%20and%20(b)%20post%20a%20copy%20of%20the%20spreadsheet%20that%20you're%20working%20with%20so%20that%20we%20could%20give%20you%20the%20actual%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20assuming%20you%20start%20in%20row%201%20and%20want%20to%20multiply%20A1%20with%20B1%20and%20do%20the%20same%20with%20the%20corresponding%20cells%20in%20rows%202%20through%2010%2C%20start%20with%20this%20formula%20in%20Cell%20C1%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D(A1*B1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20then%20just%20copy%20that%20formula%20down%20to%20the%20other%20rows.%20The%20cell%20references%20will%20adjust.%3C%2FP%3E%3CP%3EThen%20in%20cell%20C11%20enter%20this%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3Dsum(C1%3AC10)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%2C%20assuming%20the%20divisor%20is%20in%20cell%20D11%2C%20enter%20this%20formula%20in%20cell%20E11%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DC11%2FD11%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20there%20you%20have%20it.%20%26nbsp%3B(Assuming%20that's%20what%20you%20meant)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2710033%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2710033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20advice.%3C%2FP%3E%3CP%3EI%20have%20to%20admit%20to%20being%20very%26nbsp%3B%3CEM%3EExcel%26nbsp%3B%3C%2FEM%3Erusty.%26nbsp%3B%20By%20trial%20and%20error%20I%20think%20I%20have%20stumbled%20across%20the%20procedure%20needed%3A%26nbsp%3B%20The%20best%20way%20to%20describe%20my%20previous%20dilemma%20is%20to%20show%20an%20example%20of%20the%20formula%20I%20have%20now%20used%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3D%20(SUM(E35%3AE43*J35%3AJ43)%2FE44)%3C%2FP%3E%3CP%3EI%20would%20be%20most%20grateful%20if%20you%20could%20give%20me%20the%26nbsp%3Bexcel%26nbsp%3B%3CEM%3Ekeyword%3C%2FEM%3E%20which%20defines%20the%20process%20I%20have%20used%20which%20is%20much%20faster%20and%20more%20explicit%20then%20entering%20each%20of%20the%20referenced%20cells%20individually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%26nbsp%3B%20John%20Dickson%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a tabulation of values.

For each line in the table I am wanting to multiply two cells together and then add the result for all the lines in the table.  The total result is then to be divided by another cell in the table.

 

Rather than laboriously selecting each cell individually I am hoping to speed up the process:  the cells chosen from each line are in the same column each time.  (I have forgotten the procedure; I am sure there is one.)

 

Your assistance much appreciated.

4 Replies

@John_Dickson 

 

That is a fairly vague description, specific in some ways, but overall quite vague. So I'll give it a try, but you'd help us help you if you could (a) be more specific, and (b) post a copy of the spreadsheet that you're working with so that we could give you the actual formula.

 

That said, assuming you start in row 1 and want to multiply A1 with B1 and do the same with the corresponding cells in rows 2 through 10, start with this formula in Cell C1

=(A1*B1)

and then just copy that formula down to the other rows. The cell references will adjust.

Then in cell C11 enter this formula

=sum(C1:C10)

 

And then, assuming the divisor is in cell D11, enter this formula in cell E11

=C11/D11

and there you have it.  (Assuming that's what you meant)

@mathetes 

Thank you very much for your advice.

I have to admit to being very Excel rusty.  By trial and error I think I have stumbled across the procedure needed:  The best way to describe my previous dilemma is to show an example of the formula I have now used:

       = (SUM(E35:E43*J35:J43)/E44)

I would be most grateful if you could give me the excel keyword which defines the process I have used which is much faster and more explicit then entering each of the referenced cells individually.

 

Cheers,  John Dickson

              

best response confirmed by John_Dickson (New Contributor)
Solution

@John_Dickson 

 

I think you're talking about a reference to a range of cells. But you're making a bigger deal of it than I've heard before.....which may be that I'm too familiar with it so it's "no big deal" with me. 

 

And because you used the word "procedure" in your first post, I mistakenly assumed you were looking for a series of steps, simple steps, rather than a formula, a single formula. Those are all keywords too.

 

You'd do well, I think, to get yourself a basic text on Excel and just read it. Start to finish. That will bring things back, maybe even advance them.

 

Or browse through this website: https://exceljet.net/glossary/range

 

Thank you for your advice.