Top Ranked Opportunities

Hello,

I have a list that will be variable in length of all my accounts.
Let's assume I sell Bread Milk and Butter
I want to know of all my customer base, of the ones that are NOT buying
Bread, which ones buy the most milk or most butter, thus that I have a
better relationship with.

Can I do this with an IF rank feature?
Look at only those with zero sales in one category but the highest rank
in another?

Please help,

Glen Munro

a lot would depend on how you have your data laid out.

--
Regards,
Tom Ogilvy


"gmunro" <munrog@grandtoy.com> wrote in message
news:1127839049.944279.54790@g14g2000cwa.googlegroups.com...
> Hello,
>
> I have a list that will be variable in length of all my accounts.
> Let's assume I sell Bread Milk and Butter
> I want to know of all my customer base, of the ones that are NOT buying
> Bread, which ones buy the most milk or most butter, thus that I have a
> better relationship with.
>
> Can I do this with an IF rank feature?
> Look at only those with zero sales in one category but the highest rank
> in another?
>
> Please help,
>
> Glen Munro
>

Column B Cust Name
Column C Bread Sales
Column D Milk Sales
Column E Butter Sales
Starts at row 27

Cell C27 named TOP_Bread
Last C entry named BOT_Bread
ditto for TOP and BOT Milk and Butter

Also, Highest Bread sales Ranked 1 etc

Select your data
Data=>filter=>Autofilter

in the Bread column from the dropdown choose 0

in the milk column from the drop down, choose top 10 (you can change it to
the top number of choice)

--
Regards,
Tom Ogilvy

"gmunro" <munrog@grandtoy.com> wrote in message
news:1127840184.838533.59650@g47g2000cwa.googlegroups.com...
> Also, Highest Bread sales Ranked 1 etc
>

Gmunro,

It seems that you really need 7 different rankings: Bread only, Milk only, Butter only, Bread and
Butter but no Milk, Milk and Butter but no Bread, Bread and Milk but no Butter, and Bread and Butter
and Milk.

Put the header into F26:L26, and the formulas into F27:L27, then copy the formulas down to match
your data.

Bread Only
=IF(AND(D27=0,E27=0),SUMPRODUCT(($E$27:$E$1000=0)*($D$27:$D$1000=0)*($C$27:$C$1000>C27))+1,"")
Milk Only
=IF(AND(C27=0,E27=0),SUMPRODUCT(($E$27:$E$1000=0)*($C$27:$C$1000=0)*($D$27:$D$1000>D27))+1,"")
Butter Only
=IF(AND(C27=0,D27=0),SUMPRODUCT(($C$27:$C$1000=0)*($D$27:$D$1000=0)*($E$27:$E$1000>E27))+1,"")
Milk and Butter
=IF(AND(C27=0,D27>0,E27>0),SUMPRODUCT(($C$27:$C$1000=0)*(($D$27:$D$1000)+($E$27:$E$1000)>(D27+E27)))+1,"")
Bread and Butter
=IF(AND(D27=0,E27>0,C27>0),SUMPRODUCT(($D$27:$D$1000=0)*(($C$27:$C$1000)+($E$27:$E$1000)>(E27+C27)))+1,"")
Bread and Milk
=IF(AND(E27=0,C27>0,D27>0),SUMPRODUCT(($E$27:$E$1000=0)*(($C$27:$C$1000)+($D$27:$D$1000)>(C27+D27)))+1,"")
All Products
=IF(AND(C27>0,D27>0,E27>0),SUMPRODUCT(((($E$27:$E$1000)+($C$27:$C$1000)+($D$27:$D$1000))>(C27+D27+E27))*1)+1,"")

Change the 1000 to a number higher than your last row

HTH,
Bernie
MS Excel MVP


"gmunro" <munrog@grandtoy.com> wrote in message
news:1127840184.838533.59650@g47g2000cwa.googlegroups.com...
> Also, Highest Bread sales Ranked 1 etc
>