Nested If function with restricting formula result

[pambard]

I have purchase date (A), cost (B), depreciation rate (C), accumulated depreciation (D), in columns A to D and months in columns E onwards. I have used nested "if" function: =ROUND(IF((AND(D2<B2,A2<=$E$1)),B2*C2/12,0),2) to calculate depreciation for each month (in adjucent columns). I want to restrict the depreciation calculated as a result of the formula not to exceed accumulated depreciation. I am unable to do it. Any suggestion is highly appreciated.

Thank you.

 

[jproffer]

I think your post cut off

 

[pambard]

I have purchase date (A), cost (B), depreciation rate (C), accumulated depreciation (D), in columns A to D and months in columns E onwards. I have used nested "if" function: =ROUND(IF((AND(D2<B2,A2<=$E$1)),B2*C2/12,0),2) to calculate depreciation for each month (in adjucent columns). I want to restrict the depreciation calculated as a result of the formula not to exceed accumulated depreciation. I am unable to do it. Any suggestion is highly appreciated. (Excel 2007)

 

[taurean]

There will be these signs in the formula "<" and ">" so the trick is to post the formula with added spaces before and after these characters.

See my signature!

 

[pambard]

I have purchase date (A), cost (B), depreciation rate (C), accumulated depreciation (D), in columns A to D and months in columns E onwards. I have used nested "if" function: =ROUND(IF((AND(D2 < B2,A2 < = $E$1)),B2*C2/12,0),2) to calculate depreciation for each month (in adjucent columns). I want to restrict the depreciation calculated as a result of the formula not to exceed accumulated depreciation. I am unable to do it. Any suggestion is highly appreciated.

 

[taurean]

Does it mean:
Cumulative total of depreciation calculated in Column E onwards shall not exceed the total in D2?

You can post a sample data using Excel Jeanie and expected results so that it is easier to understand the situation.

 

[pambard]

Does it mean:
Cumulative total of depreciation calculated in Column E onwards shall not exceed the total in D2?

You can post a sample data using Excel Jeanie and expected results so that it is easier to understand the situation.

Thanks Taurean, here is a sample.

Excel Workbook
	A	B	C	D	E
1	Purchase date	Cost	Depre rate	Accumulated depre	Apr-11
2	13-Mar-10	* 40,609.00	25%	* 40,608.00	* * 846.02
3	Result in E2 should not exceed D2	*	*	*	*
Sheet1

 

[taurean]

I have still not understood your requirement. The layout is now clear to me.

In the case shown E2 is not exceeding D2? Or have I understood it wrong?

 

[pambard]

I apologize for not properly forming my question.

Accumulated depreciation is the beginning balance to which monthly depreciation will be added. So the accumulated depreciation plus month's depreciation should not exceed total cost. In this case, column E should return only $ 1 and not $ 846.02 which the formula returns. The depreciation then will be $ 40609.00 (column D + column E).

At any point, the result of the formula if added to the accumulated depreciation should not exceed total purchase cost in column B.

Hope it is clear.

Thanks.

 

[taurean]

I apologize for not properly forming my question.

Accumulated depreciation is the beginning balance to which monthly depreciation will be added. So the accumulated depreciation plus month's depreciation should not exceed total cost. In this case, column E should return only $ 1 and not $ 846.02 which the formula returns. The depreciation then will be $ 40609.00 (column D + column E).

At any point, the result of the formula if added to the accumulated depreciation should not exceed total purchase cost in column B.

Hope it is clear.

Thanks.

OK, now it is clear to me. This should work as intended.
=ROUND(IF((AND(D2 < B2,A2 < =$E$1)),IF((B2*C2/12) > (B2-D2),B2-D2,B2*C2/12),0),2)
Note: Remove spaces in the above formula