If Formula with Negative Numbers

[larican]

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

 

[crimson_b1ade]

Book1
	A	B	C	D
1	actva	actest	perm	temp
2	-800	-1000	-800	0
3	800	1000	800	0
4	800	-1000	0	800
5	-800	1000	0	-800
Sheet1

 

[tusharm]

If the actual variance is in C and the estimate is in D starting with row 9, then in E9 the permanent value will be =IF(SIGN(C9)=SIGN(D9),SIGN(C9)*MIN(ABS(C9),ABS(D9)),0)
and the temporary value in F9 will be =IF(E9=0,C9,SIGN(C9)*MAX(0,ABS(C9)-ABS(D9)))

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