-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Can this depreciation formula be streamlined?
- Thread starter rrmando
- Start date
Oaktree, B3 is not referenced in the formula. I just included it because C3 is A3/B3. I guess I shouldn't have.
Pgc, you formula works great. Can you indulge me with an explanation? I see array and SIGN function, but don't understant the logic. Thank you as always.
Pgc, you formula works great. Can you indulge me with an explanation? I see array and SIGN function, but don't understant the logic. Thank you as always.
Upvote
0
Hi rrmando
I'm glad it works as you wanted.
This was the logic: The 2 last conditons of your formula are:
IF A3 < 0 and A3 - C3 < 0 then A3 - C3 else 0
IF A3 > 0 and A3 - C3 > 0 then A3 - C3 else 0
They are very similar. You can write them in a way that they are almost the same:
IF A3 < 0 and (-1)*(A3 - C3) > 0 then A3 - C3 else 0
IF A3 > 0 and (+1)*A3 - C3 > 0 then A3 - C3 else 0
The number multiplying (A3-C3) in the condition in both cases is the sign of A3. So the value of the expression is, for both A3<0 and A3 >0:
(SIGN(A3)*(A3-C3)>0) * (A3-C3)
As you know, excel converts true to 1 and false to 0 in an arithmetic expressions. So if the condition is met its value is 1 and you have the result (A3-C3) . If the condition is not met, its value is 0 and you have the result 0.
Now we look at the third condition: If A3 = 0 then result 0. Usually to include that condition you would just have to multiply by (A3<>0).
However in this case this would be redundant. In fact if A3=0 then SIGN(A3)=0 then the condition is false and you are multiplying 0*(A3-C3) which is already 0.
So the last expression encompasses all three conditions.
I hope it was clear.
PGC
I'm glad it works as you wanted.
This was the logic: The 2 last conditons of your formula are:
IF A3 < 0 and A3 - C3 < 0 then A3 - C3 else 0
IF A3 > 0 and A3 - C3 > 0 then A3 - C3 else 0
They are very similar. You can write them in a way that they are almost the same:
IF A3 < 0 and (-1)*(A3 - C3) > 0 then A3 - C3 else 0
IF A3 > 0 and (+1)*A3 - C3 > 0 then A3 - C3 else 0
The number multiplying (A3-C3) in the condition in both cases is the sign of A3. So the value of the expression is, for both A3<0 and A3 >0:
(SIGN(A3)*(A3-C3)>0) * (A3-C3)
As you know, excel converts true to 1 and false to 0 in an arithmetic expressions. So if the condition is met its value is 1 and you have the result (A3-C3) . If the condition is not met, its value is 0 and you have the result 0.
Now we look at the third condition: If A3 = 0 then result 0. Usually to include that condition you would just have to multiply by (A3<>0).
However in this case this would be redundant. In fact if A3=0 then SIGN(A3)=0 then the condition is false and you are multiplying 0*(A3-C3) which is already 0.
So the last expression encompasses all three conditions.
I hope it was clear.
PGC
Upvote
0
Similar threads
- Solved
- Solved
