Google Sheets – YNAB Progress Bars – Negative Value Colouring


I have the following in my Google Sheets:

Where H5 is the following:

=iferror(sparkline(F5:G5, {"charttype", "bar"; "color1", if(G5=F5, "72BE7A", "72BE7A"); "color2", if(G5<0, "FF0000", "808080"); "negcolor", "808080"}))

And where F5 = =Iferror(D5/C5) and G5 = =iferror(1-F5)

This is working. However, occasionally, G5 will be greater than F5 (which I have in the code as G5 <0 like so:

Where the red bar is meant to represent overspending. Unfortunately, whilst it is a 50% overspend, the bar is showing red at around 33% of the value.

I have messed around a lot, but I cannot seem to solve it. Any suggestions on where I am going wrong?

Thank you!

>Solution :

Here’s one approach:

when Activity>Assigned the card progress is maxed out at 1 (100%) and Missing becomes -(Activity/Assigned)


F5 : =if(D5>C5,1,IFERROR(D5/C5))

G5 : =if(D5>C5,-D5/C5,1-F5)

Leave a Reply Cancel reply