max() and sort

[John Savage]

Excel on XP: I want the user to enter a sequence of random integers
into cell A1 and have the spreadsheet record their maximum. So in
B1 I placed the formula =max(a1,b1)

Excel alerts to a circular reference, but then resolutely maintains
a constant 0 in cell B1 regardless of the numbers entered in A1. Is
something amiss?

And another question: I generated a column of 10 numbers using =rand()
and used the A-Z sort button on the toolbar to sort them. Although there
was some rearrangement of the numbers, they were still not fully/properly
sorted according to size. Am I expecting too much from this sort tool?
I changed the numbers to integers, and still they will not sort properly.

Very puzzling. Both work okay on the Sphygmic SS on my old laptop.

 

[Guest]

You can force the Circular Reference to iterate to give a result: HELP on
this appears when you get the circular reference warning.

Re sorting: SORT will invoke the RAND function so your range (values) of
random number will change. If you want to generate and then sort, you will
need to Copy=>Paste Special=>Values (either over the data in column A or to a
new column) and then Sort.

HTH

 

[Guest]

The formula you typed will always return zero because of the way XL
xalculates and in any case it is incorrect. You should return the max of 2 or
more numbers in a cell that is outside of the range being evaluated. If you
really want to do as you are doing then select tools - options - calculation
and check the iterations box. Your formula will then return the value of A1
but if that's what you want then why not =A1 in B1.

For the second part of your question then it looks as if you may have mixed
data types i.e. things that look like numbers but are actually text.

Mike

 

[blheems]

And another question: I generated a column of 10 numbers using =rand()

and used the A-Z sort button on the toolbar to sort them. Although there
was some rearrangement of the numbers, they were still not fully/properly
sorted according to size. Am I expecting too much from this sort tool?
I changed the numbers to integers, and still they will not sort properly.

I don't know about the circular reference problem.

I think that the second problem is happening because the rand creates
new values each time the worksheet is updated. The sort forces an
update so..... When you sort, it sorts the values and THEN creates
new values for the rand function.

An easy way to see this is: copy the column and Paste Special By Value
into a new column. Sort the new column and watch the original column
get new values.

Hope that helps,
Brian

 

[John Savage]

I think that the second problem is happening because the rand creates
new values each time the worksheet is updated. The sort forces an
update so..... When you sort, it sorts the values and THEN creates
new values for the rand function.

You're right; thanks. I was misled through having used the freeware
Sphygmic spreadsheet, its sort does not cause recalculation.

 

[V for Vegemite]

Excel on XP: I want the user to enter a sequence of random integers
into cell A1 and have the spreadsheet record their maximum. So in
B1 I placed the formula =max(a1,b1)

Don't include B1 in the MAX() arguments.
If your column of random numbers is A1:A10, the formula should read
B1=MAX(A1:A10)