4

I'm trying to color the header if all of the cells in that column are numbers. Blank or non-numbers should break this.

I have:

custom formula: =ISNUMBER(A3:A)

But it styles the cells instead. Not sure how to specify the header cell within this formula.

| improve this question | |
3

Apply conditional formatting with custom formula

=arrayformula(sum(N(not(isnumber(A3:A)))))=0

This can be applied even to a range of headers such as A1:E1; the formula, entered as above, will be interpreted in a relative way for other columns.

Explanation

  • not(isnumber(...)) is true if the entry is not a number
  • N converts logical true/false to integers 1 or 0
  • sum adds these.
  • arrayformula makes sure the entire range is handled
  • the sum is equal to 0 if all entries are numbers.

Note

Dates and times are considered numbers, because internally, they are. For example, the date 05/26/2016 is the number 42516 formatted as a date.

| improve this answer | |
0

The way I managed to do this, even though it will be very cumbersome if you have multiple rows that you need to check, is by using the following equation coupled with the Conditional Formatting feature (Format > Conditional Formatting):

=IF(AND (ISNUMBER(n), ISNUMBER(n1), ISNUMBER(n2)), True, False)

Where "n" is whatever cell you're checking.

enter image description here

| improve this answer | |
  • That is unfortunate, and I will test your method, but I do recall reading something about array support in google sheets; is that applicable? – user179561 May 26 '16 at 19:51
  • I'm not sure about the use of arrays in Google Sheets. They have a good developer portal that might be worth checking out. – user108770 May 27 '16 at 21:29

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.