# Rank Cells Using Large/Small based on a "smart" Range

#### filipm123

##### New Member
hi all,

been building out a stock screening spreadsheet. I want to rank companies with upcoming ex-dividend dates in the next week or so. I am half way there ( I think, but if someone has a better way or has done this before, please let me know!!)
<P>
so you have a list of companies and ex-dividend dates. Column C is an if statement that if date is equal to today's date + 1 day but not greater than + 10 days

<P>

=IF(AND(b1>now()+1,B34<now()+10),"DIV","")

<table>
A B C
GE 6/19/2008 DIV
APPLE 7/12/2008
GM 6/21/2008
Chase 6/15/2008
J&J 6/23/2008 DIV
Pepsico 9/24/2008
Verizon 10/1/2008
ATT 12/5/2008
Etc 12/28/2008 DIV
Etc 6/29/2008 DIV
Etc 5/30/2008
Etc 7/1/2008
</table>
so now you need to set up a ranking of these companies and their respective ex-dividend dates. You should ONLY rank cells in Column B if Column C has a value of "DIV"
=small(SmartRange,1)
I realize an array formula is probably best here, just been blanking out!

Any ideas guys and gals? thanks!

Last edited:

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### konew1

##### Well-known Member
The first company "ETC" has December date but DIV in col C. I assume that is just a typo.

This will rank the dates where there is DIV in Col C, but it ranks against the entire list, not just the ones tagged DIV.
=IF(C1="","",RANK(B1*(C1="DIV"),(\$B\$1:\$C\$13),1))

If you change the result of the IF statements in column C to return the dividend date and not the text DIV you could then easily rank on column C alone

#### filipm123

##### New Member
The problem of ranking the entire list of dates is exactly the problem i was running into to!! - but why didnt I think of that. Simply return the dates I want in Column C and then rank on those. Ingenious!! - ha, thanks so much for the help. Its obvious that I am running on empty right now. Appreciate the help konew1..

Replies
2
Views
681
Replies
3
Views
545
Replies
4
Views
367
Replies
7
Views
2K
Replies
0
Views
426

1,191,174
Messages
5,985,102
Members
439,940
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back