Counting only active cells

S

Sac73

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

Bob Phillips

What defines active?:

--

HTH

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

sac73

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)
 
M

Myrna Larson

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))
 
B

Biff

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
 
S

sac73

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

Myrna Larson

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

Biff

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
 
S

sac73

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

Biff

Hi!
That means that there are empty cells throughout
the 'Helper column'.
Click to expand...

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
 
M

Myrna Larson

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

sac73

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

Biff

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
 
S

sac73

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

Biff

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

On second thought, I withdraw that offer.

You ignored my first reply and my most recent reply.

You figure it out!

Biff
 
S

sac73

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