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

[Thomas]

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:

 

[GlennUK]

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?