how is your data structured ? thanks
Kaps
This is a discussion on Largest number starting with "9" in a list within the Excel Questions forums, part of the Question Forums category; Hello. I'm wanting a function to display the largest number starting with 7,8 and 9. I have a list of ...
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
how is your data structured ? thanks
Kaps
Read my Excel blog on
http://simplyspreadsheets.wordpress.com/
For more ways I can help you with Excel :-
www.simplyspreadsheets.co.uk
Hi,
=MAX(IF(LEFT(H5:H75)+0=9,H5:H75))
Confirmed with CTRL+SHIFT+ENTER
HTH
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?
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.
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.
Does my a$$ look big in this picture ?
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
= A B C D E F G 1 Numbers Ist digit 7 Ist digit 8 Ist digit 9 2 920 FALSE FALSE TRUE 780 888 980 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
Like this thread? Share it with others