Forum Discussion

Copper Contributor

Mar 16, 2023

Solved

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 wi...

Formulas and Functions

Riny_van_Eekelen
Mar 17, 2023
notanexpert
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.

Riny_van_Eekelen

Platinum Contributor

Mar 17, 2023

notanexpert

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.

PeterBartholomew1

Silver Contributor

Mar 17, 2023

Riny_van_Eekelen

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.