Changing a variable horizontally in an excel formula

Question

I want to change a variable in an excel formula horizontally and maintain a single variable constant.

O3/$C18, P3/$C18, Q3/$C18

I can save the lower variable constant using the $ character, but when I want to expand the formula to additional cells, the upper variable does not change horizontally, but vertically.

Thank you for your help. Greetings

Answer 1

Dollar signs in excel "fix" element on the right.

$ C18

means that C will always be fixed, and 18 can change

C $ 18 means that C may change, but 18 is always fixed.

$ C $ 18 means that C is fixed and 18 is fixed (i.e. always use C18 no matter which direction you drag the cell)

I don’t fully understand what you want to do, but hopefully the above will help

based on the comments below i think i understand what you mean

try this formula

 =INDIRECT(ADDRESS(1,ROW()))/$C$18 

here address takes a row and column, so row = 1 and column =, of which you are always (so row1 = A, row2 = B, etc.)

then indirect allows you to use this as a control point

Hope that works

Answer 2

If you understand correctly, you want to copy the formula vertically, but refresh the columns as if you were copying horizontally. For example, you want to copy =O3 to the cell below it as =P3 .

In the upper part, consider the Offset() and Row() functions. Say that cells A1, B1, C1 are 1 , 2 and 3 . Try =OFFSET($A$1,0,ROW()-1) . If you copy this formula vertically, the result will be 1, then 2, then 3.

So in your case try =OFFSET($O$3,0,ROW()-1) . This probably needs a little adjustment.

Answer 3

Here is another way to do this:

Start with the formula in this form:

 =O$3/$P$18 

Copy and paste it so that you get:

 =O$3/$P$18 =P$3/$P$18 =Q$3/$P$18 

Copy the two nested formulas and select the cell below the first formula.

Then do a Paste Special / Transpose , which can be accessed by right-clicking on the selected cell (that is, under the first formula you entered), and then selecting the button, the cell range is flat and then vertical.

Finish by deleting the formulas in the cells you just copied.