Hello all,

I need to do a count on a list (from a database) that grows and
shrinks. It needs to exclude the last 200 entries depending on where
the end of the list is.

I'll be doing average, stdev, counta, and more functions with this
list so it is not as simple as just counting the list and subtracting
200. Essentially i need to tell microsoft where the end of the list
is, then only do calc's on everything but the previous 200 cells.

Possible?

My only idea (failure by the way) was something like

A1 = counta(B1:B1000) = result is 1000
A2 = average(B1:(address(A1,2)-200))

.... but it appears you can not have cell references when calculating
on a range.

Thanks for any help :)
-SD

"S Davis" wrote...
I need to do a count on a list (from a database) that grows and
shrinks. It needs to exclude the last 200 entries depending on
where the end of the list is.
....
Possible?

Yes.

My only idea (failure by the way) was something like

A1 = counta(B1:B1000) = result is 1000
A2 = average(B1:(address(A1,2)-200))
....

If your COUNTA formula in A1 is reliable (there are never any blank
cells in col B), you could try the following in A2.

=AVERAGE(B1:INDEX(B:B,A1-200))

Unless I misunderstand the question, I don't think A1-200 is going to work
real well. Unfortunately I don't have time right now to experiment, but if
someone hasn't gotten back to you I will look at this this evening.

However, Harlan initial answer is correct - possible = "Yes"
--
Cheers,
Shane Devenshire


"Harlan Grove" wrote:

"S Davis" wrote...
I need to do a count on a list (from a database) that grows and
shrinks. It needs to exclude the last 200 entries depending on
where the end of the list is.
....
Possible?

Yes.

My only idea (failure by the way) was something like

A1 = counta(B1:B1000) = result is 1000
A2 = average(B1:(address(A1,2)-200))
....

If your COUNTA formula in A1 is reliable (there are never any blank
cells in col B), you could try the following in A2.

=AVERAGE(B1:INDEX(B:B,A1-200))

This is possible, but I can't look at it until after work. But I don't think
A1-200 will do it.

--
Cheers,
Shane Devenshire


"Harlan Grove" wrote:

"S Davis" wrote...
I need to do a count on a list (from a database) that grows and
shrinks. It needs to exclude the last 200 entries depending on
where the end of the list is.
....
Possible?

Yes.

My only idea (failure by the way) was something like

A1 = counta(B1:B1000) = result is 1000
A2 = average(B1:(address(A1,2)-200))
....

If your COUNTA formula in A1 is reliable (there are never any blank
cells in col B), you could try the following in A2.

=AVERAGE(B1:INDEX(B:B,A1-200))

ShaneDevenshire wrote...
This is possible, but I can't look at it until after work. But I
don't think A1-200 will do it.
....

Why not? I did state 'if COUNTA formula in A1 is reliable'. If there
were no blank cells in B1:B1000 above the last nonblank cell in that
range, then COUNTA(B1:B1000) will RELIABLY give the index of the
bottommost nonblank cell in that range. More robust would be

=MATCH(9.99999999999999E+307,B1:B1000)

which will always return the index of the bottommost cell in B1:B1000
that contains a numeric value.

What I didn't do was guard against the possibility that the bottommost
numeric cell was above row 201, so with A1 using the MATCH formula
above,

=IF(A1200,AVERAGE(B1:INDEX(B:B,A1-200)),"not enough data")

If you believe this still won't work, try testing it.

Hi Folks,

I have just returned home so I appologize on not responding sooner, but I
was consulting at Apple and was not free to go on line.

Here is the comment that suggests this may not work - from the original
question - "it is not as simple as just counting the list and subtracting
200".

It looks like that's what the suggested solution is doing. So I had to ask
myself why is it not that simple? The only reason I could see was that there
are blanks in the range, which Harlan did say was he was assuming were not
present.

If the user's comment was in error than the suggestion will work as will
many other solutions such as =AVERAGE(INDIRECT("B1:b"&A1)) after modifying
the formula in cell A1 to read COUNTA(B1:B1000)-200. Note that if the user's
comment was in error you could also use Harlan's suggested formula with this
minor modification by changing it to read =AVERAGE(B1:INDEX(B:B,A1))

So on to the problem that there may be blanks:

=AVERAGE(B1:INDEX(B1:B1047,LARGE(ROW(B1:B1047)*(B1 :B1047<""),200+1)))

This formula requires array entry, and follows Harlan's approach.

or

SUMPRODUCT(AVERAGE(INDIRECT("B1:B"&LARGE(ROW(B1:B1 047)*(B1:B1047<""),200+1))))

This does not require array entry, but it is longer. Note that these
formulas don't address the issue of having less than 200 total entries. I
have set the range in both formulas not to reference the entire range; this
is because array formulas have a problem with full column references, a
problem that has been corrected in 2007.

--
Cheers,
Shane Devenshire


"Harlan Grove" wrote:

ShaneDevenshire wrote...
This is possible, but I can't look at it until after work. But I
don't think A1-200 will do it.
....

Why not? I did state 'if COUNTA formula in A1 is reliable'. If there
were no blank cells in B1:B1000 above the last nonblank cell in that
range, then COUNTA(B1:B1000) will RELIABLY give the index of the
bottommost nonblank cell in that range. More robust would be

=MATCH(9.99999999999999E+307,B1:B1000)

which will always return the index of the bottommost cell in B1:B1000
that contains a numeric value.

What I didn't do was guard against the possibility that the bottommost
numeric cell was above row 201, so with A1 using the MATCH formula
above,

=IF(A1200,AVERAGE(B1:INDEX(B:B,A1-200)),"not enough data")

If you believe this still won't work, try testing it.

ShaneDevenshire wrote...
....
If the user's comment was in error than the suggestion will work as
will many other solutions such as =AVERAGE(INDIRECT("B1:b"&A1)) after
modifying the formula in cell A1 to read COUNTA(B1:B1000)-200. Note
that if the user's comment was in error you could also use Harlan's
suggested formula with this minor modification by changing it to read
=AVERAGE(B1:INDEX(B:B,A1))

Note that using B:INDEX(B:B,x) involves no volatile function calls
while INDIRECT("B1:B"&x) does. And if you're going to use volatile
function calls, OFFSET(B1,0,0,x,1) may make more sense than INDIRECT.

So on to the problem that there may be blanks:

You're failing to include the possibilities that there could be any
nonnumeric entries in B1:B1000, so text, booleans and error values in
addition to blank cells.

=AVERAGE(B1:INDEX(B1:B1047,LARGE(ROW(B1:B1047)*(B 1:B1047<""),200+1)))
....

Or to handle any nonnumeric values, and sticking with the OP's 2-cell
layout,

A1: =LARGE(ISNUMBER(B1:B1000)*ROW(B1:B1000),201)
A2: =AVERAGE(B1:INDEX(B:B,A1))

. . . Note that these formulas don't address the issue of having
less than 200 total entries. . . .

Much more easily handled using the OP's 2-cell layout. A1 as
immediately above.

A2: =IF(COUNT(A1),AVERAGE(B1:INDEX(B:B,A1)),"not enough numbers")

. . . I have set the range in both formulas not to reference the
entire range; this is because array formulas have a problem with full
column references, a problem that has been corrected in 2007.

Also easily handled using the OP's 2-cell layout. Only the cell A1
formula is affected by this restriction, and even in old Excel
versions one could use

=LARGE(ISNUMBER(B1:B65535)*ROW(B1:B65535),201-COUNT(B65536))

The A2 formulas above *CAN* handle entire column references because
they're used in expressions that return range references.

Older Excel versions only choke on entire column references that are
evaluated as arrays during function evaluation. Older Excel versions
can handle selecting B:B and entering the array formula =ROW()+0, and
=SUM(B:B) correctly returns 2147516416, =AVERAGE(B:B) correctly
returns 32768.5, but =SUMPRODUCT(B:B) unhappily returns #NUM!. Excel's
function semantics are quirky.