Is there a way to set the axis level on a Google Sheets win loss sparkline to a figure in another ce-CodePudding

I have a google sheet which has 2 tabs, the first is called Data and the second is called Dashboard.

I want a Win Loss sparkline in a cell on the sheet titled Dashboard.

The axis level is set to 0 as a standard on sparklines but I would like this to be set to the figure based in cell U3 on the Data tab.

The sparkline itself should auto update daily to always show the past 30 days. The dates are in B4:B368 on my Data tab and the figures needed for the past 30 days are in U4:U368.

=SPARKLINE((indirect("Data!U"&match(today()-30, Data!B:B, true)): indirect("Data!U"&match(today(), Data!B:B, true))),{"charttype","winloss";"color","blue";"axis",true;"axiscolor","black";"firstcolor","grey";"lastcolor","pink"})

I've come up with the above formula but so far not having any luck. Any help is greatly appreciated.

Here's a sample sheet: https://docs.google.com/spreadsheets/d/1Rd4U9iCQq64afi1yaZgucO98RbN1INDTaaoa0slEIVU/edit?usp=sharing

CodePudding user response：

The available options for SPARKLINE can be found here. Some options like ymin and ymax that defines min and max value along the axis are not applicable for "winloss" charts.

CodePudding user response：

I came up with a fix by adding an additional column and applying this formula in that new column.

=ArrayFormula((U4:U368-U3)*-1)

I then set my win/loss sparkline to this column.

This formula balances out the win/loss by removing the target figure based in cell U3 which then allows me to adjust the sparkline X axis.