Largest number starting with "9" in a list

[adambailey]

Hello.

I'm wanting a function to display the largest number starting with 7,8 and 9.

I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.

I need:
One displaying the largest number in the group starting with 7.
One displaying the largest number in the group starting with 8.
And one displaying the largest number in the group starting with 9.

Could I make use of an IF function and the MAX function?

Something like:
M20:
=IF("1st digit = 7", "display the largest of the list")
M21:
=IF("1st digit = 8", "display the largest of the list")
M22:
=IF("1st digit = 9", "display the largest of the list")

I've used =MAX(H5:H75), but this always displays the top "9" number.

I want something so I can show the top 8xxx number, and the top 7xxx number too.

Thanks for your help in advance.

Adam

 

[DonkeyOte]

Adam as previously mentioned this is an array formula and what this is doing is (to all intents and purposes) EXCLUDING any values in the range that do not begin with the requisite number PRIOR to calculating the MAX of that range... try Aladin's suggestion.

 

[Richard Schollar]

Hi

If you have blanks in the range then perhaps:

=MAX(IF(LEFT((0&H5:H75)+0)+0=A1,H5:H75))

where A1 contains the number of interest (eg 9 or 8) and the formula is CSE-entered.

 

[winsonlau]

Hi, I don't know if I'm too late to reply to your post, but here is a simple formula that you can use to get you the exact result you need.

{=MAX(IF(LEFT($A$1:$A$14,1)="7",$A$1:$A$14))} - Remember to use <Ctrl><Shift><Enter> to enter the formula otherwise it will not work.

Then copy and paste the formula to any another cell and only change the "7" to "8" or "9" to find your MAX value, and of course changing your column reference $A$1:$A$14 to whatever column your data is in.

Hope this will help. Regards, - Winson.