The_New_Guy
New Member
- Joined
- Oct 20, 2021
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello Everyone,
I was wondering if anyone could help with a formula to determine the placement of gradient color line stops on a 2-series line chart in Excel. I've been able to figure it out for single-line charts but adding multiple series has befuddled me.
My team needs to generate 50+ KPI charts for presentations each month and I had originally hoped to script the process in VBA only to find out that gradient formatting of 1-dimensional line objects is something VBA can't handle. Plan B is to create a refreshable file with formulas adjacent to each chart to tell users where to manually place the gradient stops and make it a little less painful vs trial and error for each chart.
The example data shows two series with percentage values both positive and negative. The gradient color scheme should kick in if any line dips below -0.5% and turn fully red at -3%.
Any help would be greatly appreciated.
Thanks,
Aaron
I was wondering if anyone could help with a formula to determine the placement of gradient color line stops on a 2-series line chart in Excel. I've been able to figure it out for single-line charts but adding multiple series has befuddled me.
My team needs to generate 50+ KPI charts for presentations each month and I had originally hoped to script the process in VBA only to find out that gradient formatting of 1-dimensional line objects is something VBA can't handle. Plan B is to create a refreshable file with formulas adjacent to each chart to tell users where to manually place the gradient stops and make it a little less painful vs trial and error for each chart.
The example data shows two series with percentage values both positive and negative. The gradient color scheme should kick in if any line dips below -0.5% and turn fully red at -3%.
Any help would be greatly appreciated.
Thanks,
Aaron
| Chart 1 | January | February | March | April | May | June | July | August | September | October | November | December |
| Team 1 | 6.81% | 14.13% | 4.62% | 5.20% | 5.71% | 2.67% | -2.24% | 4.26% | 9.11% | -13.00% | 16.30% | 6.20% |
| Team 2 | -2.30% | -0.52% | -0.09% | -18.07% | -16.53% | 0.52% | 1.92% | 0.00% | 0.00% | 0.96% | 0.00% | 1.20% |
| Chart 2 | January | February | March | April | May | June | July | August | September | October | November | December |
| Team 1 | 5.48% | -0.79% | -2.87% | 6.63% | -0.87% | -4.24% | 4.28% | 1.40% | -1.35% | 2.61% | -2.89% | 0.37% |
| Team 2 | -2.45% | 0.77% | -1.46% | 0.00% | 0.00% | 0.00% | 0.00% | 1.55% | 2.52% | 0.00% | 0.16% | -1.01% |
| Chart 3 | January | February | March | April | May | June | July | August | September | October | November | December |
| Team 1 | -1.83% | 0.80% | -1.51% | 7.05% | 13.59% | 13.49% | 31.25% | 30.85% | 37.78% | 13.05% | -40.80% | -57.80% |
| Team 2 | 0.00% | 0.00% | 0.49% | 0.00% | 0.32% | -0.01% | 9.07% | -3.28% | -0.23% | -16.15% | 0.00% | -0.10% |
