I have a series of data in the same row, (F5:AC5) containing anywhere from 1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five numbers
entered in the row of data (not smallest or largest, but simply the last
numbers that were entered in). There could be duplicate numbers, and blanks
in between cells of numbers. How would I go about this?

Thanks.

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott" wrote in message
...
I have a series of data in the same row, (F5:AC5) containing anywhere from
1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five
numbers
entered in the row of data (not smallest or largest, but simply the last
numbers that were entered in). There could be duplicate numbers, and
blanks
in between cells of numbers. How would I go about this?

Thanks.

Will anything other than a macro work?

"Don Guillett" wrote:

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott" wrote in message
...
I have a series of data in the same row, (F5:AC5) containing anywhere from
1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five
numbers
entered in the row of data (not smallest or largest, but simply the last
numbers that were entered in). There could be duplicate numbers, and
blanks
in between cells of numbers. How would I go about this?

Thanks.

What should happen if there aren't 5 numbers?

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
Will anything other than a macro work?

"Don Guillett" wrote:

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott" wrote in message
...
I have a series of data in the same row, (F5:AC5) containing anywhere
from
1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five
numbers
entered in the row of data (not smallest or largest, but simply the
last
numbers that were entered in). There could be duplicate numbers, and
blanks
in between cells of numbers. How would I go about this?

Thanks.

I would want it to return the numbers up to five numbers. So if there were
only three number for instance in the data set, I'd want to return all three.

"T. Valko" wrote:

What should happen if there aren't 5 numbers?

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
Will anything other than a macro work?

"Don Guillett" wrote:

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott" wrote in message
...
I have a series of data in the same row, (F5:AC5) containing anywhere
from
1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five
numbers
entered in the row of data (not smallest or largest, but simply the
last
numbers that were entered in). There could be duplicate numbers, and
blanks
in between cells of numbers. How would I go about this?

Thanks.

Try this array formula** entered in AD5 and copied across to AH5:

=IF(COLUMNS($AD5:AD5)<=COUNT($F5:$AC5),INDEX($F5:$ AC5,LARGE(IF($F5:$AC5<"",COLUMN($F5:$AC5)),COLUMN S($AD5:AD5))-COLUMN($F5)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
I would want it to return the numbers up to five numbers. So if there were
only three number for instance in the data set, I'd want to return all
three.

"T. Valko" wrote:

What should happen if there aren't 5 numbers?

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
Will anything other than a macro work?

"Don Guillett" wrote:

try this idea
Sub lastfivenums()
mr = 5
For i = Cells(mr, Columns.Count).End(xlToLeft).Column To 6 Step -1
If Cells(mr, i) 0 Then
ms = ms + Cells(mr, i)
mc = mc + 1
If mc = 5 Then Exit For
End If
Next i
MsgBox ms
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott" wrote in message
...
I have a series of data in the same row, (F5:AC5) containing anywhere
from
1
to 24 numbers. In cells AD5:AH5, I want to determine the LAST five
numbers
entered in the row of data (not smallest or largest, but simply the
last
numbers that were entered in). There could be duplicate numbers,
and
blanks
in between cells of numbers. How would I go about this?

Thanks.