filter rows rather than columns in Excel 2003?

[Lynne]

can this be done? I have 12 entries in a row...need the top 10 averaged,
lowest 2 discarded.

[Mike Armstrong]

Lynne,

I haven't found a filter row solution yet, but this may help to start with
anyways. Select your data and choose to copy it, but instead of just pasting
it, do a paste special and choose the checkbox at the bottom to "transpose".
That will take your row format and change it to a column format that you can
then use autofilter on. When you're done, you can do the same thing in
reverse. Copy the data and Paste Special and transpose again. Hope this helps
as at least a temporary fix.

"Lynne" wrote:

> can this be done? I have 12 entries in a row...need the top 10 averaged,
> lowest 2 discarded.

[Chip Pearson]

You can get the average of the top 10 entries with the following
formula

=AVERAGE(LARGE(A1:A12,ROW(INDIRECT("1:10"))))

Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Lynne" <Lynne@discussions.microsoft.com> wrote in message
news:6614EAA5-ECBF-4B2E-A897-02C816AC3EE4@microsoft.com...
> can this be done? I have 12 entries in a row...need the top 10
> averaged,
> lowest 2 discarded.

[Lynne]

Chip,

Brillliant! You've saved me hours of work with my mark sheets. Thanks so
much!

"Chip Pearson" wrote:

> You can get the average of the top 10 entries with the following
> formula
>
> =AVERAGE(LARGE(A1:A12,ROW(INDIRECT("1:10"))))
>
> Since this is an array formula, you must press Ctrl+Shift+Enter
> rather than just Enter when you first enter the formula and
> whenever you edit it later. If you do this properly, Excel will
> display the formula enclosed in curly braces {}.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Lynne" <Lynne@discussions.microsoft.com> wrote in message
> news:6614EAA5-ECBF-4B2E-A897-02C816AC3EE4@microsoft.com...
> > can this be done? I have 12 entries in a row...need the top 10
> > averaged,
> > lowest 2 discarded.
>
>
>

[Lynne]

Mike,

Read Chip's response. I have tried what you suggested but it's alot of
extra work with the spreadsheets. Chip's formula works perfectly as long as
there are numbers i.e. 0 or greater, in every cell. Blanks won't work!

"Mike Armstrong" wrote:

> Lynne,
>
> I haven't found a filter row solution yet, but this may help to start with
> anyways. Select your data and choose to copy it, but instead of just pasting
> it, do a paste special and choose the checkbox at the bottom to "transpose".
> That will take your row format and change it to a column format that you can
> then use autofilter on. When you're done, you can do the same thing in
> reverse. Copy the data and Paste Special and transpose again. Hope this helps
> as at least a temporary fix.
>
> "Lynne" wrote:
>
> > can this be done? I have 12 entries in a row...need the top 10 averaged,
> > lowest 2 discarded.