I have a chart already made -- happens to be a stacked column chart -- that plots 80 data points (in columns).
Sheet name where the data is: Annual Recap
The charts are on a sheet called charts
Here is what I want:
If the value in B6 (on the Annual Recap Sheet) is < 50, then plot the first 70 values in column K starting with K6 ( i.e., plot K6:k76)
Else, plot the first 40 values in column K starting with K6 (i.e., plot K6:k46)
I tried the following formula in the "Series Values" box on the already made chart (where the range of data to be plotted goes), but I get a "that function isn't valid" error. I use Excel 2016
A poster on this forum suggested this formula, but I have not heard back from them in a while, so I am asking in a different way. -- do not know how to link to the thread, so please do not holler at me.
Can someone tell be what is wrong with the formula? thanks for looking
Sheet name where the data is: Annual Recap
The charts are on a sheet called charts
Here is what I want:
If the value in B6 (on the Annual Recap Sheet) is < 50, then plot the first 70 values in column K starting with K6 ( i.e., plot K6:k76)
Else, plot the first 40 values in column K starting with K6 (i.e., plot K6:k46)
I tried the following formula in the "Series Values" box on the already made chart (where the range of data to be plotted goes), but I get a "that function isn't valid" error. I use Excel 2016
Code:
=OFFSET('Annual Recap'!$K$6,,,IF('Annual Recap'!$B$6<50,70,40))
A poster on this forum suggested this formula, but I have not heard back from them in a while, so I am asking in a different way. -- do not know how to link to the thread, so please do not holler at me.
Can someone tell be what is wrong with the formula? thanks for looking