Apply A Formula to Increments of a Value

[anborroms]

Hello

In my spreadsheet below, I am trying to calculate the Depreciation to Date of the items in column A. Each item has a number of completed months (ie. the number of moths the item has been used) and the expected yearly depreciation amount. So for example, item A has 15 months of completed months (ie. it has been used for 15 months already) and its life is 11 years with expected depreciation of 264.81 in Year 1, 249.68 of depreciation in Year 2, etc.

Is it possible to create a formula so that the total depreciation is calculated based on the number of completed months and the corresponding yearly depreciation?

So for example, for Item A, for the first 12 mths, its depreciation is 264.81; and for the remaining 3 months its depreciation is 249.68/12 * 3 = 64.42 . Therefore its total depreciation for 15 mths is 264.81 + 64.42 = 327.23 .

Any help would be much appreciated.

 

[Cubist]

Try this for 365. (I'm not sure why "Prefer not to say" is an option for XL version).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Item	# Completed Months	Depreciation to Date				Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Year 11
3	A	15	327.23				264.81	249.68	199.74	159.79	127.87	102.29	99.11	99.11	99.27	99.11	12.41
4
Sheet1

Cell Formulas
Range	Formula
C3	C3	=LET(q,QUOTIENT(B3,12),SUM(TAKE(G3:Q3,,q),MOD(B3,12)*INDEX(G3:Q3,,q+1)/12))

 

[anborroms]

Try this for 365. (I'm not sure why "Prefer not to say" is an option for XL version).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Item	# Completed Months	Depreciation to Date				Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Year 11
3	A	15	327.23				264.81	249.68	199.74	159.79	127.87	102.29	99.11	99.11	99.27	99.11	12.41
4
Sheet1

Cell Formulas
Range	Formula
C3	C3	=LET(q,QUOTIENT(B3,12),SUM(TAKE(G3:Q3,,q),MOD(B3,12)*INDEX(G3:Q3,,q+1)/12))

Thank you Cubist. When I copied down the formula I am getting a #CALC! error, I believe due to an empty array. I am not really sure how to resolve.

 

[Cubist]

Can you show what is on your sheet?

 

[anborroms]

Try this for 365. (I'm not sure why "Prefer not to say" is an option for XL version).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Item	# Completed Months	Depreciation to Date				Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Year 11
3	A	15	327.23				264.81	249.68	199.74	159.79	127.87	102.29	99.11	99.11	99.27	99.11	12.41
4
Sheet1

Cell Formulas
Range	Formula
C3	C3	=LET(q,QUOTIENT(B3,12),SUM(TAKE(G3:Q3,,q),MOD(B3,12)*INDEX(G3:Q3,,q+1)/12))

This is what I see:

 

[Cubist]

Try this.

=LET(q,QUOTIENT(B3,12),SUM(IFERROR(TAKE(G3:Q3,,q),0),MOD(B3,12)*INDEX(G3:Q3,,q+1)/12))

 

[anborroms]

=LET(q,QUOTIENT(B3,12),SUM(IFERROR(TAKE(G3:Q3,,q),0),MOD(B3,12)*INDEX(G3:Q3,,q+1)/12))

That worked perfectly! Thank you so much.

 

[Cubist]

You're welcome. Note that C3:Q3 needs to be the largest column range for all of your items when filling down, otherwise, you'll miss out some years. Blank cells are OK.