Largest number starting with "9" in a list

A

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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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
adambailey said:
Some of the cells in the column do not have values in them - could this be the problem?
Click to expand...

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
adambailey said:
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?
Click to expand...

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
AdamL said:
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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

K
  • Question Question
Return a user specified number of cell values to a comma list randomly
Replies
1
Views
324
Eric W
Eric W
V
  • Solved
Creating a new filtered list from a dynamically created list based on previous numbers
Replies
6
Views
127
DanteAmor
DanteAmor
PETRODATAjames
  • Question Question
Random 6-digit or 9-digit numbers that do not repeat and do not start with 0 (zero)
Replies
2
Views
886
Legacy 143009
L
N
  • Question Question
What should I do in order to solve an inconsistent reference error when using INDEX/MATCH?
Replies
1
Views
354
numbernoodle
N
E
  • Question Question
FILTER formula based on field with numbers and text formats
Replies
1
Views
435
keda duck
K

Forum statistics

Threads
1,223,832
Messages
6,174,905
Members
452,590
Latest member
CraiginColorado

Share this page

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