CODEWITHSUNDEEP
google-sheets
Gregor Carswell
Gregor Carswell

Reputation: 51

Google Sheet Sparkline Progress bar

I'm building a Delivery Schedule which pulls data from another sheet.

The Delivery schedule should have a quick colour coded points of reference. To do this I'm looking at having a Sparkline as a progress bar.

I'd like the sparkline (In column D) to start Red, The target volume is in Column B, Assigned Volumes are in Column F & as the volume in Column F gets closer to the target volume in Column B, the sparkline changes colour to Green. Once the target volume has been achieved, The status in Column E will be changed to "Closed" and the sparkline will then change to Grey.

I've discovered I'm useless at finding the formula to achieve this.

Image below is an example of what i'm working with enter image description here

Upvotes: 0

Views: 1599

Answers (1)

Rocky
Rocky

Reputation: 990

Try the below formula in cell C8:

=ifs(D8="Closed",Sparkline(E8,{"charttype","bar";"color1","gray";"Max",B8}),(E8/B8)<=0.8,Sparkline(E8,{"charttype","bar";"color1","red";"Max",B8}),(E8/B8)>0.8,Sparkline(E8,{"charttype","bar";"color1","green";"Max",B8}))

Closer value kept at 80% change the value in the formula as per your need

And for the status column E try below:

   =if((E8/B8)>=1,"Closed","Open")

Closed status value kept at 100% change the value in the formula as per your need

enter image description here

Upvotes: 1

Related Questions