Formula to find the last number in a series

  • Thread starter Thread starter OX_Gambit
  • Start date Start date
O

OX_Gambit

Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002
 
Maybe this:

=LOOKUP(2,1/(LEFT(A1:A7,4)="1006"),A1:A7)
Also if I search for numbers beginning with 1008
I need to know that there is none present.
Click to expand...

The above formula will return #N/A.
 
Numbers in col J
=SUMPRODUCT((LEFT(TRIM($J$2:$J$22),4)="1006")*1)
if numbers in col L
=SUMPRODUCT((LEFT(TRIM($J$2:$J$22),4)=TEXT(L4,"0000"))*1)
 
Q1: use formula
=INDEX(A1:A10,MAX(IF(INT((A1:A10)/1000)=1006,ROW(A1:A10),0)))
As this is an array formula, commit it with CTRL+SHIFT+ENTER (not just
ENTER)
Change A1:A100 to suit your needs, for not use full column reference like
A:A unless you have Excel 2007

Q2: use array formula
=IF(MAX(IF(INT((A1:A10)/1000)=1008,ROW(A1:A10),0)),"found","not found")

best wishes
 
OX_Gambit said:
Hello,

I have the following list of number and need to find the last number that
starts with 1006? How do I do that? Also if I search for numbers beginning
with 1008 I need to know that there is none present.

1005001
1005002
1006001
1006002
1006003
1007001
1007002
Click to expand...

If, by "last number" you mean the highest number and they may not be sorted
ascending, try this array-entered formula:

=MAX((LEFT(A1:A7,4)="1006")*A1:A7)

This formula will result in 0 for "beginning with 1008" with your data.
 
OX_Gambit said:
What does the 1/(LEFT(A1:A7,4) do to the text?
Click to expand...
....

Unless Transition Formula Evaluation is enabled (it's disabled by
default), Excel converts numbers to text when used in text contexts,
such as when ranges are processed by the LEFT function. LEFT(range,4)
returns an array of the leftmost 4 characters from each cell in range.
In your case that would mean returning the leftmost 4 numerals as a
string. The entire expression actually was

1/(LEFT(A1:A7,4)="1006")

in which the leftmost 4 numerals in each cell in A1:A7 is compared to
"1006", the 4 digits you've said you're seeking. The result is an
array of TRUE (match) or FALSE (don't match) values. Next, Excel
converts TRUE to 1 and FALSE to 0 when they're used in arithmetic
contexts such as dividing them into 1. The entire expression will then
return an array of 1s (1/TRUE = 1/1 = 1) or #DIV/0! (1/FALSE = 1/0 =
#DIV/0!). The rest of the formula uses a quirk of Excel LOOKUP
function in which it will seek through it's entire 2nd argument to
find the first value greater than its 1st argument. It also keeps
track of the last value in its 2nd argument less than its 1st
argument. Since the 2nd argument would contain only 1 and #DIV/0!, it
won't find 2. It skips the #DIV/0! values, and it keeps track of the
last 1 found. When it runs through the 2nd argument array without
finding a 2, it uses the last 1 found as the match. That means it
matches the last cell in A1:A7 that begins with the ordered numerals
1006.
 
Back
Top