MarcoMember@marcoJoin Date: 2003Post 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?
MarcoStuart WemyssMember@stuart-wemyssJoin Date: 2003Post Count: 598
Obviously you have to enter the cell range that contains the numbers.
StuSmaointeMember@smaointeJoin Date: 2003Post 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:
Hope that helps!
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.
The topic ‘Excel Spreadsheet formula’ is closed to new replies.