Can this depreciation formula be streamlined?

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
What is C3?

How could A3 be negative?

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi rrmando

Please try:

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

Hope this helps
PGC
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212

ADVERTISEMENT

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

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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.
 

rrmando

Board Regular
Joined
Dec 23, 2004
Messages
212

ADVERTISEMENT

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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

Forum statistics

Threads
1,136,712
Messages
5,677,335
Members
419,688
Latest member
sarahmichelle

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top