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

Thomas

Active Member
Joined
May 3, 2002
Messages
366
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:
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,331
Messages
5,571,578
Members
412,407
Latest member
ElmerCC
Top