Need help with a RankIF (Sumproduct) product formula

[sloaneml]

Hi all,

I am trying to rank the following examples under a certain criteria.

Overall I want to rank a number of stores by their % of business (easy) but then I want to set the criteria for the store to be ranked only if their Margin is over 30% and or to be ranked lower than a store that has hit over 30%.


The example is for an internal competition in our business and we are ranking the stores as per their % of business but there is also a minimum margin amount to qualify of 30%


Can anyone help me with a formula please?


.........% of business .........Margin.......Rank?

Store 1........6% ...............18%

Store 2.......12% ...............31%

Store 3.......21% ...............35%

Store 4.......15% ...............29%

Store 5........8% ...............37%


As per the the above under the Rank formula

.........% of business .........Margin.......Rank?

Store 1........6% ...............18%.........5

Store 2.......12% ...............31%.........3

Store 3.......21% ...............35%.........1

Store 4.......15% ...............29%.........2

Store 5........8% ...............37%.........4


but under the criteria of being ranked with margin over 30%

Store 1........6% ...............18%.........5

Store 2.......12% ...............31%.........2

Store 3.......21% ...............35%.........1

Store 4.......15% ...............29%.........4

Store 5........8% ...............37%.........3


Thanks in advance

 

[NARAYANK991]

Hi Sloan ,


It becomes very easy if you use helper columns ; is this acceptable ?


The helper column has the formula :


% of business + ( Margin > 0.3 )


Copy this value down to give an list of values 0.06 , 1.12 , 1.21 , 0.15 and 1.08


Now use the rank function to reference each calculated value ( using the same calculation as above i.e. % of business + ( Margin > 0.3 ) ) against the list of values in the helper column.


Narayan

 

[xld]

Try this


=SUMPRODUCT(--(B2+(C2>0.3)*100<=(B$2:B$6)+($C$2:$C$6>0.3)*100))