spreadsheet - Google Sheet: Color scale conditional formatting works on column C but not on column B. Help me fix it?

Question

Beginner here who just started building a spreadsheet to track different stocks. I can't seem to get conditional formatting to work on certain columns.

Here's an example: https://docs.google.com/spreadsheets/d/1chW8URH3S4P_AGOfPr7oKmeqWApUWVBTwCgu0XtmuyA/edit#gid=0

Column C works fine, column B doesn't.

How can I get it to work?

Thanks in advance!

question from:https://stackoverflow.com/questions/65876393/google-sheet-color-scale-conditional-formatting-works-on-column-c-but-not-on-co

Answer 1

It isn't working because the values in column B aren't numbers - although they look like numbers, they are actually text. You could try converting them to numbers like this - you need the Iferror because the hyphens will produce an error when it tries to convert them.

=IF(ISBLANK(A2),,iferror(--SUBSTITUTE(INDEX(QUERY(IMPORTHTML("https://www.finviz.com/quote.ashx?t="&A2, "table", 8),"select Col4"),6),"*",""),"-"))