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?

What defines active?:

--

HTH

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


"Sac73" wrote in message
...
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?

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
...
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?

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
...
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?

Hi!

Where are these "active cells"?

In a row? A1:IV1

In a column? A1:A65536

Just FYI, in Excel, active cell is a standard term that
means the cell that is currently selected. When you move
the cursor and select cell A1, cell A1 becomes the active
cell.

Biff

-----Original Message-----
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?



.

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
...
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?

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
...
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?

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
...
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?

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?







.

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?







.

Hi!

That means that there are empty cells throughout
the 'Helper column'.

Yes, those empty cells are intentional and the MIN formula
I suggested will ignore them.

Did you enter the MIN formula as an array?

Type in the MIN formula and INSTEAD of hitting ENTER you
must use the key combination of CTRL,SHIFT,ENTER.

When done properly Excel will place squiggly braces { }
around the formula. You can not type these braces in
manually.

Biff

-----Original Message-----
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?







.


.

How are you defining the "last" entries? Your range is rectangular. Are the
numbers being entered from left to right, then down to the next row, or from
top to bottom, then right to the next column?


On Fri, 1 Apr 2005 18:41:02 -0800, "sac73"
wrote:

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?







.

The range is rectangular, and from left to right. The 5th cell (eg E:1) is
almost always empty, but needs to be in the equation. The other cells are
usually full, but not always. That is why I need an equation that will work
backwards, (right to left, then up), only factoring in the cells that have a
numeric entry in it, until it has checked 28. At which point it will give
the lowest number in the group. I should add, that there are (and will be)
duplicate entries within the 28 cells being checked. I don't know if that
will make a difference in the equation.

To help (I think), here are the cells that are within the form...

H4:K15 then skip 2 rows and continue with H18:K57, with the first
equation starting in cell P18.


"Myrna Larson" wrote:

How are you defining the "last" entries? Your range is rectangular. Are the
numbers being entered from left to right, then down to the next row, or from
top to bottom, then right to the next column?


On Fri, 1 Apr 2005 18:41:02 -0800, "sac73"
wrote:

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?







.

Hi!

This is not a very difficult thing to accomplish. The
suggestion I gave you works.

If you want to send me a copy of your file I'll do it for
you.

In this latest explanation you say you want to go back 28
cells. Now, is that 28 cells total OR the last 28 cells
with numbers in them? As I understood your earlier posts,
you want the LAST 28 CELLS WITH NUMBERS IN THEM.

Either way, it's not difficult.

Biff

-----Original Message-----
The range is rectangular, and from left to right. The
5th cell (eg E:1) is
almost always empty, but needs to be in the equation.
The other cells are
usually full, but not always. That is why I need an
equation that will work
backwards, (right to left, then up), only factoring in
the cells that have a
numeric entry in it, until it has checked 28. At which
point it will give
the lowest number in the group. I should add, that there
are (and will be)
duplicate entries within the 28 cells being checked. I
don't know if that
will make a difference in the equation.

To help (I think), here are the cells that are within the
form...

H4:K15 then skip 2 rows and continue with H18:K57,
with the first
equation starting in cell P18.


"Myrna Larson" wrote:

How are you defining the "last" entries? Your range is
rectangular. Are the
numbers being entered from left to right, then down to
the next row, or from
top to bottom, then right to the next column?


On Fri, 1 Apr 2005 18:41:02 -0800, "sac73"

wrote:

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?







.




.

It is the last 28 cells, and I have entered the suggestions you made, however
as with most things, another issue has cropped up....

It didn't dawn on me, but the cells that are 'empty'.... aren't. They have
a 4-part IF statement in them, so I think the equation is counting them.

I don't really want to dump my project onto you, because this is just the
start. There are a few other things that I would like to make 'idiot-proof',
so guys like me can't screw it up, but to also make the entry of the info
that much quicker.

"Biff" wrote:

Hi!

This is not a very difficult thing to accomplish. The
suggestion I gave you works.

If you want to send me a copy of your file I'll do it for
you.

In this latest explanation you say you want to go back 28
cells. Now, is that 28 cells total OR the last 28 cells
with numbers in them? As I understood your earlier posts,
you want the LAST 28 CELLS WITH NUMBERS IN THEM.

Either way, it's not difficult.

Biff

-----Original Message-----
The range is rectangular, and from left to right. The
5th cell (eg E:1) is
almost always empty, but needs to be in the equation.
The other cells are
usually full, but not always. That is why I need an
equation that will work
backwards, (right to left, then up), only factoring in
the cells that have a
numeric entry in it, until it has checked 28. At which
point it will give
the lowest number in the group. I should add, that there
are (and will be)
duplicate entries within the 28 cells being checked. I
don't know if that
will make a difference in the equation.

To help (I think), here are the cells that are within the
form...

H4:K15 then skip 2 rows and continue with H18:K57,
with the first
equation starting in cell P18.


"Myrna Larson" wrote:

How are you defining the "last" entries? Your range is
rectangular. Are the
numbers being entered from left to right, then down to
the next row, or from
top to bottom, then right to the next column?


On Fri, 1 Apr 2005 18:41:02 -0800, "sac73"

wrote:

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?







.




.

If you want to send me a copy of your file I'll do it for
you.

On second thought, I withdraw that offer.

You ignored my first reply and my most recent reply.

You figure it out!

Biff

-----Original Message-----
Hi!

This is not a very difficult thing to accomplish. The
suggestion I gave you works.

If you want to send me a copy of your file I'll do it for
you.

In this latest explanation you say you want to go back 28
cells. Now, is that 28 cells total OR the last 28 cells
with numbers in them? As I understood your earlier posts,
you want the LAST 28 CELLS WITH NUMBERS IN THEM.

Either way, it's not difficult.

Biff

-----Original Message-----
The range is rectangular, and from left to right. The
5th cell (eg E:1) is
almost always empty, but needs to be in the equation.
The other cells are
usually full, but not always. That is why I need an
equation that will work
backwards, (right to left, then up), only factoring in
the cells that have a
numeric entry in it, until it has checked 28. At which
point it will give
the lowest number in the group. I should add, that
there
are (and will be)
duplicate entries within the 28 cells being checked. I
don't know if that
will make a difference in the equation.

To help (I think), here are the cells that are within
the
form...

H4:K15 then skip 2 rows and continue with H18:K57,
with the first
equation starting in cell P18.


"Myrna Larson" wrote:

How are you defining the "last" entries? Your range is
rectangular. Are the
numbers being entered from left to right, then down to
the next row, or from
top to bottom, then right to the next column?


On Fri, 1 Apr 2005 18:41:02 -0800, "sac73"

wrote:

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?







.




.

.

Did you not get my last reply? I have included it in this reply. No intent
was meant to upset you.


It is the last 28 cells, and I have entered the suggestions you made, however
as with most things, another issue has cropped up....

It didn't dawn on me, but the cells that are 'empty'.... aren't. They have
a 4-part IF statement in them, so I think the equation is counting them.

I don't really want to dump my project onto you, because this is just the
start. There are a few other things that I would like to make 'idiot-proof',
so guys like me can't screw it up, but to also make the entry of the info
that much quicker.


"Biff" wrote:

If you want to send me a copy of your file I'll do it for
you.

On second thought, I withdraw that offer.

You ignored my first reply and my most recent reply.

You figure it out!

Biff

-----Original Message-----
Hi!

This is not a very difficult thing to accomplish. The
suggestion I gave you works.

If you want to send me a copy of your file I'll do it for
you.

In this latest explanation you say you want to go back 28
cells. Now, is that 28 cells total OR the last 28 cells
with numbers in them? As I understood your earlier posts,
you want the LAST 28 CELLS WITH NUMBERS IN THEM.

Either way, it's not difficult.

Biff

-----Original Message-----
The range is rectangular, and from left to right. The
5th cell (eg E:1) is
almost always empty, but needs to be in the equation.
The other cells are
usually full, but not always. That is why I need an
equation that will work
backwards, (right to left, then up), only factoring in
the cells that have a
numeric entry in it, until it has checked 28. At which
point it will give
the lowest number in the group. I should add, that
there
are (and will be)
duplicate entries within the 28 cells being checked. I
don't know if that
will make a difference in the equation.

To help (I think), here are the cells that are within
the
form...

H4:K15 then skip 2 rows and continue with H18:K57,
with the first
equation starting in cell P18.


"Myrna Larson" wrote:

How are you defining the "last" entries? Your range is
rectangular. Are the
numbers being entered from left to right, then down to
the next row, or from
top to bottom, then right to the next column?


On Fri, 1 Apr 2005 18:41:02 -0800, "sac73"

wrote:

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?







.




.

.