Use nested IF statements to change sparkline colors

Question

I'm trying to color-code my sparklines based on percentage cutoffs.

I am able to do two colors but can't wrap my head around multiple ifs.

B1 has points possible of 3

1. B2 has points scored the first time
2. B3 has points scored the second time

3. B4 has a two-column sparkline.

   • B1 can vary from 2 to 8.

   • Would like to color code the sparklines where each column has its own colors based on the following percentage conditions of Cell B1

     1. >0% but <50% to be (RED)
     2. >=50% but <75% to be (ORANGE)
     3. >=75% but <100% to be (GREEN)
     4. >100% to be (BLUE)

I used the following formula to create two possible ones where the high color is blue or green.

=if(B3>B1,
 SPARKLINE(B2:B3,{"charttype","column";"color","red";"highcolor","blue";"ymin",0}),
 SPARKLINE(B2:B3,{"charttype","column";"color","red";"highcolor","green";"ymin",0}))

https://docs.google.com/spreadsheets/d/1cvzznbrsR0GdqEr6J52_aWOSFDAf0zYn9MxglzKRyfA/edit?usp=sharing

Answer 1

nesting of IF statements is done like this:

=IF(B3 > B1, SPARKLINE(B2:B3,
             {"charttype", "column"; "color", "red"; "highcolor", "blue";  "ymin", 0}), 
 IF(B3 < B1, SPARKLINE(B2:B3,
             {"charttype", "column"; "color", "red"; "highcolor", "green"; "ymin", 0}), 
 IF(B3 = B1, SPARKLINE(B2:B3,
             {"charttype", "column"; "color", "red"; "highcolor", "pink";  "ymin", 0}), )))

Answer 2

Tested OK in my GANTT sheet.. My ref cell is F24

If F24 <=30 "red"
If F24 <=95 "orange"
Else "Green"
So if F24 30="Red"  31<=>95"Orange" >95="Green"
=SE(F24<=30;SPARKLINE(F24;{"charttype"\"bar";"color1"\ "red";"max"\100});SE(F24<=95;SPARKLINE(F24;{"charttype"\"bar";"color1"\ "orange";"max"\100});SPARKLINE(F24;{"charttype"\"bar";"color1"\ "green";"max"\100})))