On Aug 16, 4:20*am, "Bob Phillips" <bob....@somewhere.com> wrote:
> You could use this array formula
>
> =IF(ISERROR(SMALL(IF((Team=F$20)*(Sales=F$21),ROW(Sales)-MIN(ROW(Sales))+1,*""),ROW(A1))),"",INDEX(Colour,SMALL(IF((Team=F$20)*(Sales=F$21),ROW(Sales)-*MIN(ROW(Sales))+1,""),ROW(A1))))
>
> copy it down, it gives the values in separate rows
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "nj" <nwarn...@yahoo.com> wrote in message
>
> news:95820743-ef30-43fb-8df6-(E-Mail Removed)...
>
>
>
> > OK, I give. I've worked on this off and on for days, I've hunted and
> > hunted for other posts that might be close enough to get me there, but
> > no luck. Is anyone up for helping with this?
>
> > I'm trying to set up acustom functionto return all the members of a
> > team who made the top sales number. Here's some sample data:
>
> > Team Color Sales
> > Team A Red 87
> > Team A Blue 87
> > Team A White 29
> > Team A Black 28
> > Team B Yellow 83
> > Team B Green 4
> > Team B Teal 2
> > Team B Silver 1
> > Team C Tan 121
> > Team C Gray 119
> > Team C Brown 4
> > Team C Purple 2
> > Team D Aqua 94
> > Team D Gold 94
> > Team D Pink 29
> > Team D Orange 1
>
> > Now, I found and have used the max(if()) function to find that top
> > sale number per team,
> > =MAX(IF((Team=F$20),Sales,0)) -- array function, so entered with Ctrl
> > +Shft+Enter
> > * *(Which reminds me, I've used sumproduct for all the other
> > calculations because we find people break array functions done this
> > way, but DANGED if I can figure out how to work Max with Sumproduct.
> > Grrr.)
>
> > Oh, yes, forgot -- I've set up the dynamic named ranges as well, so
> > Team *=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
> > Sales =OFFSET(Sheet1!$A$2,0,2,COUNTA(Sheet1!$A:$A)-1,1)
>
> > So the Max results look like this:
>
> > Team A Team B Team C Team D
> > Max Sales 87 83 121 94
>
> > Now, if there could only be one top seller, I would have my solution
> > because I discovered Ctrl+Shft+Enter allow Match to work with arrays,
> > and I have the formula
> > =INDEX($A$2:$C$17,MATCH(F20&F21,$A$2:$A$17&$C$2:$C$17,0),2) * -- array
> > function, so entered with Ctrl+Shft+Enter
>
> > so now it looks like this:
>
> > Team A Team B Team C Team D
> > Max Sales 87 83 121 94
> > Top Seller(s) Red Yellow Tan Aqua
>
> > But what I *really* need is:
>
> > Team A Team B Team C Team D
> > Max Sales 87 83 121 94
> > Top Seller(s) Red, Blue Yellow Tan Aqua, Gold
>
> > Is anyone up for messing with that?
>
> > I'd be very grateful if anyone were so inclined.
>
> > NJ- Hide quoted text -
>
> - Show quoted text -
Very interesting, but the real context is a summary report per site,
by team, where each col is a team and each row is another statistic
type. There are about 50 rows, and I would introduce a different
problem if the number of rows for a statistic varies.
Similar Threads
