Applying a formula to a group of cells

Question

I'm trying to do a formula where it is CELL / 127.05 - 1 and apply this to columns H-Y and rows 2-455. I'm not really familiar with excel and am going about this calculation cell by cell. Also, I'm running into a "circular" problem where certain cells rely on another, if anyone could explain this.

Thanks ahead of time!

Answer 1

A formula in a cell generally cannot refer to itself. If you want to apply an operation to an existing range of data, you can, but it is quite rare and surely not in the spirit of a spreadsheet app.

Regarding your question, you could
- enter a value (127.05) anywhere in an empty cell,
- then copy that cell
- then select the range you want to modify
- then select Paste Special / Divide (or any other operation)

Answer 2

One way is to highlight the column (or specific range) you want to apply the formula to, press F2 to access the formula bar, type the formula, and press CTRL+D to paste DOWN if the range is vertical and CTRL+R to paste ACROSS if the range is horizontal. Say that your data looks like this:

    A        B
   ---      ---
    5     A1/127-1
    4
    7
    8

Then in order to copy the formula down, highlight A2 to A4 and press CTRL+D, or highlight B1, and click on the bottom right of the box that comes up surrounding the cell.

If you wanted to simply replace the values in A with their formula values you would still have to use Column B as a 'helper' column, rather than entering the value right into the cell. This is in fact exactly what is giving you the circular reference error.

Regarding the circular error, you may be trying to apply the formula to the cell you are already in. For example, if you are trying to apply the formula A1 / 127 - 1 in the cell A1 Excel won't know what to do because you have specified that the value of A1 is both the value in the specified cell and another value ( A1 / 127 - 1), which can't be true.

Now, the only way I know of to do what you're requesting is with VBA, because I realized just now that I asked a very similar question a while ago which was helpfully answered by Gary. The code was as follows:

Sub Formulate()
    Dim N As Long, i As Long, dq As String
    N = Cells(Rows.Count, "A").End(xlUp).Row
    dq = Chr(34)
    For i = 1 To N
        v = Cells(i, 1).Value
        Cells(i, 1).Formula = "=Text(" & v & "," & dq & "mm/dd/yyyy" & dq & ")"
    Next i
End Sub

Answer 3

As I said above, it sounds like you want to apply that formula to same cell that contains the value you want to act on. That will not work. results cells (i.e. containing your conclusions) will contain the formula and a reference to the cell it will act on. (Although I am using a smaller area for illustration, the principles will apply to your specific application)

Note - I used the randbetween(min,max) function to populate all the data cells. this is why each image contains different data. You of course will use cells containing static data.

For a simple example:

Say you put the value 127.05 in cell A1, and have a range of data cells, like this:

In cell F1, enter = b1/$a$1 - 1 like this:

Note, the $ signs tell Excel to use a static location cell reference. After hitting enter, the value -0.85045 will appear. Now, click and hold your mouse starting in that cell, and drag your mouse down to row 14 release the mouse button and hit keys <ctrl><d>. Your sheet should look like this:

Hold down the shift key while the column is still selected, and hit the right arrow key 3 times, Your sheet should look like this:
release the shift key and while the cells are all highlighted, hit keys <ctrl><r>. The results are here: