# Ok... I'm confused here... (nifty charts + conundrum)

#### Thomas

##### Active Member
Ok, here's what I have:

Named Ranges:
IndexSeries : =ROW(Sheet1!\$A\$1:\$A\$96)*RAND()
X_Values : =COS(IndexSeries)
Y_Values : =SIN(IndexSeries)

Plot the Sheet1!X_Values and Sheet1!Y_Values on an X-Y Scatterplot with the points connected & voila! Nifty spirograph-type patterns (hit F9 a few times to update the chart & show a miriad of different, cool patterns).

Now here's the tricky part I don't get:
See the 'RAND()' part of the IndexSeries? I can do something like move that to its own named range such that:
Named Ranges:
IndexSeries : =ROW(Sheet1!\$A\$1:\$A\$96)*RandNumber
RandNumber : =RAND()
X_Values : =COS(IndexSeries)
Y_Values : =SIN(IndexSeries)

... the graph will still look the same, & will update with F9 to other cool patterns.

HOWEVER:
If the =rand() is placed in a cell, for example, and a named range points to that cell value, all I get is a circle, no matter how many times I update w/ a recalculation! I originally wanted to see what value(s) it took to make some of the patterns, but I now I'm just intrigued by the difference in outcomes.

I've also tried making a range (Sheet1!\$B\$1:\$B\$96) filled with =RAND() formulae to no avail. So what's the deal here? How are the calculations different? Seems a nice little trick to possibly pull a few 'WOW's out of :wink:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### GlennUK

##### Well-known Member
When your defined name formula contains the RAND() function, the series is created like an array formula ... so there are multiple calls to the RND() function. Likewise, if you separate the RAND() out into a defined name of it's own, your formula will reference the defined name multiple times ( as in an array formula ), each reference recalculating the contents of the name, hence multiple calls to RAND(). However, if you separate the RAND() into a spreadsheet cell, no matter how many times it's references, the calculation hierarchy in Excel will ensure that the cell is only calculated once.

Just one other thing, your range filled with =RAND() ... shouldn't that be =ROW()*RAND() to simulate what your defined name stuff was doing?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,057
Messages
5,835,161
Members
430,343
Latest member
t0m_c

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back