ranking formula used in conjunction with a selection box

Hi all,

I am working something for my management and was hoping to improve on a tool I had created for them. As you can see in the attached example on the analysis tab, I have a drop down box that allows the user to select an office organization cell G4 which then populates the data for the 26 programs planned activity, monthly and year to date (YTD) actuals. What I need assistance with is a formula at the bottom of this worksheet that would evaluate the 5 mission totals and organize the delta's at the bottom so that the missions would be ranked from highest to lowest. These values will need to be able to change (be dynamic if that is the proper term) and re-sort based upon what office org is selected. I have hard keyed how the formulas would need to work at the bottom of the page, using office "A" as the selected org.


Any help you can provide is greatly appreciated.

Thx.

Chris

Thank you for the help! This brings me one step closer however I do run into an issue where in my live data I have office's that have exactly the same numbers, and the LARGE formula ignores duplicates. Is there a modification that could be done to this formula to alleviate this. In the updated example "AA" and "CC" have the same delta but it duplicates "AA" I believe since it is the first alphabetically. Any help is always appreciated.

Thx.

C.