All Topics / Hotch Potch / Excel Spreadsheet formula

Viewing 4 posts - 1 through 4 (of 4 total)
• Marco
Member
@marco
Join Date: 2003
Post Count: 66

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

=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

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

Thanks Stuart and Smaointe for your help. I shall try both methods now.

Have a great day!

Marco

Pick the excel novice!! ME

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

The topic ‘Excel Spreadsheet formula’ is closed to new replies.