Can someone help me with a spreadsheet formula?

Question

I am making a large invoice. Qty x Unit Price = total price.
I am having to put in a formula for each line (A4*F4) (A5*B6) (A6*F6), etc.... This will be time consuming if I want 100 lines. Is there a way to do this more quickly than one formula at a time for each line?

What ETB said is no help. I can't just paste the formula, because I have to go back and change each row number in the formula. That will take a long time also--it would be easier just to type in the formula 100 times than paste it and correct 2 numbers 100 times.

My apologies. I guess I can't give you both best answer. Thanks.

Answer 1

Copy and paste

Do the first row and validate the answer is accurate. Copy the cell where the answer is. Then highlight the equivalent cell in all the other rows and click paste and you are done!

Answer 2

You need some spreadsheeting help. Find someone who is good and have them show you how formulas work.

That being said:

If you enter in a formula and hit enter, then select the cell again and copy it (hit ctrl + c), you can copy the formula AND IT WILL MAINTAIN the relative relationships. PROMISE.

You can also go to the bottom right hand column of hte cell and drag the plus sign down the column, and it fills with the formula with the correct references.

If you don't want the copy to use the relative reference scheme, you use $, this symbol gives ABSOLUTE reference.

You can use it before the row or column to keep these absolute, like this:

($A$4*$F$4) ($A$5*$B$6) ($A$6*$F$6)

Try these NOW until you see how they work. You need the equal sign or plus sine before an equation.

Good luck. Don't spend too much time spinning your wheels before getting a basics level excel refresher from people who are doing the tasks you need to do with excel.

Answer 3

Like the first answerer said...copy and paste.

There is an even easier (if you can believe it) way in newer versions of Excel. After entering the formula in the first cell and pressing ENTER, select that cell. You will notice a little box in the bottom right hand corner of the cell. Grab that box and drag it as far down as you want and it will copy the formula.

Answer 4

Let Q be quantity , U be unit price and T be total price
In Excel worksheet, type
Q U T in adjacent horizontal cells

In cell under the T enter = then click the cell under Q, enter the sign *, click the cell under U, press ENTER button.
In the selected cell you now have a formula to calculate Q X U = T
To repeat this formula down the page, click on cell under T and drag down the page as required.
Formula is then repeated down the page.

Answer 5

Provided the second formula is A5*F5 and not A5*B6, you can:

Select the cell containing A5*F5.
Drag down to highlight as many rows as you need.
Press Ctrl and D to copy down.

The menu equivalent for the last line is:
Edit -> Fill -> Down.

If you have several formulae across one line, you can copy them all down together in the same way.

Answer 6

What ETB said was precisely correct. In Excel, when you cut a formula and paste it into different cells, the references all update so that they are correct relative to the cell into which the formula is pasted. So if in cell G4 I have a formula =A4*F4, when I cut and paste to cell G5, the formula updates to =A5*F5. If you want to override that, use absolute addressing (=$A$4*$F$4).

Answer 7

Click on the bottom right of the cell with the original answer. You will see a small black box in the bottom right corner of the cell. click on that and drag it as far down as you need this will copy the formula using the corresponding rows to get answer.

Answer 8

I'm not sure you understand the first answer. Excel will automatically change the row numbers in all the 'pasted' lines. Try it and see.