Although the formula works perfectly on a set number of rows I have to alter
it to allow for extra rows or less rows.
As an example if I have data running to 21 rows but I have to go into the
formula bar to manually adjust to incorporate the extra row.
Similarly if the original formula is for 20 rows but I wish to run for only
say 8 rows again I need to manually adjust the formula downwards.
May I ask is it possible for this adjustment to be made automatically.
Subject: Re: Automatic sort and Highlight 10/17/2008 8:29 AM PST
By: Celticshadow In: microsoft.public.excel
Dear Roger, Ken and Shane
Many thanks for your replies. I can now confirm that I have sussed the
formula and can also report that it works magnificently, top draw help once
"Roger Govier" wrote:
> Hi Ken
> I should have thought it through myself. Of course, the blank cells would
> mess it up.
> Adding the extra term to Sumproduct sorts it nicely.
> Roger Govier
> >> Hi Ken
> >> I tested it with the series of numbers
> >> 5,8,12,12,12,12,20,23,45,46,47
> >> With the formula using <=6, it highlighted down to number 23 i.e. the
> >> first
> >> 5 unique numbers
> >> Substituting 6 with 7 it highlighted the first 6 unique numbers, i.e down
> >> to
> >> number 45 - the same result as I got with Shane's formula
> >> --
> >> Regards
> >> Roger Govier
> >> > The +1 in the formula is to make the ranks start at 1 rather than 0 so
> >> > I think it's OK as is.
> > My formula will only return the correct rank when the ranges used in
> > the various arguments have the same number of rows as there are values
> > to rank...
> > D1:D20 has 20 rows the array of values in your test has 11 rows.
> > This means rank 1 was taken by the 9 blank cells below your test
> > values.
> > So, I have changed to...
> > =SUMPRODUCT(--(D$1:D$20<>""),--(D1>D$1:D$20),1/COUNTIF(D$1:D$20,D$1:D
> > $20&""))+1<=6
> > Thanks for pointing out my error.
> > Ken Johnson