Can this depreciation formula be streamlined?

rrmando

Board Regular
Hello. Can the following formula be streamlined? It's for depreciation where A3 = purchase amount. Thank you.

Code:
``=IF(\$A3=0,0,IF(\$A3<0,IF(\$A3-\$C3>0,0,(\$A3-\$C3)),IF(\$A3-\$C3<0,0,(\$A3-\$C3))))``

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What is C3?

What is C3?

How could A3 be negative?

Maybe just =MAX(0,A3-C3) ?

Hi rrmando

= (SIGN(A3)*(A3-C3)>0)*(A3-C3)

Hope this helps
PGC

Sorry about that. A3 = purchase amount, B3 = # of months to depreciate, C3 = monthly depreciation (A3/B3). Thanks again for your help.

Hmm. Still not clear (at least to me)...

What is this formula supposed to be calculating? The formula you posted does not include references to B3.

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.

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

You da man pgc!

The eye sees only what the mind is prepared to comprehend.

Replies
7
Views
146
Replies
4
Views
219
Replies
0
Views
125
Replies
2
Views
195
Replies
8
Views
292

1,218,541
Messages
6,143,102
Members
450,463
Latest member
DaveG999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back