How to use functions properly for a certain problem?

[MuhammadZiaUrRehman]

Hi, My name is Muhammad and I am working in Pakistan Petroleum. I am making a policy model for my seniors. During the work, I encountered a problem with the formula making in excel. I have given a table below. what I need to do is build a model for the bonus amount that has to be taken from first table of Cumulative Production and Amount Columns. The model says that upon reaching first 90 days of start of commercial production, the one time bonus to be given is 400000. and then upon reaching a certain level of production like 60 MMBOE, 120 MMBOE ,160 MMBOE and 200 MMBOE, we need to give corresponding one time amount of bonus in form of 1600000, 3000000, 9000000,and 12000000. However this amount will not be paid annually but just one time. If function gives the amount but what it does is that it puts the bonus in the next years as well which is not required. Like if we reach 60 MMBOE in 2019 and 80 MMBOE in 2020, then the amount should reflect only in the cell corresponding to 2019 not in 2020 where it should be zero. and then upon reaching or crossing 120 MMBOE next amount should show and so on. If anyone can help me in this regard as to what kind of formula can be applied to do it what kind macros can be written to do it, it will be very helpful for me and I will be very thankful. Or if it can be done only manually, then also please do tell. thank you
I have also attached a file .

Cumulative Production Amount Cumulative MMBOE Year Bonus
90 days 400,000 2017
60 MMBOE 1,600,000 2018
120 MMBOE 3,000,000 2019
160 MMBOE 9,000,000 2020
200 MMBOE 12,000,000 2021

[JohnTopley]

Your file only contains a table and we need (yearly?) data/examples showing how the "model" is be applied

[oeldere]

show in your file some manual result with given conditions.

[MuhammadZiaUrRehman]

Here this may help you understand the situation

Thank you

Please Login or Register  to view this content.

[MuhammadZiaUrRehman]

Sorry the file was not being attached

[JohnTopley]

To upload file Click "Go advanced" then scroll down to "Manage attachments"

[oeldere]

change the vlookup table.

after that:

=if(MAX($M$4:$M4)=VLOOKUP(J5,$E$6:$F$10,2,1),0,VLOOKUP(J5,$E$6:$F$10,2,1))

see the attached file.

[MuhammadZiaUrRehman]

Thank you very much John it was really helpful, the improvement that you showed to me in the model is close to perfect. In the model the first bonus is still not being captured when at the start of production ,we pay a bonus. It can be linked to somehow the first non 0 value that appears in the cumulative production column or the MIN Function, however it also doesnt have to copy in the cells for years after that as its a one time payment. I am trying that myself as well. If you can figure out something, it would be great, otherwise , this model is also sufficient.




Again! thank you very much for your kind help.

I will be posting some other problems as well in coming days.

[JohnTopley]

Thanks are due to Oeldere not myself.

[oeldere]

Please Login or Register  to view this content.

Thank you very much Oeldere it was really helpful

Yes I noticed that, but also in your new example you don't show any date in your file, in which the 90 days can be counted.

[oeldere]

please read the forumrules, especialy the ones about crossposting.

http://www.mrexcel.com/forum/excel-q...ml#post4521895

[MuhammadZiaUrRehman]

Okey I have read the forumrules now and will stick to them. Once Again thank you very much for the assistance. I finally figured out what was wrong with my vlookup table and it is now perfect. I will post new threads when a problem arises.