• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

Excel question

DogsofJune

DogsofJune

Supreme [H]ardness
2FA
Joined
Nov 7, 2008
Messages
5,979
I am trying to work on a spreadsheet that reflects a price fuel adjustment in Excel from Office 2003.

I have a cell that will contain the info of the contract price for fuel.
The next cell is for the price of fuel cost for that particular month that work was performed.
Next cell contains the calculation that reflects the difference.
If the price of fuel is just a difference of .25 cents or less then no action is taken.

Say the contract price was for $2, then if the monthly price fluctuates between $1.75 to $2.25 then no adustment is made.

If greater than a .25 change in monthly fuel price, then make the adjustment based off of the contractors monthly fuel usage.

So if the contract price was for $2, and for the month of March the price of fuel went down to $1.65, then the Adjustment Factor would be -.35

I have used an IF statment which works as long as both values are positive. When introduced to a negative, "0" is in the calculated cell.

=IF(J4<=0.25,0,IF(J4>0.25,J4*E4)) cell J4 is the Monthly Fuel Index Adjustment Factor.

This statment works for a positive but I haven't managed to complete the statment to handle a negative number. SHould I be looking at this differntly? Maybe the "IF" statement isn't what I really should be using?

Make sense? Any assitance would be helpful as I sniff around the web for more info.
 
I'm not sure I know which columns contain which values from your description. Maybe J4 is the change; if that's the case, then the IF statement is doing exactly what you describe.

What should happen when the change is price is negative? In your example, what should happen when the difference is -0.35 ?

Do you mean to use ABS(J4) <= 0.25 so that the magnitude of the change is 0.25 or less, you do nothing? This means changes between -0.25 and 0.25 (exclusive) result in zero adjustment.
 
My issue is what you are pointing out. When a neg number is used in the calculation I get a response of zero.

What should happen is this. I'll try and explain the best I can, bear with me.

What I posted above is a partial statment, I have not completed it with any success and was a bit timid with divulging the remainder. Hehe.

If there is a change in the Monthly Fuel Index that is greater than a +-.25, I would calculate a price adjustment.

So, I would have a cell for Percent of Project Complete, we'll call it "A"
A cell for Total Fuel Allocation, we'll call it "B"
A cell for Fuel Use\Pay Period. Fuel used for the month, which is calculated as (A*B) We'll call this "C"
A cell for Starting Fuel Index. The fixed contract price for fuel for this project. We'll call this "D"
A cell for Monthly Fuel Index. This price fluctuates. We'll call this "E"
A cell for Monthly Fuel Index Adjustment Factor, which is calculated as (D-E) We'll call this "F"
The last cell is the Fuel Adjustment, which is calculated as (F*C)

The issue I have is that the Monthly Fuel Index is a variable that fluctuates and can be less than the Starting Fuel Index, which resultes in a negative number. It seems when I use the IF statment and a negative number is used, it fails to work.

The buffer is that the Starting Fuel Index is allowed to move up or down 25 cents before the adjustment kicks in.


The whole point of this is a contractor can elect to use a price adjustment for his fuel usage for the work he needs to perform. In the event that the prices would go screaming up again, the contractor would not be locked into a price and be properly compensated, however, this can work to the contractors disadvantage as well, if the price goes lower than the original contract price. Then a deduction would be needed.

I'm trying to create a sheet to reflect this monthly change.
 
Did you read my suggested solution? I don't see any acknowledgment of it in your post.
 
Now, ABS would return an absolute value of the number, correct?
 
Well, with more fiddling, I have managed to get a result of 1 instead of 0.
The whole point tho is to display the deduction in the event that the fuel price drops below 25 cents of the original price.
 
It'll be a lot easier to tell you what's wrong with the formula if I can actually see it. Without looking, I have to guess at what you did or didn't do, then ask you if that's what you did, then interpret your answer.

Will you show the forumula that you're now using, or do I have to guess at it?
 
I assume that if the Monthly Fuel Index was higher than the Starting Fuel Index, that you want a positive Fuel Adjustment if the difference is owed the contractor and a negative if the contractor owes?

If so, then if the absolute value of your difference (F1) is greater than/equal to 0.25, multiply your difference by the Fuel Use for the period (C1), otherwise show 0.

=IF(ABS(F1)>=0.25,F1*C1,0)

Note that your difference should be the Monthly Fuel Index minus your Starting Fuel Index (E1-D1). If you want to reverse the sign of the result (i.e.: a negative if the contractor is owed) just change the difference (F1) to D1-E1.
 
You may have solved my issue kind sir, sorry it took so long to respond, I am up to my eyebrows in work right now.

I will report my results when completed.
 
Literally, absolutly what I was looking for. Made one change to .26 instead of .25.
Thanks to both of you for your time.
 
Ooops, sorry about that. I just reread your orig post and saw that .25 or less should be no change. Anyways, glad to help.
 
You must log in or register to reply here.
Back
Top