-1

I've got a Google Sheet that I'm trying to write some formulas for - it's basically just a bunch of numbers in columns that I'm trying to do some conditional formatting on (per column).

I want to be able to apply the conditional formatting formulas across every column of the sheet, thus my use of anchors ($) in some of the formulas. Below is what I've got so far, but I know these aren't correct both from the standpoint of the formula itself as well as missing the use of certain functions that would make them completely correct based on the cell contents (or lack thereof). Here are the formulas and their description/requirements.

1. Find the largest numeric value in a given column (ignoring empty cells)

What I have:

    =A1=LARGE(A$1:A$1022,1)

I would like this (and all the other formulas mentioned here) to apply only to non-empty cells that have a numeric value in them.

2. Find the second largest numeric value in a column of non-empty cells

What I have:

    =A1=LARGE(A$1:A$1022,2)

3. Find the 3rd largest numeric value in a column of non-empty cells

What I have:

    =A1=LARGE(A$1:A$1022, 3)

4. Any non-empty cell with a numeric value that is greater than or equal to the value in another (A23) cell

What I have:

    =IF(A1 >= 0 + A$23, ISNUMBER(A1))

5. Any non-empty cell with a numeric value that is less than one (A23) cell's value, but greater than or equal to the value in another (A24) cell

What I have:

    =IF(A1 < 0 + A$23, A1 >= 0 + A24, A1 > 0)

6. Any non-empty cell with a numeric value that is less than the value in another (A24) cell

What I have:

    =IF(A1 > 0, A1 < 0 + A$24, ISNUMBER(A1))

I'm pretty sure my use of IF() isn't what I'm supposed to be doing, but it seems to work whenever there are numeric values in all of the cells in the column. I'm not sure which functions I should be using to test for the cell being empty and if that should be wrapped inside or outside of something like ISNUMBER() or whatever.

0

To test if a cell is being empty use ISBLANK

Note Testing cells having an empty string "" or any non-visible character will return FALSE

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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