Going to try to give some background on the data of this needed formula so that hopefully its clear (as mud?) for whoever is so gracious to help me.
Nomination = Estimated Gas flow
Burn = Actual gas flowed
Imbalance = Nomination minus Burn
Tolerance = Amount of Imbalance allowed before the shipper has to park/loan the gas
The formula that I want to create is going to look for the gas outside of the tolerance.
Here is the tolerance per the contract:
nomination </= 50,000 = +/- 5,000 tolerance
nomination > 50,000 = 10% tolerance
Ex. 1
Nom = 10,000
Burn = 6,000
Imb = 4,000
Tolerance = +/- 5,000 (because the nomination is less than 50,000)
Gas outside of tolerance = 0 (Imb < 5000)
Ex. 2
Nom = 25,000
Burn =34,000
Imb = 9,000
Tolerance = +/- 5,000 (because the nomination is less than 50,000)
Gas outside of tolerance = 4,000 (Imb > 5,000, therefore 9,000 minus 5,000)
Ex. 3
Nom = 56,000
Burn = 70,000
Imb = -14,000
Tolerance = 10% = 5600 (56,000 * 10%)
Gas outside of tolerance = -8400 (5600 - 14000)
Ex. 3
Nom = 56,000
Burn = 46000
Imb = 10,000
Tolerance = 10% = +/- 5600 (56,000 * 10%)
Gas outside of tolerance = 4400 (10000 - 5600)
One issue I see with creating a formula is with the 10% tolerance, because it can be plus or minus the nomination. See the green highlights above.
Here is a walkthrough of how I envision the formula…
Nom = Cell Q4
Burn = Cell R4
Imb = Cell T4
IF Q4 <= 50000, then if T4>5000 or T4<-5000, then T26-5000, otherwise 0.
If Q4 > 50000, then if (0.1*Q4) > T4 or –(0.1*Q4) < T4, then T4 – (0.1*Q4) or (0.1 * Q4) – T4, otherwise 0.
I’d like this to be just one formula, but if that isn’t at all possible, I can have 2 different formulas.
Bookmarks