Conditional Formatting In Google Sheets: Can I Use Custom Function In The 'custom Formula Is:' Field?


Answer :

Short answer


A custom function can't be used as a part of a custom formula in the Google Sheets' conditional formatting built-in user interface.


Explanation


In Google Sheets, a custom function is like spreadsheet built-in functions but written by the user using the Google Apps Script editor. They can't modify other objects, only return one or multiple values.


By the other hand, a custom function only is calculated when it's added to a cell or when at least one of it's parameters changes. They can't use non-deterministic functions as parameters like NOW(), TODAY(), RAND() and RANDBETWEEN().


Test


In order to test the statement in the short answer, I created a did the following:



  1. Create a simple custom function


function two() {
  return 2;
}


  1. Add the custom function as part of a custom formula in the conditional formatting side panel


=two()=2

Result:
Nothing changed.


References



  • Custom Functions in Google Sheets



I have also found that custom functions cannot be used for conditional formatting. However, I located a pretty simple workaround.



The custom formula that I tried to use is:
=hasGreen(CELL("address",$H3)&":"&CELL("address",$M3))



ie. format a cell based on a range of other cells in that row.



My solution was to place the above formula into column P. Then I changed my conditional formatting's custom formula to =P3



Worked like a charm. Any change to H3:M3 would call hasGreen and update the value of P3. The conditional formatting would note any change to P3, and adjust the formatting.



Comments

Post a Comment

Popular posts from this blog

530 Valid Hostname Is Expected When Setting Up IIS 10 For Multiple Sites

Answer : Solution 1: When configured with two or more hostnames, the correct virtual host name and username must both be sent in the username by the ftp client. Separate the site name and user with the vertical line symbol: | www.example.com|MyUser So, in your FTP Client use this for the username: ftp.telefonievergelijken.nl|tv_ftp Solution 2: It appears that you're attempting to connect to the FTP site using a hostname which is not currently configured in any of the bindings to the FTP site within IIS. I base this only on the error output from Filezilla which you have included, as you have censored the hostname (even in example form) from the output, so there isn't much more to go on. You'll need to configure a binding on the FTP site which matches the hostname you are using to connect to the FTP site (whether that be from Filezilla or any other FTP client). EDIT: From your updated post information, I notice that your bindings for the FTP site are indeed in...
Read more

C Perror Example

Defined in header <stdio.h> void perror ( const char * s ) ; Prints a textual description of the error code currently stored in the system variable errno to stderr . The description is formed by concatenating the following components: the contents of the null-terminated byte string pointed to by s , followed by ": " (unless s is a null pointer or the character pointed to by s is the null character) implementation-defined error message string describing the error code stored in errno , followed by '\n' . The error message string is identical to the result of strerror ( errno ) . Parameters s - pointer to a null-terminated string with explanatory message Return value (none). Example # include <stdio.h> int main ( void ) { FILE * f = fopen ( "non_existent" , "r" ) ; if ( f == NULL ) { perror ( "fopen() failed" ) ; } else { fclose...
Read more

Converting A String To Int In Groovy

Answer : Use the toInteger() method to convert a String to an Integer , e.g. int value = "99".toInteger() An alternative, which avoids using a deprecated method (see below) is int value = "66" as Integer If you need to check whether the String can be converted before performing the conversion, use String number = "66" if (number.isInteger()) { int value = number as Integer } Deprecation Update In recent versions of Groovy one of the toInteger() methods has been deprecated. The following is taken from org.codehaus.groovy.runtime.StringGroovyMethods in Groovy 2.4.4 /** * Parse a CharSequence into an Integer * * @param self a CharSequence * @return an Integer * @since 1.8.2 */ public static Integer toInteger(CharSequence self) { return Integer.valueOf(self.toString().trim()); } /** * @deprecated Use the CharSequence version * @see #toInteger(CharSequence) */ @Deprecated public static Integer toInteger(String self) { return toInteg...
Read more