Forum Discussion
Formula repeating when dragging down
Hi all,
When I drag my formula down, it is repeating the same formula (ie referencing the same cells as in the first row) rather than referencing the appropriate cells for the row.
In the screenshot above, when I drag the formula in the "Occurrence" column, the formula reference cell C2 the whole way down (highlighted in red in the "Formula in..." column. Surprisingly, the cell referenced in the second part of the formula (highlighted in green) is referencing the correct cell automatically (this is not the case in my actual data set that I'm working on).
I've tried manually entering the correct cells to reference for the first 3 rows, and then dragging the formula down the column, but it will simply repeat the first 3 formulas throughout the column.
I've also tried entering the bin value manually (increasing by increments of 5 for each row) and dragging that down, but again it will just repeat the formula entered.
My actual data set has +/- 140 bins, so I would like to avoid having to enter the formula manually for every single row.
Workbook calculations is set to automatic.
Hopefully I've explained my issue adequately - look forward to hearing your responses!
Not sure I understand. But if you want to fix P8, P9, P10, use absolute references $P$8 etc.
AlexJamieson Try this in D2 and copy down:
=COUNTIFS($A$2:$A$31,">="&C2,$A$2:$A$31,"<"&C3)- You are my hero. Can you please explain the changes you made to the formula? What is the function of the "&" symbol?
AlexJamieson If you would be comparing a range to a fixed value, let's say 1300, you could use ">=1300" as the criteria. But when you want to link the value to a cell reference, you need to write it like ">="&C2. That will make the cell reference dynamic and change when you copy the formula down.
