The first have of "Biff"'s seems to work as it is needed.... the second part
still needs some work....

I need the min of the last 28 cells that have entries. That means that
there are empty cells throughout the 'Helper column'. In my test page, I
have entries in cells J1:J75. The Min formula that was given just gives me
an error response of #VALUE!. We are close....

"Biff" wrote:

Hi!

This works but you need a helper column.

If your table of numbers is in the range A1:E15:

Enter this formula in H1 and copy down to H75:

=IF(OFFSET(A$1,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-1,5))
=0,"",OFFSET(A$1,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-
1,5)))

Now, to find the MIN of the last 28 values:

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MIN(H75:INDEX(H1:H75,LARGE(IF(H1:H75<"",ROW(1:75 )),28)))

NB: The normal direction of calculation is left to right
then down. Since this is sort of working in reverse, the
direction is now right to left then up.

Biff

-----Original Message-----
No, I still want the lowest number of the last 28 cells
with numbers in them.


"Myrna Larson" wrote:

Perhaps =LARGE(A1:E15,28) will do what you want. It
will return the 28th
largest value in the range.


On Thu, 31 Mar 2005 17:03:02 -0800, "sac73"

wrote:

We are on the right track with the offset suggestion,
now here is a bit
more....

Let me know if I am asking for too much from the
program:

The items that I want are in multiple rows AND
multipule columns. Can we
set it to count backwards along a row, then move up
and continue counting,
ignoring cells with no entries, until it has checked
28?

eg. There are 50 items in A1:E15, meaning there are 15
random cells with no
entries.

"Myrna Larson" wrote:

By last, are you referring to their location on the
worksheet, i.e. if the
data is in A1:A40, you want to see the lowest in the
range A13:A40?

If that's it, then if you want to see the minimum in
A41, in that cell

=MIN(OFFSET(A40,-27,0,28,1))



On Thu, 31 Mar 2005 06:11:03 -0800, "sac73"

wrote:

Active is defined as a numeric entry... Let me
also better define:
40 cells - 35 active (numbers in them) - I want to
count the last 28, and
then get the lowest number (Min)

"Bob Phillips" wrote:

What defines active?:

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"Sac73" wrote
in message
news:207089F2-80B4-46C8-863D-
...
Is there a way to count back a certain number
of active cells only
(eg.
40
possible cells, only 28 are active), and then
enter the lowest number
within
that range into a cell?







.