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!

<colgroup><col><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

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

<colgroup><col span="2"><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[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!!