Largest number starting with "9" in a list

Thanks:  0
Likes:  0

# Thread: Largest number starting with "9" in a list

1. ## Largest number starting with "9" in a list

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.

2. ## Re: Largest number starting with "9" in a list

how is your data structured ? thanks

Kaps

3. ## Re: Largest number starting with "9" in a list

Hi,

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

Confirmed with CTRL+SHIFT+ENTER

HTH

4. ## Re: Largest number starting with "9" in a list

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?

5. ## Re: Largest number starting with "9" in a list

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.

6. ## Re: Largest number starting with "9" in a list

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.

7. ## Re: Largest number starting with "9" in a list

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.

8. ## Re: Largest number starting with "9" in a list

Hi,

Another way..

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B2C2D2E2F2G2 =

A
B
C
D
E
F
G
1
Numbers Ist digit 7Ist digit 8Ist digit 9
2
920FALSEFALSETRUE780888980
3
852
4
880
5
922
6
831
7
742
8
964
9
821
10
885
11
839
12
13
780
14
703
15
980
16
852
17
762
18
888
19
818
20
953
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Formula in B2:

=LEFT(A2)="7"

In E2:

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

HTH

9. ## Re: Largest number starting with "9" in a list

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.

10. ## Re: Largest number starting with "9" in a list

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•