PDA

View Full Version : Using RANK on a range of data - need an exception



BlondieC
08-29-2017, 10:44 AM
Hi, this is the last piece for a little tool I'm working on. I've used RANK for the data range but what I need is for any cell with 0.00 in Column E to remain with that cell content and not be ranked numerically and preferablyy be at the bottom of the data content.

Only cells >0.00 should be ranked highest to lowest based on the revenue dollar amount.

The idea is to have the ranking number displayed rather that the revenue dollar amount except where the revenue = 0.00. Currently the data is ranking the way it should and displays the way it should but the rows with 0.00 are also displaying a ranking number. Thank you




Sheets("SiteCopy").Select

Range("E8:E" & Range("E7").End(xlDown).Offset(-1).Row).Name = "DataRank"

With Range("DataRank").Offset(0, 1)
.FormulaArray = "=RANK(DataRank,DataRank)"

End With



Initial state of raw data:



Site CC
Site Name
Business Name
Revenue


101874
Toronto
Business 10
$10,150.22


103918
Montreal
Business 12
$999.81


15741
Vancouver
Business 5
$419.75


104144
Hamilton
Business 8
$553.60


438812
Paris
Business 23
$2,355.00


105250
Victoria
Business 54
$2,590.10


104982
Peterborough
Business 98
$2,473.55


356409
Point Pelee
Business 8
$0.00


104236
Prince Albert
Business 4
$429.30


Revenue Total


$19,971.33





After ranking:



Site CC
Site Name
Business Name
Ranking


101874
Toronto
Business 10
1


105250
Victoria
Business 54
2


104982
Peterborough
Business 98
3


438812
Paris
Business 23
4


103918
Montreal
Business 12
5


104144
Hamilton
Business 8
6


104236
Prince Albert
Business 4
7


15741
Vancouver
Business 5
8


356409
Point Pelee
Business 8
9


Revenue Total


$19,971.33




Desired outcome:



Site CC
Site Name
Business Name
Ranking


101874
Toronto
Business 10
1


105250
Victoria
Business 54
2


104982
Peterborough
Business 98
3


438812
Paris
Business 23
4


103918
Montreal
Business 12
5


104144
Hamilton
Business 8
6


104236
Prince Albert
Business 4
7


15741
Vancouver
Business 5
8


356409
Point Pelee
Business 8
$0.00


Revenue Total


$19,971.33

mdmackillop
08-29-2017, 01:20 PM
How about a simple IF
=IF(A1>0,RANK(A1,$A$1:$A$20),A1)