N
nj
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 a custom function to 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
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 a custom function to 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