Largest number starting with "9" in a list

adambailey

New Member
Joined
Sep 23, 2008
Messages
7
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi.

Thanks for your response, but this just gives me #VALUE.

I am confirming it as a CSE forumula.

Some of the cells in the column do not have values in them - could this be the problem?
 
Upvote 0
I've got it working with the numbers starting with 9, using:
=IF((LEFT(MAX(H5:H75))+0=9),MAX(H5:H75))

Which is just a long winded =MAX(H5:H75).

How can I get this to work using numbers starting with 7 and 8?

If I the same formula, just altering the "=9" part, the if statement is always false, because a number beginning with 7 or 8 will never satisfy the first "MAX" statement.

Thanks for your help.
 
Upvote 0
Are you entering as an array as detailed ?

The array is maxing only those numbers where left most character = required number... if you don't set the array correctly it won't work.

To set array enter formula using SHIFT + CTRL + ENTER
Once set array will appear encased within {}

Whenever you edit an array formula you must remember to reset it using the above key strokes.
 
Upvote 0
Some of the cells in the column do not have values in them - could this be the problem?

In short, (I think) yes.

Perhaps if they are always 4 digit numbers, then Ctrl+Shift+Enter

=MAX(IF(INT(H5:H75/1000)=9,H5:H75))

which can be adapted for 8 and 7.
 
Upvote 0
Hi,

Another way..
Book1
ABCDEFG
1NumbersIst digit 7Ist digit 8Ist digit 9
2920FALSEFALSETRUE780888980
3852
4880
5922
6831
7742
8964
9821
10885
11839
12
13780
14703
15980
16852
17762
18888
19818
20953
Sheet1


Formula in B2:

=LEFT(A2)="7"

In E2:

=DMAX($A$1:$A$20,1,B1:B2)

HTH
 
Upvote 0
Hi.

Thanks for your response, but this just gives me #VALUE.

I am confirming it as a CSE forumula.

Some of the cells in the column do not have values in them - could this be the problem?

Change

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

to

=MAX(IF(ISNUMBER($H$5:$H$75),IF(LEFT($H$5:$H$75)+0=J20,$H$5:$H$75)))

where J20 houses 7, J21 8, and J22 9.

The formula needs still to be confirmed with control+shift+enter.
 
Upvote 0
In short, (I think) yes.

Perhaps if they are always 4 digit numbers, then Ctrl+Shift+Enter

=MAX(IF(INT(H5:H75/1000)=9,H5:H75))

which can be adapted for 8 and 7.

That can't be adopted fro 7 or 8, that's the problem with all the suggestions so far.

It will work for 9xxx numbers, but nothing else.

The reason is that a 7xxx or 8xxx number will never fullfill the "MAX" requirement of the function.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top