Search code examples
google-sheetsgoogle-sheets-formulaprogress-bar

Horizontal sparklines with negative numbers in G. Sheets

I am trying to create a sparkline formula in Google Sheets for a horizontal bar with negative numbers, for a project budget tracker.

The idea is that the horizontal bar is centered in a cell, so it can go right (green color) for positive numbers, and left (red color) for negative numbers, while keeping yellow color for inbetween. Maximum and minimum should be for each cell different, based on the approved budget for that line, so I would prefer a formula which I can drag for the other cells.

The bars should look something like this:

Status bars

In the provided document you can see my attempt for the formula.

Remark: I use European delimiters, so for more complex formulas it can get confusing where to put ";" or "", for the level of knowledge I currently possess.

Thank you in advance for the help!

EDIT: Perhaps a solution in two columns could be possible. With if function, where negative numbers go to the left in the first column, and if the number is positive goes to the right in the second column.

Solution

  • Approach 1

    =map(A2:A;C2:C;lambda(a;c;if(or(a="";c="");;
 sparkline(let(Σ;c/a;hstack(1+min(max(Σ;-1);0);min(0;max(Σ;-1));max(0;Σ)));{"charttype"\"bar";"max"\2;"color1"\"white";"color2"\"#ec417a";"color3"\"#63d297"}))))

    enter image description here

    Approach 2 (2-column approach)

    =map(A2:A11;C2:C11;lambda(a;c;let(Σ;c/a;if(Σ>0;
 hstack(;sparkline(Σ;{"charttype"\"bar";"max"\1;"color1"\"#63d297"}));
 hstack(sparkline(Σ;{"charttype"\"bar";"max"\1;"color1"\"#ec417a";"rtl"\true});)))))

    enter image description here