A "constant" in google spreadsheet?

Question

Say I have 2 columns: (this is extremely simplified)

Data = a number
Result = Data * 1.2

I can put B2 = A2*1.2, then drag and drop B2 down...

and it fills all the other cells, which is perfect.

But can I put this multiplier (1.2) somewhere as a "constant"? (for clarity and being easily editable)

Say I put it in E1, and set B2 = A2*E1.
Now I can't drag and drop anymore (because E1 becomes E2 E3 and so on)

In this example, is there a way to make E1 stay as you drag it down?

score 64 · Accepted Answer · answered Apr 04 '16 at 12:16

Short answer

Use an absolute cell reference or a named range

Explanation

Instead of E1, which is a relative cell reference, use $E$1 which is an absolute cell reference.

An alternative is to to assign a name to the cell E1, let say, "constant"

In the first case the formula will be

=A2*$E$1

In the second case

=A2*constant

References

score 4 · Answer 2 · answered Oct 29 '20 at 19:57

I understand that you are asking for "A “constant” in Google spreadsheet?" and later on a "drag down" solution.

You can use the accepted answer, OR ...
Instead of having to drag down (increasing the number of formulas)

Use a single formula in `B2`

=ArrayFormula(A2:A6*E1)

You can even use it for future entries or now blank cells.

=ArrayFormula(IF(A2:A11<>"",A2:A11*E1,""))

How the formula works

The ArrayFormula will apply the calculation cell*E1 in every single row, IF the cell is not empty/blank "".
IF it is a blank cell it will return a blank cell "".

Functions used:

A superb solution for working with large datasets where dragging down would be infeasible (and where empty cells prevent auto-fill)! — Michael McGreal, Jul 27 '22 at 00:23

score 2 · Answer 3 · answered Apr 04 '16 at 07:17

Can you try this in your formula?

Cell B2 type

 =A2*$E$1

Then there is no need to drag it down on column E, it will all follow E1. I believe this solves the problem.

If you really want to drag it down, then why not just put Cell E2

=E1

So that even when you drag, the value will remain 1.2