| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "XL3SparkLine"
(Added link to the monochrome version) |
(Added new banded fill parameters) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
Creates a line, point or area chart that is rendered in an Excel cell. | Creates a line, point or area chart that is rendered in an Excel cell. | ||
| + | |||
| + | Double-clicking the chart, or selecting the {{Menu|XLCubed|Format In-Cell Chart...}} right-click menu item allow the editing of the chart format using the [[SparkLine Chart Designer]]. | ||
==Syntax== | ==Syntax== | ||
| − | {{Code|XL3SparkLine( Data, Output, InRows, [LineType], [LineColour], [PointColour], [NegativePointColour], [PeriodLength], [PeriodColour], [StartColour], [EndColour], [MinColour], [MaxColour], [ReferenceValues], [ReferenceColour], [Minimum], [Maximum], [UseCommonScale], [NormalLowerBounds], [NormalUpperBounds], [NormalBandColour], [AreaColour], [ScaleFactor], [MissingValueHandling], [Label] )}} | + | {{Code|XL3SparkLine( Data, Output, InRows, [LineType], [LineColour], [PointColour], [NegativePointColour], [PeriodLength], [PeriodColour], [StartColour], [EndColour], [MinColour], [MaxColour], [ReferenceValues], [ReferenceColour], [Minimum], [Maximum], [UseCommonScale], [NormalLowerBounds], [NormalUpperBounds], [NormalBandColour], [AreaColour], [ScaleFactor], [MissingValueHandling], [Label], [Band Data], [Lower Band Colour], [Mid Band Colour], [Upper Band Colour], [Median Band Colour], [Percentiles] )}} |
==Parameters== | ==Parameters== | ||
| Line 102: | Line 104: | ||
| {{Code|Label}} | | {{Code|Label}} | ||
| Specifies the text to use in the formula cell | | Specifies the text to use in the formula cell | ||
| + | |- | ||
| + | | {{Code|Band Data}} | ||
| + | | A range to be used to plot the banded fill. This should either be empty (calculated quartiles will be used), a single row (the same values will be used for each series) or one row per chart (each series will have its own set of values). Each row should have values for: | ||
| + | * lower band lower bound | ||
| + | * lower band upper bound / mid band lower bound | ||
| + | * median value | ||
| + | * mid band upper bound / upper band lower bound | ||
| + | * upper band upper bound | ||
| + | |- | ||
| + | | {{Code|Lower Band Colour}} | ||
| + | | The colour to use for the lower band | ||
| + | |- | ||
| + | | {{Code|Mid Band Colour}} | ||
| + | | The colour to use for the mid band | ||
| + | |- | ||
| + | | {{Code|Upper Band Colour}} | ||
| + | | The colour to use for the upper band | ||
| + | |- | ||
| + | | {{Code|Median Band Colour}} | ||
| + | | The colour to use for the median line | ||
| + | |- | ||
| + | | {{Code|Percentiles}} | ||
| + | | When using calculated bands, this range specifies what percentiles should be used to plot the banded fill. This should either be left empty (quartiles will be used), a single row (the same percentiles will be used for each series) or one row per chart (each series will have its own set of percentiles). Each row should have values for: | ||
| + | * lower band lower bound percentile | ||
| + | * lower band upper bound / mid band lower bound percentile | ||
| + | * 'median' percentile | ||
| + | * mid band upper bound / upper band lower bound percentile | ||
| + | * upper band upper bound percentile | ||
|} | |} | ||
| Line 107: | Line 137: | ||
* [[Formula Reference]] | * [[Formula Reference]] | ||
* [[XL3SparkLineM|XL3SparkLineM Formula]] | * [[XL3SparkLineM|XL3SparkLineM Formula]] | ||
| + | * [[SparkLine Chart Designer]] | ||
| + | * [[Bandline Chart Designer]] | ||
[[Category:Formulae]] | [[Category:Formulae]] | ||
[[Category:In-Cell Chart Formulae]] | [[Category:In-Cell Chart Formulae]] | ||
[[Category:In-Cell Charts]] | [[Category:In-Cell Charts]] | ||
Latest revision as of 16:36, 5 June 2015
Creates a line, point or area chart that is rendered in an Excel cell.
Double-clicking the chart, or selecting the XLCubed > Format In-Cell Chart... right-click menu item allow the editing of the chart format using the SparkLine Chart Designer.
Syntax
XL3SparkLine( Data, Output, InRows, [LineType], [LineColour], [PointColour], [NegativePointColour], [PeriodLength], [PeriodColour], [StartColour], [EndColour], [MinColour], [MaxColour], [ReferenceValues], [ReferenceColour], [Minimum], [Maximum], [UseCommonScale], [NormalLowerBounds], [NormalUpperBounds], [NormalBandColour], [AreaColour], [ScaleFactor], [MissingValueHandling], [Label], [Band Data], [Lower Band Colour], [Mid Band Colour], [Upper Band Colour], [Median Band Colour], [Percentiles] )
Parameters
| Parameter | Description | ||||||
|---|---|---|---|---|---|---|---|
| Data | The data to chart. This can be a range or a formula | ||||||
| Output | The target range in which to render the chart(s) | ||||||
| InRows | Specifies whether the input data are in rows or columns:
| ||||||
| LineType | Specifies how to plot the data:
| ||||||
| LineColour | The colour to use for the line | ||||||
| PointColour | The default colour to use for the data points | ||||||
| NegativePointColour | The default colour to use for negative data points | ||||||
| PeriodLength | The length of the period for alternate background shading | ||||||
| PeriodColour | The colour of the alternate background shading | ||||||
| StartColour | The colour to highlight the first point, or -1 or an empty parameter for none | ||||||
| EndColour | The colour to highlight the last point, or -1 or an empty parameter for none | ||||||
| MinColour | The colour to highlight the lowest point, or -1 or an empty parameter for none | ||||||
| MaxColour | The colour to highlight the highest point, or -1 or an empty parameter for none | ||||||
| ReferenceValues | A value or range to use for a reference line. To use the mean value instead, use "Average" | ||||||
| ReferenceColour | The colour to use for the reference line, or -1 or an empty parameter for none | ||||||
| Minimum | A value to use for the minimum vertical axis value, or an empty parameter to use an automatic value | ||||||
| Maximum | A value to use for the maximum vertical axis value, or an empty parameter to use an automatic value | ||||||
| UseCommonScale | Use 1 here to force all charts to use a common scale | ||||||
| NormalLowerBounds | A value or range to use for the lower bounds of the normal bands | ||||||
| NormalUpperBounds | A value or range to use for the upper bounds of the normal bands | ||||||
| NormalBandColour | The colour to use for the normal band, or -1 or an empty parameter for none | ||||||
| AreaColour | The colour to fill the chart area under the line, or -1 or an empty parameter for none | ||||||
| ScaleFactor | The horizontal scale factor to use when drawing the chart | ||||||
| MissingValueHandling | Specifies how missing values should be rendered:
| ||||||
| Label | Specifies the text to use in the formula cell | ||||||
| Band Data | A range to be used to plot the banded fill. This should either be empty (calculated quartiles will be used), a single row (the same values will be used for each series) or one row per chart (each series will have its own set of values). Each row should have values for:
| ||||||
| Lower Band Colour | The colour to use for the lower band | ||||||
| Mid Band Colour | The colour to use for the mid band | ||||||
| Upper Band Colour | The colour to use for the upper band | ||||||
| Median Band Colour | The colour to use for the median line | ||||||
| Percentiles | When using calculated bands, this range specifies what percentiles should be used to plot the banded fill. This should either be left empty (quartiles will be used), a single row (the same percentiles will be used for each series) or one row per chart (each series will have its own set of percentiles). Each row should have values for:
|
