Hi,
This has been asked a lot but I can't adapt solutions to my needs.
I need to rank by brand the top & bottom variance but only if the sales & plan <> 0.
Sample data below with expected results
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Brand[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"]Plan[/TD]
[TD="class: xl63, width: 64"]Var[/TD]
[TD="width: 64"]Top Rank[/TD]
[TD="width: 64"]Bottom Rank[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]7,374[/TD]
[TD="class: xl64, align: right"]6,287[/TD]
[TD="class: xl64, align: right"]1,087[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]12[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]15,849[/TD]
[TD="class: xl64, align: right"]16,798[/TD]
[TD="class: xl64, align: right"]-949[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]3,085[/TD]
[TD="class: xl64, align: right"]3,549[/TD]
[TD="class: xl64, align: right"]-464[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]9,498[/TD]
[TD="class: xl64, align: right"]11,716[/TD]
[TD="class: xl64, align: right"]-2,218[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]3,666[/TD]
[TD="class: xl64, align: right"]4,165[/TD]
[TD="class: xl64, align: right"]-499[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]10[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]5,026[/TD]
[TD="class: xl64, align: right"]6,091[/TD]
[TD="class: xl64, align: right"]-1,065[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]12,042[/TD]
[TD="class: xl64, align: right"]12,987[/TD]
[TD="class: xl64, align: right"]-945[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]7[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]4,059[/TD]
[TD="class: xl64, align: right"]5,677[/TD]
[TD="class: xl64, align: right"]-1,618[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]2,294[/TD]
[TD="class: xl64, align: right"]4,121[/TD]
[TD="class: xl64, align: right"]-1,827[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]14,603[/TD]
[TD="class: xl64, align: right"]15,349[/TD]
[TD="class: xl64, align: right"]-746[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]842[/TD]
[TD="class: xl64, align: right"]1,219[/TD]
[TD="class: xl64, align: right"]-377[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]11,814[/TD]
[TD="class: xl64, align: right"]13,996[/TD]
[TD="class: xl64, align: right"]-2,182[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]6,930[/TD]
[TD="class: xl64, align: right"]7,996[/TD]
[TD="class: xl64, align: right"]-1,066[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]3,374[/TD]
[TD="class: xl64, align: right"]5,832[/TD]
[TD="class: xl64, align: right"]-2,458[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]13,091[/TD]
[TD="class: xl64, align: right"]12,184[/TD]
[TD="class: xl64, align: right"]907[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]11[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]41,375[/TD]
[TD="class: xl64, align: right"]40,040[/TD]
[TD="class: xl64, align: right"]1,335[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]13[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]5,802[/TD]
[TD="class: xl64, align: right"]6,408[/TD]
[TD="class: xl64, align: right"]-606[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]4,325[/TD]
[TD="class: xl64, align: right"]4,702[/TD]
[TD="class: xl64, align: right"]-377[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]7[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]2,082[/TD]
[TD="class: xl64, align: right"]2,830[/TD]
[TD="class: xl64, align: right"]-748[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]21,470[/TD]
[TD="class: xl64, align: right"]22,101[/TD]
[TD="class: xl64, align: right"]-631[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]9[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]7,115[/TD]
[TD="class: xl64, align: right"]12,438[/TD]
[TD="class: xl64, align: right"]-5,323[/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
</tbody>[/TABLE]
This has been asked a lot but I can't adapt solutions to my needs.
I need to rank by brand the top & bottom variance but only if the sales & plan <> 0.
Sample data below with expected results
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Brand[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"]Plan[/TD]
[TD="class: xl63, width: 64"]Var[/TD]
[TD="width: 64"]Top Rank[/TD]
[TD="width: 64"]Bottom Rank[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]7,374[/TD]
[TD="class: xl64, align: right"]6,287[/TD]
[TD="class: xl64, align: right"]1,087[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]12[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]15,849[/TD]
[TD="class: xl64, align: right"]16,798[/TD]
[TD="class: xl64, align: right"]-949[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]3,085[/TD]
[TD="class: xl64, align: right"]3,549[/TD]
[TD="class: xl64, align: right"]-464[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]9,498[/TD]
[TD="class: xl64, align: right"]11,716[/TD]
[TD="class: xl64, align: right"]-2,218[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]3,666[/TD]
[TD="class: xl64, align: right"]4,165[/TD]
[TD="class: xl64, align: right"]-499[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]10[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]5,026[/TD]
[TD="class: xl64, align: right"]6,091[/TD]
[TD="class: xl64, align: right"]-1,065[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]12,042[/TD]
[TD="class: xl64, align: right"]12,987[/TD]
[TD="class: xl64, align: right"]-945[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]7[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]4,059[/TD]
[TD="class: xl64, align: right"]5,677[/TD]
[TD="class: xl64, align: right"]-1,618[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]2,294[/TD]
[TD="class: xl64, align: right"]4,121[/TD]
[TD="class: xl64, align: right"]-1,827[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]14,603[/TD]
[TD="class: xl64, align: right"]15,349[/TD]
[TD="class: xl64, align: right"]-746[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]842[/TD]
[TD="class: xl64, align: right"]1,219[/TD]
[TD="class: xl64, align: right"]-377[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]8[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]11,814[/TD]
[TD="class: xl64, align: right"]13,996[/TD]
[TD="class: xl64, align: right"]-2,182[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]6,930[/TD]
[TD="class: xl64, align: right"]7,996[/TD]
[TD="class: xl64, align: right"]-1,066[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl64, align: right"]0[/TD]
[TD="class: xl63"]-[/TD]
[TD="class: xl63"]-[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]3,374[/TD]
[TD="class: xl64, align: right"]5,832[/TD]
[TD="class: xl64, align: right"]-2,458[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]13,091[/TD]
[TD="class: xl64, align: right"]12,184[/TD]
[TD="class: xl64, align: right"]907[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]11[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]41,375[/TD]
[TD="class: xl64, align: right"]40,040[/TD]
[TD="class: xl64, align: right"]1,335[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]13[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]5,802[/TD]
[TD="class: xl64, align: right"]6,408[/TD]
[TD="class: xl64, align: right"]-606[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]4,325[/TD]
[TD="class: xl64, align: right"]4,702[/TD]
[TD="class: xl64, align: right"]-377[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]7[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="class: xl64, align: right"]2,082[/TD]
[TD="class: xl64, align: right"]2,830[/TD]
[TD="class: xl64, align: right"]-748[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]4[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]21,470[/TD]
[TD="class: xl64, align: right"]22,101[/TD]
[TD="class: xl64, align: right"]-631[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]9[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="class: xl64, align: right"]7,115[/TD]
[TD="class: xl64, align: right"]12,438[/TD]
[TD="class: xl64, align: right"]-5,323[/TD]
[TD="class: xl63"]13[/TD]
[TD="class: xl63"]1[/TD]
[/TR]
</tbody>[/TABLE]