# 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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

What is C3?

#### Oaktree

##### MrExcel MVP
What is C3?

How could A3 be negative?

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

#### pgc01

##### MrExcel MVP
Hi rrmando

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

Hope this helps
PGC

#### rrmando

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

#### Oaktree

##### MrExcel MVP
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
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
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

#### rrmando

##### Board Regular
You da man pgc!

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

Replies
8
Views
489
Replies
4
Views
155
Replies
6
Views
175
Replies
10
Views
623
Replies
3
Views
130

### Forum statistics

1,171,064
Messages
5,873,564
Members
432,984
Latest member
WilMel ### 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.

### Which adblocker are you using?    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