When filling this formula down a column:
=if(OR(H5>=LARGE(UNIQUE(H1:H11),1), H5>=LARGE(UNIQUE(H1:H11),2)), "top", "")
When I copy/paste it from row 5 to row 6 I get:
=if(OR(H6>=LARGE(UNIQUE(H2:H12),1), H6>=LARGE(UNIQUE(H2:H12),2)), "top", "")
H6 is correct, but, for range H2:H12 should stay as H1:H11. The copy/paste is anticipating and changing the formula. Normally that works fine.
How can I alter the formula, or alter the copy/paste process, so that the comparison range is fixed?
Or, perhaps, define the comparison range somehow? The caveat being that I don't want delve too deeply into formulas. Not so much for the KISS principle but in consideration of the learning curve.
Best Answer
Use $ sign to lock range.