CODEWITHSUNDEEP
google-sheets
JSM
JSM

Reputation: 13

How can I change my SPARKLINE function depending on the values in the progress chart?

I was hoping to be able to change the SPARKLINE colour that would go in Cell D2, depending on the values on the right-hand side.

I would also want the bar to show a relative position depending on the personal best in the range.

So for example, the person best for Scn1 is 925. This is past the Gold rank, but now quite at the diamond, therefore I would want the bar to display a gold colour. If the score was between another bracket, I would want it to display the colour of the rank that has been surpassed.

ON top of this, I would love for the bar to show a relative position depending on the person's score in a bracket. For example, if the personal best was 925, that would make the bar a Gold colour AND have the Bar cover 50% of the cell (because 925 is halfway between 900 and 950(Gold and Diamond ranks)).

Basically, the position of the bar would reset back to the left side relative to the percentage the personal best was through a rank.

Would this be possible?

cheers!

img_cell

Upvotes: 1

Views: 704

Answers (1)

General Grievance
General Grievance

Reputation: 4988

This should do it:

=SPARKLINE(
IF(C2,
    IF(C2>=I2,
        I2,
        IFERROR(
            1/(1/(C2-INDEX(
                ({0,E2:I2}),
                MATCH(
                    C2,
                    ({0,E2:I2}),
                    1
                )
                )
            )),
            INDEX(({0,E2:I2}),MATCH(C2,({0,E2:I2}),1)+1)-
                INDEX(({0,E2:I2}),MATCH(C2,({0,E2:I2}),1))
        )
    ),
    0
),
{
    "charttype","bar";
    "max",IF(
        C2>=I2,
        I2,
        INDEX(({0,E2:I2}),MATCH(C2,({0,E2:I2}),1)+1)-
            INDEX(({0,E2:I2}),MATCH(C2,({0,E2:I2}),1))
    );
    "color1",IF(
        C2>=I2,
        "Green",
        IF(C2<E2,"black",
        INDEX(
            {"Brown","Gray","Gold","Cyan","Green"},
            IFNA(
                MATCH(C2,E2:I2,0),
                MATCH(C2,({0,E2:I2}),1)-1
            )
        )
        )
    )
})

You'll have to edit the color scheme yourself, unless you want to have another range to define the colors you want to use.

Behavior:

  • On boundaries, it displays 100% of the rank attained in that color.
  • When over max value, it saturates at 100%, and displays a saturation color.
  • When below min value, it displays black.
  • Error at <0.

Upvotes: 1

Related Questions