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