New answers tagged

1

You're on the right track, just get rid of If: the custom formula should be =And((E2+7)>Today(), H2="No") Explanation If requires at least two arguments: condition and the result to be returned. Here you just have a condition, so if is neither suitable nor needed. The formula used for conditional formatting should return a boolean value (True/False), ...


0

I think this is what you meant: Current item is THIS Select for all items whose A = this.A and whose B = this.B Check the rank of THIS in the list of all matched items. The appropriate code, in your case is: =match(F2, sort(filter(F$2:F,C$2:C=C2,I$2:I=I2))) Let's break it down: filter(F$2:F,C$2:C=C2,I$2:I=I2) gives you a list of all column F values ...


0

Given the numbers 8.97 11.57 14.17 16.76 19.36 compute the midpoints between them (using the spreadsheet or otherwise): 10.27 12.87 15.465 18.06 If a number is less than 10.27, then the closest value to it is 8.97. Otherwise: if the number is less than 12.87, the closest is 11.57. Otherwise... and so on. This logic is expressed in conditional ...


3

You need =E$1 there. (Or, =$E$1 if the formatted range spans multiple columns.) Putting E1 would compare cells to the string "E1", which is not what you want. Putting =E1 compares the first cell in your column range to E1, the second to E2, the third to E3, etc -- because the reference is relative. The dollar sign makes it an absolute reference.


1

This is similar to Conditional formatting based on comparison with today's date and also with a future date except you want to format another cell, not the one with the date. Select the range of names (for example, A2:A10) Apply conditional formatting with Custom formula... =B2<TODAY(). Here B2 appears because it is the correct cell to look at when ...



Top 50 recent answers are included