2

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.

4

Use $ sign to lock range.

$H$1
0

Lock the range it should be like this

=IF(OR(H5>=LARGE(UNIQUE($H$1:$H$11),1), H5>=LARGE(UNIQUE($H$1:$H$11),2)), "top", "")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.