Actual Actual Wanted Wanted
Variance Estimate Permanent Temporary
a1 -800 -1000 -800 0
b1 800 1000 800 0
c1 800 -1000 0 800
d1 -800 1000 0 -800
Positive = overage
Negative = savings
I am trying to come up with a formula that takes Actual Varience against the actual estimated and tells me what portion of the actual variance is permanent or temporary
ie. (b1) we estimated we would go over budget $1000 and so far we have gone over budget $800, so i can assume the $800 is a permanent overage as it is less than the annual estimate but on the right track (result I want is $800 permanent, $0 temporary)
Formula I am using =IF( A2 < B2 , A2 ) and only works when number are positive (A1 returning false)
Also, I have a problem when the current variance is in the opposite direction of the estimate.
ie. (D1) we estimated we would go over budget by $1000 but currently we have a savings of $800, so I want to formula to show me the current savings as temporary and the permanent column as 0, since although we have a savings today, by year end that number will be an overage
I have tried using an absolute ref =if(abs..... around the numbers, but that throws off some of the other scenarios
I'll pulling my hair out here, any ideas? Maybe there are other functions I should use???? Any help i will take
Variance Estimate Permanent Temporary
a1 -800 -1000 -800 0
b1 800 1000 800 0
c1 800 -1000 0 800
d1 -800 1000 0 -800
Positive = overage
Negative = savings
I am trying to come up with a formula that takes Actual Varience against the actual estimated and tells me what portion of the actual variance is permanent or temporary
ie. (b1) we estimated we would go over budget $1000 and so far we have gone over budget $800, so i can assume the $800 is a permanent overage as it is less than the annual estimate but on the right track (result I want is $800 permanent, $0 temporary)
Formula I am using =IF( A2 < B2 , A2 ) and only works when number are positive (A1 returning false)
Also, I have a problem when the current variance is in the opposite direction of the estimate.
ie. (D1) we estimated we would go over budget by $1000 but currently we have a savings of $800, so I want to formula to show me the current savings as temporary and the permanent column as 0, since although we have a savings today, by year end that number will be an overage
I have tried using an absolute ref =if(abs..... around the numbers, but that throws off some of the other scenarios
I'll pulling my hair out here, any ideas? Maybe there are other functions I should use???? Any help i will take