SOLVED

# Help with dragging formula

Copper Contributor

# Help with dragging formula

I've setup a table with units sold listed horizontally, and price per unit listed vertically. I am trying to calculate the revenue based on the number units sold and the price received per unit. I will attach a photo of the table for reference. If in cell B3 I take =A3*B2 and then drag that formula to the right into cell C3 it populates the formula in cell C3 as =C2*B3. I want it to populate as =C2*A3. Is there any way to make this formula so it drags the way I want it to? Working with another large document, it will take a looong time to manually type each formula the way I want it to.

Thanks

3 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Help with dragging formula

In B3 enter:

=\$A3*B\$3

This you can drag down (will keep the row number fixed to 3) and across (will keep the column fixed to A).

However, when you are on a modern Excel version you can enter this:

=A3:A7*B2:H2

in B3 and all results will be spilled out in one go without the need to copy down and across. Just make sure that all other cells in B3:H7 are empty.

# Re: Help with dragging formula

I hadn't especially thought of it but, in using defined names, I am almost always using absolute referencing.

``````= price * unitsSold

=\$A\$3:\$A\$7 * \$B\$2:\$H\$2``````

If relative references and dragged formulas ceased to exist, it might be years before I noticed!

Then again, perhaps the howls of protest and pain from the rest of the community would get through.

# Re: Help with dragging formula

Great, that is exactly what I need! Thank you

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Help with dragging formula

In B3 enter:

=\$A3*B\$3

This you can drag down (will keep the row number fixed to 3) and across (will keep the column fixed to A).

However, when you are on a modern Excel version you can enter this:

=A3:A7*B2:H2

in B3 and all results will be spilled out in one go without the need to copy down and across. Just make sure that all other cells in B3:H7 are empty.