Excel column data representation problem

  • Thread starter Thread starter adeater
  • Start date Start date
A

adeater

Can anyone help me with this problem?

I have a column of data from P3 to P1007

entering =max(p3:p1007) gives the maximum value in another cell...
need a way to display the five largest and five smallest values betwee
P3 and P1007 in a column (ie a new column of 10 values).

Hope someone knows how
 
In R3 enter & copy down:

=IF(ROW()-ROW(R$3)+1<=10,IF(ROW()-ROW(R$3)+1<=5,LARGE($P$3:$P$1007,ROW()-ROW
(R$3)+1),SMALL($P$3:$P$1007,ROW()-ROW(R$3)+1-5)),"")

Note that the ROW(R$3) bit in the formula references the first cell in which
the formula is entered.
 
Hi

Into some cell enter the formula
=SMALL(P$3:P$1007;ROW(P1))
and copy down for 5 rows.
Below it enter the formula
=Large(P$3:P$1007;ROW(P1))
and again copy down for 5 rows.

NB! When there are several identic values falling into range of 5
smallest/largest ones, then SMALL/LARGE does return them. So when you need 5
smallest/largest DIFERENT values, those formulas doesn't qualify.
 
I have columns of data P3:P1007 and that is filtered for the 5 larges
and 5 smallest values by

=IF(ROW()-ROW(AF$3)+1<=10,IF(ROW()-ROW(AF$3)+1<=5,LARGE($P$3:$P$1007,ROW()-ROW(AF$3)+1),SMALL($P$3:$P$1007,ROW()-ROW(AF$3)+1-5)),"")

The problem is that the columns P3:P1007 and Q3:Q1007 and so on til
AB3:AB1007 are values related to column O3:O1007 which has dates in th
form of 20000809 (9th august 2000). When the 5 largest and 5 smalles
values for P3:P1007 are shown i need a way to display the correspondin
date from O3:O1007 to show up in an adjacent column. Havent been abl
to understand Lookup or Match functions and would appreciate help
 
Back
Top