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:
 

Excel Facts

Links? Where??
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
Joined
Jul 8, 2002
Messages
11,687
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?
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top