I would like to rank data based on sales in a division. The sales in the other divisions would not affect the rankings in for example the Central Division. Duplicates would be assigned the same rank. Is there any way to use a sum(if or small, large formula. I am trying to avoid the rank formula because it does not work with closed workbooks.
<title>Excel Jeanie HTML</title>Sheet2
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 94px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td> </td> <td> </td> <td> </td> <td>End Rank</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Salesperson A</td> <td style="text-align: right;">10000</td> <td>East</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Salesperson B</td> <td style="text-align: right;">20000</td> <td>Central</td> <td style="text-align: right;">3</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>Salesperson C</td> <td style="text-align: right;">30000</td> <td>Central</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>Salesperson D</td> <td style="text-align: right;">40000</td> <td>East</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>Salesperson E</td> <td style="text-align: right;">40000</td> <td>East</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>Salesperson F</td> <td style="text-align: right;">50000</td> <td>West</td> <td style="text-align: right;">3</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>Salesperson G</td> <td style="text-align: right;">60000</td> <td>West</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>Salesperson H</td> <td style="text-align: right;">70000</td> <td>Central</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td>Salesperson F</td> <td style="text-align: right;">80000</td> <td>West</td> <td style="text-align: right;">1</td></tr></tbody></table>
Excel tables to the web >> Excel Jeanie HTML 4
<title>Excel Jeanie HTML</title>Sheet2
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 94px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td> </td> <td> </td> <td> </td> <td>End Rank</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Salesperson A</td> <td style="text-align: right;">10000</td> <td>East</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Salesperson B</td> <td style="text-align: right;">20000</td> <td>Central</td> <td style="text-align: right;">3</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>Salesperson C</td> <td style="text-align: right;">30000</td> <td>Central</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td>Salesperson D</td> <td style="text-align: right;">40000</td> <td>East</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td>Salesperson E</td> <td style="text-align: right;">40000</td> <td>East</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td>Salesperson F</td> <td style="text-align: right;">50000</td> <td>West</td> <td style="text-align: right;">3</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>Salesperson G</td> <td style="text-align: right;">60000</td> <td>West</td> <td style="text-align: right;">2</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>Salesperson H</td> <td style="text-align: right;">70000</td> <td>Central</td> <td style="text-align: right;">1</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td>Salesperson F</td> <td style="text-align: right;">80000</td> <td>West</td> <td style="text-align: right;">1</td></tr></tbody></table>
Excel tables to the web >> Excel Jeanie HTML 4