Drilling Rig Allocation and Contract Renewals

[asifmian55]

Dear Friends:

I am trying to solve a problem in the attached spreadsheet. I have to allocate a Drilling ring in a specified year with a certain starting day rate. Every 4 years (period specified by the user), the contract will be renewed at a new day rate (the rate will be specified). Could you please help me automate these calculations? I have 25 years and up to 10 rigs will be introduced at different times. I used the MOD function but it is not giving me correct answer.

I have made it to work but it is not flexible, i.e. as soon as I change any of the values in Cell C1, C2 OR C3 my results are messed up.

Your prompt response will be very highly appreciated.

Thanks and regards,

[AliGW]

I see no MOD formula in the sheet you have attached. Where are you using it, what is it and in what way is it not producing the correct results? Does this workbook show the results you are expecting or the incorrect ones? What is the significance of the cells highlighted in yellow?

[asifmian55]

This is the result I want got. This is juat the sample of what I want to acieve. The MOD function is used in my original midel.

[AliGW]

OK - let's try again!

1. Please tell us what the MOD formula is that you have tried.
2. Tell us in which cell(s) you tried it.
3. Tell us the significance of the cells you have highlighted in yellow.

I will try to help when you have answered these three questions.

[asifmian55]

Please check the revised spreadsheet. It is working fine as is but as soon as I change C1 or C2 or C3, all my calculations are messed up.

[shg]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Contract Period, Years	4
2	Rig	Start Year
3	1	2019
4	2	2021
5	Contract Period	Rate
6	0	0
7	1	40
8	2	45
9	3	50
10
11	Calender Year	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028
12	Contract Period - Rig 1	0	0	1	1	1	1	2	2	2	2	3	3		B12: =MAX(0, INT((B$11 - $B3)/$B$1) + 1)
13	Contract Period - Rig 2	0	0	0	0	1	1	1	1	2	2	2	2		B13: =MAX(0, INT((B$11 - $B4)/$B$1) + 1)
14	Rate - Rig 1	0	0	40	40	40	40	45	45	45	45	50	50		B14: =VLOOKUP(B12, $A$6:$B$9, 2)
15	Rate - Rig 2	0	0	0	0	40	40	40	40	45	45	45	45		B15: =VLOOKUP(B13, $A$6:$B$9, 2)

[shg]

As attached.

[asifmian55]

Thank you so much. This is excellent.

[shg]

You're welcome.