Excel Spreadsheet formula

Viewing 4 posts - 1 through 4 (of 4 total)

Marco
Member
@marco
Join Date: 2003
Post Count: 66
October 4, 2003 - 9:12 am
Hi All, hope you are enjoying a great weekend.
Hoping somebody might be able to point me in the right direction. I need a formula that will return a value showing the highest number and lowest number in a data range, ie for data range 2 4 6 8 the formula would return 8 & 2.
Does anybody know of something that would achieve this result?
Happy investing!
Marco
Stuart Wemyss
Member
@stuart-wemyss
Join Date: 2003
Post Count: 598
October 4, 2003 - 9:55 am
=MIN(A1:A6)
=MAX(A1:A6)
Obviously you have to enter the cell range that contains the numbers.
Cheers
Stu
Smaointe
Member
@smaointe
Join Date: 2003
Post Count: 18
October 4, 2003 - 10:01 am
Hi Marco, I hope this is what you want.
The formula for the largest number in a range is =LARGE(A:A,1) but you would replace the A:A with the appropriate range, eg F2:F14. The “1” denotes 1st largest number in the range.
The formula for lowest is =LARGE(A:A,n) where “n” is the number of numbers in the range; ie if you had 10 numbers in the range, the formula would be =LARGE(A:A,10) and the 10 refers to the 10th largest number.
I’ve got a screenshot that might help explain it:
http://www4.tpgi.com.au/users/emmamail/temp/excel.jpg
Hope that helps!
-Emma
Edit: oops, too slow! My version seems a little more complicated than MIN/MAX, but you can also use LARGE to pick out the 2nd, 5th, 23rd, 16th highest numbers etc.
Marco
Member
@marco
Join Date: 2003
Post Count: 66
October 5, 2003 - 1:32 pm
Thanks Stuart and Smaointe for your help. I shall try both methods now.
Have a great day!
Marco
Pick the excel novice!! ME