Ask Your Question

can a function asign a color to a result

asked 2020-06-28 07:30:17 +0200

curnowdj gravatar image

updated 2020-06-28 07:33:08 +0200

I have a IFS function and would like to asign different colors for each result. Is this possible?

edit retag flag offensive close merge delete


=IFS(C5 + D5 <160,"Low",C5+D5 >220,"Hi",1=1,"OK")

curnowdj gravatar imagecurnowdj ( 2020-06-28 07:31:54 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-06-28 07:58:57 +0200

keme gravatar image

Conditional formatting is the most common way to accomplish this, but you can also use the STYLE() function.

edit flag offensive delete link more



... and also there are conditions in number format codes. It might be useful, simplest and fastest for up to three used colors.

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-28 22:31:22 +0200 )edit

I have tried the conditional formatting but as soon as I add square brackets I get a 507 error =IFS(C2+D2 <160;[blue] "Low";C2+D2 >220;[red] "High",1=1;[green] "OK") As far as I can tell there is not a missing bracket. Is the missing #.0 signifficant in this function? I should add I am using Ver on Ubuntu 20.04

curnowdj gravatar imagecurnowdj ( 2020-06-29 03:22:26 +0200 )edit

Number format codes are not used in formulas - they are used in cell formatting (e.g., right-click a cell and choose Format Cell then see Numbers tab; or better use cell styles).

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-29 07:03:58 +0200 )edit

I have tried the conditional formatting but as soon as I add square brackets I get a 507 error

=IFS(C2+D2 <160;[blue] "Low";C2+D2 >220;[red] "High",1=1;[green] "OK")

The number format codes @Mike Kaganski referred to do not go inside the formula, but rather in the cell formatting dialog. See menu item Format - Cells, the Numbers tab. Conditions also have to be inside square brackets.

Once you get the hang of it, using format codes can be faster than either full conditional formatting or using the STYLE() function, because you don't need to set up a style first.

The only solution I know of that goes inside the formula is with STYLE(). This does not use the predefined format codes, but cell styles

keme gravatar imagekeme ( 2020-06-29 07:06:20 +0200 )edit

For the formula attempt mentioned above, if you want to go the format codes path, the proper formula would be =C2+D2, and the number format code for that cell would be [BLUE][<160]"Low";[RED][>220]"High";[GREEN]"OK". That would just change display in the cell, not the cell content: you won't be able to refer to the cell in other formulas as "if the cell contains OK", but rather "if the cell is between 160 and 220".

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-29 07:22:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-06-28 07:30:17 +0200

Seen: 27 times

Last updated: Jun 28