• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamically assign the range for an X-Y scatter chart

nagovind

Member
Is it possible to assign the chart range dynamically based on certain conditions ?


This query is due to the handling of huge data


For e.g in certain cases there are less no. of XY coordinates that are required to be plotted in XY scatter chart


In certain cases there is a requirement to plot more no. of points in the same chart


If it is dynamically assigned then there will be resonable spped in the calculation. As the range is always linked to 1000000 cells there is a drastic slow down in the excel sheet calculation


Please advise a solution


thanks
 
Yes


You can use Named Formula to define the ranges for the X and the Y series for the chart

These can be as flexible as your requirements


Can you upload a samll sample of data and define the various criteria you want to use to define the ranges?
 
@Hui

Thanks for the reply

I will send it is huge file

The logic is as below


For e.g depending on the user input X-Y coordinate will be generated


For i = 1 to n

X = i

Y = function of i

Next i


n is the user input


Now the coordinate of XY to be plotted in the XY scatter chart


n varies from 10 to 100000


So if i select a XY scatter chart with the range P10:Q100000 then there is a considerable slowdown in the calculation


So the aim is to assign the Maximum range as a variable so that the speed depends on the user input data as the maximum value is used in rare cases but it is not a fixed value to be assigned in the named ranges


thanks
 
Have a look at the sample file below


I have a Named Formula

n: which defines 1 to n

X: =Row(Offset(A1,,,n,1))

Y: =My_Func(x)


Then a User defined Function My_Func() that calculates the Y Value for each X value


Have a look here: https://www.dropbox.com/s/dnjozv6n6y5gnqf/My%20Function.xlsm
 
I did above

Examine the chart series and see where they reference

Then examine the Named Formula

Then checkout the UDF

Then come back with some specific questions
 
You may also want to read about the Evaluate function at

http://chandoo.org/wp/2011/05/16/lost-excel-functions/
 
Nagovind


Absolutely


Just keep adding inputs, named formula and UDF's as appropriate


Refer: https://www.dropbox.com/s/97k04mi9jztzp84/My%20Function2.xlsm


In this sample:

My Function 1 & 4 use a UDF to calculate the Y values

My Function 2 & 2 use Named Formula calculate the Y values

In My Function 3 I have used an intermediate Named Formula t_3 to calculate the Radians of the n_3, this is then available to calculate the X & Y values for a circle.

It doesn't look like a circle as the axis aren't the same scale, but it is


When you download the file,you may get an error that some formula contain invalid references, Accept that

then Enable Content

Then press F9 to recalculate
 
Back
Top