MAX MIN Surprise Excel 2007 3

[Clyde38]

At least a surprise to me. . .

While trying to determine the maximum and the minimum for a column of numbers of almost 3000 points, I discovered that the =MAX() and =MIN() statements have a limit of 255. I never paid attention to the note in the description of the function.

To get the Max and Min for the column see attached spread sheet (MAX MIN.xlsx)

[COLOR=red b]If the cells are in a contiguous row or column[/color b]

1. Select a cell below or to the right of the numbers for which you want to find the smallest number.
2. On the Home tab, in the Editing group, click the arrow next to AutoSum, click Min (calculates the smallest) or Max (calculates the largest), and then press ENTER.


[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[IRstuff]

The limit applies to the number of separate arguments passed to the function, so if your numbers are contiguous, that only counts as a single argument.

Note that this is an improvement over Excel2000, which only allowed 30 arguments for the same function.

Unless they changed some other logic, your example is not correctly inputted as a RANGE, i.e., A1:A2942. Note that the maximum of your column is actually 0.093551833. The Number2 field is for a non-contiguous data input, not the end of the contiguous range.


TTFN

FAQ731-376

 

[Clyde38]

Thanks IRstuff. I see that I have used a commma (,) instead of a colon ) so I was not describing a RANGE.

A single conversation with a wise man is better than ten years of study. ~Chinese Proverb

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[IRstuff]

FYI for anyone else interested, the number of parameters allowed for MAX was changed in either 2003 or 2007, i.e., Excel2002 still has only 30 arguments allowed.

TTFN

FAQ731-376

 

[electricpete]

Thanks to both of you. I like the threads where people identify unexpected behavior. Everyone benefits.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.