Need a ranking formula which minus out dates in another column

[Cosmic Wizard]

Hello there,
This is a tough questions, but basically i need ranking formula which can minus off a numbers.
I have these demountables under contract and when we go over 48, we have to pay more.
But we also remove demountables, so we can go back under 48.
We are not sure when we should have been charge for the extra demountables, and we have many contacts and sites.
What i need is a a count which minus off a demountable when it is remove.

Any help would be greatly appreciated, as i am truly struck here. Thankyou!


[TABLE="width: 646"]
<colgroup><col><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 596"]
<colgroup><col span="2"><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Placement[/TD]
[TD] [/TD]
[TD]Rank 1[/TD]
[TD]True Rank[/TD]
[TD]Removal[/TD]
[TD]Rank 2[/TD]
[/TR]
[TR]
[TD="align: right"]5-Feb-97[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]20-Oct-08[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD="align: right"]27-Oct-99[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]6-Aug-07[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: right"]5-Nov-99[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]17-Oct-08[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD="align: right"]17-Apr-00[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]6-Oct-08[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD="align: right"]29-Mar-01[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]13-Oct-08[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD="align: right"]21-Aug-01[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]14-Oct-08[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD="align: right"]20-May-02[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]30-Jan-03[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]20-May-02[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]14-Jan-03[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]20-May-02[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]30-Jan-03[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]20-May-02[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]14-Jan-03[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]23-May-02[/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD]14-Jan-03[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]18-Dec-02[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]13-Oct-08[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-03[/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD]20-Oct-08[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD="align: right"]1-May-03[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD]17-Oct-08[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD="align: right"]2-Sep-05[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]2-Sep-05[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]16-Dec-07[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]2-Sep-05[/TD]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]17-Dec-07[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD="align: right"]3-Sep-05[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]29-Dec-07[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD="align: right"]3-Sep-05[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]30-Dec-07[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD="align: right"]3-Sep-05[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]28-Dec-07[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD="align: right"]3-Sep-05[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]26-Dec-07[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD="align: right"]3-Sep-05[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]3-Sep-05[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep-05[/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[TD]31-Dec-07[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep-05[/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[TD]20-Dec-07[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep-05[/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[TD]20-Dec-07[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep-05[/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[TD]26-Dec-07[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep-05[/TD]
[TD][/TD]
[TD]24[/TD]
[TD][/TD]
[TD]26-Dec-07[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD="align: right"]7-Sep-05[/TD]
[TD][/TD]
[TD]29[/TD]
[TD][/TD]
[TD]4-Jan-08[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD="align: right"]8-Sep-05[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]2-Jan-08[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD="align: right"]8-Sep-05[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]28-Dec-07[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD="align: right"]8-Sep-05[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]3-Jan-08[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD="align: right"]8-Sep-05[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]31-Dec-07[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD="align: right"]9-Sep-05[/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]18-Nov-07[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD="align: right"]9-Sep-05[/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]19-Dec-07[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD="align: right"]9-Sep-05[/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]29-Dec-07[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD="align: right"]9-Sep-05[/TD]
[TD][/TD]
[TD]34[/TD]
[TD][/TD]
[TD]30-Dec-07[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD="align: right"]10-Sep-05[/TD]
[TD][/TD]
[TD]38[/TD]
[TD][/TD]
[TD]17-Dec-07[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD="align: right"]10-Sep-05[/TD]
[TD][/TD]
[TD]38[/TD]
[TD][/TD]
[TD]29-Dec-07[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-05[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-05[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]31-Dec-07[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-05[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]4-Jan-07[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-05[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-05[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]16-Dec-07[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-05[/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]13-Sep-05[/TD]
[TD][/TD]
[TD]46[/TD]
[TD][/TD]
[TD]22-Dec-07[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD="align: right"]13-Sep-05[/TD]
[TD][/TD]
[TD]46[/TD]
[TD][/TD]
[TD]20-Dec-07[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD="align: right"]13-Sep-05[/TD]
[TD][/TD]
[TD]46[/TD]
[TD][/TD]
[TD]20-Dec-07[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD="align: right"]13-Sep-05[/TD]
[TD][/TD]
[TD]46[/TD]
[TD][/TD]
[TD]26-Dec-07[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD="align: right"]14-Sep-05[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]16-Dec-07[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]14-Sep-05[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]23-Dec-07[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD="align: right"]14-Sep-05[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]23-Dec-07[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD="align: right"]14-Sep-05[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]23-Dec-07[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD="align: right"]15-Sep-05[/TD]
[TD][/TD]
[TD]54[/TD]
[TD][/TD]
[TD]16-Dec-07[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]15-Sep-05[/TD]
[TD][/TD]
[TD]54[/TD]
[TD][/TD]
[TD]21-Dec-07[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD="align: right"]16-Sep-05[/TD]
[TD][/TD]
[TD]56[/TD]
[TD][/TD]
[TD]20-Dec-07[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD="align: right"]16-Sep-05[/TD]
[TD][/TD]
[TD]56[/TD]
[TD][/TD]
[TD]15-Dec-07[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD="align: right"]17-Sep-05[/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD]17-Dec-07[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD="align: right"]17-Sep-05[/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD]24-Dec-07[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD="align: right"]17-Sep-05[/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD]24-Dec-07[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD="align: right"]17-Sep-05[/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD]2-Jan-08[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD="align: right"]17-Sep-05[/TD]
[TD][/TD]
[TD]58[/TD]
[TD][/TD]
[TD]2-Jan-08[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD="align: right"]19-Sep-05[/TD]
[TD][/TD]
[TD]63[/TD]
[TD][/TD]
[TD]20-Dec-07[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD="align: right"]19-Sep-05[/TD]
[TD][/TD]
[TD]63[/TD]
[TD][/TD]
[TD]22-Dec-07[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD="align: right"]19-Sep-05[/TD]
[TD][/TD]
[TD]63[/TD]
[TD][/TD]
[TD]16-Dec-07[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]19-Sep-05[/TD]
[TD][/TD]
[TD]63[/TD]
[TD][/TD]
[TD]22-Dec-07[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD="align: right"]20-Sep-05[/TD]
[TD][/TD]
[TD]67[/TD]
[TD][/TD]
[TD]7-Jan-08[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD="align: right"]21-Sep-05[/TD]
[TD][/TD]
[TD]68[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]21-Sep-05[/TD]
[TD][/TD]
[TD]68[/TD]
[TD][/TD]
[TD]27-Dec-07[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD="align: right"]22-Sep-05[/TD]
[TD][/TD]
[TD]70[/TD]
[TD][/TD]
[TD]15-Dec-07[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD="align: right"]22-Sep-05[/TD]
[TD][/TD]
[TD]70[/TD]
[TD][/TD]
[TD]17-Dec-07[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD="align: right"]24-Sep-05[/TD]
[TD][/TD]
[TD]72[/TD]
[TD][/TD]
[TD]23-Dec-07[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD="align: right"]28-Sep-05[/TD]
[TD][/TD]
[TD]73[/TD]
[TD][/TD]
[TD]2-Jan-08[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD="align: right"]4-Jan-06[/TD]
[TD][/TD]
[TD]74[/TD]
[TD][/TD]
[TD]13-Oct-08[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD="align: right"]6-Aug-07[/TD]
[TD][/TD]
[TD]75[/TD]
[TD][/TD]
[TD]20-Oct-08[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD="align: right"]29-Jan-08[/TD]
[TD][/TD]
[TD]76[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb-08[/TD]
[TD][/TD]
[TD]77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6-Mar-08[/TD]
[TD][/TD]
[TD]78[/TD]
[TD][/TD]
[TD]6-Oct-08[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD="align: right"]19-Sep-08[/TD]
[TD][/TD]
[TD]79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9-Jan-09[/TD]
[TD][/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-Mar-09[/TD]
[TD][/TD]
[TD]81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18-Jan-10[/TD]
[TD][/TD]
[TD]82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23-Jan-10[/TD]
[TD][/TD]
[TD]83[/TD]
[TD][/TD]
[TD]7-Jul-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23-Jan-10[/TD]
[TD][/TD]
[TD]83[/TD]
[TD][/TD]
[TD]8-Jul-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23-Jan-10[/TD]
[TD][/TD]
[TD]83[/TD]
[TD][/TD]
[TD]8-Jul-10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7-Jul-10[/TD]
[TD][/TD]
[TD]86[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8-Jul-10[/TD]
[TD][/TD]
[TD]87[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-11[/TD]
[TD][/TD]
[TD]88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-11[/TD]
[TD][/TD]
[TD]88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-11[/TD]
[TD][/TD]
[TD]88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Jul-11[/TD]
[TD][/TD]
[TD]91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8-Dec-11[/TD]
[TD][/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8-Dec-11[/TD]
[TD][/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8-Dec-11[/TD]
[TD][/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8-Dec-11[/TD]
[TD][/TD]
[TD]92[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16-Dec-11[/TD]
[TD][/TD]
[TD]96[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6-Jan-12[/TD]
[TD][/TD]
[TD]97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Double posted, probably by accident: http://www.mrexcel.com/forum/excel-...ula-can-minus-out-numbers-another-column.html

 

[Smitty]

Double posted, probably by accident: http://www.mrexcel.com/forum/excel-...ula-can-minus-out-numbers-another-column.html

I deleted the duplicate thread - Moderator

 

[Cosmic Wizard]

I deleted the duplicate thread - Moderator

Sorry guys, my mistake, this problem has me tearing the hair out!!!!

 

[Cosmic Wizard]

Got it, or with a really ugly solutions.

There are three columns with formulas in them.

First you rank the placements in terms of date =RANK(A2,$A$2:$A$277,1)

Then in the next column you use a countif to find out how many demountables have been removed before your placement =COUNTIF($D$2:$D$277,"<"&A2)

Then it a simple matter of using the sum =SUM(B1-C1). This well get you your placement rank minus any demountable removed.

I have no doubt there are easily ways of doing this, but his works so brilliant!!