Sum function from VLOOKUP results

r_worley

New Member
Joined
Mar 16, 2009
Messages
3
I'd like to have my VLOOKUP function work with a sum function, but do not know how to formulate. Example: Calculating Previous Depreciation

YEAR OF LIFE_DEPREC METHOD__USEFUL LIFE__COST__PREV DEPREC_
3_____________MACR 5___________5________ 4,060_____ 1,949

The lower table represents rates of depreciation each year whic corresponds to the year of life an asset is in.

MACR 5
rate
1 .20000
2 .32000
3 .19200
4 .11520
5 .11520
6 .05760

I'd like Excel to use a VLOOKUP function to find the rates according to 'Year of Life' and then add each year's function of (cost * rate) up to the appropriate year. The formula must 'Year of Life' minus 1 to get 'Previous Deprec' Thanks for your help.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi and welcome to MrExcel!

Before anyone answers this question, I think we need to know two additional things:
1) How did you arrive at the value 1,949?
2) I presume there are depreciation methods other than 'MACR5'?

Andrew
 

r_worley

New Member
Joined
Mar 16, 2009
Messages
3
The number is incorrect, I apologize. Instead of $1,949, it s/b $2,111. I've inherited the worksheet and am still checking everything. That amount is from multiplying cost by the applicable table rates for each year I need depreciation for and summing the results. Year one depreciation is $812 (4,060*.20000), Year two is $1,299 (4,060*.32000), etc. Thanks.
 

r_worley

New Member
Joined
Mar 16, 2009
Messages
3
There are additional depreciation methods. I primarily use tables for 3-year, 5-year, 7-year, 15-year and 20-year, but all have tables like the one in the example.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

Start by setting up a table of depreciation rates like this:

Code:
   K                    L       M      N      O      
 1 Depreciation Methods                              
 2 Year                 MACR1   MACR2  MACR5  MACR9  
 3 0                    0.00%   0.00%  0.00%  0.00%  
 4 1                    100.00% 67.00% 20.00% 10.00% 
 5 2                            33.00% 32.00% 10.00% 
 6 3                                   19.20% 10.00% 
 7 4                                   11.52% 10.00% 
 8 5                                   11.52% 10.00% 
 9 6                                   5.76%  10.00% 
10 7                                          10.00% 
11 8                                          10.00% 
12 9                                          20.00% 
13 10                                                
Sheet1
[Table-It] version 09 by Erik Van Geit

I used the range K1:O13 on sheet1. If you use a different range then you will need to change the following formula (see my next post). Modify the headings and rates and the size of the table to suit the methods you actually use.

Andrew
 
Last edited:

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Then I used the following layout and test data. Note the array formula for the 'Prev Depn' column - there are instructions on how to use that below.

Code:
  A     B            C      D    E     F         G         
1 Asset Year of Life Method Life Cost  Prev Depn Curr Depn 
2 1     3            MACR5  5    4060  2111      780       
3 2     1            MACR1  1    2000  0         2000      
4 3     2            MACR1  1    1000  1000      0         
5 4     8            MACR9  9    10000 7000      1000      
Sheet1
[Table-It] version 09 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
F2:F5 {=ROUND(E2*SUM(IF(C2=$L$2:$O$2,IF(B2>$K$3:$K$13,$L$3:$O$13,0),0)),0)}
G2:G5 =ROUND(E2*INDEX($L$3:$O$13,B2+1,MATCH(C2,$L$2:$O$2,0)),0)
{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down
[Table-It] version 09 by Erik Van Geit

Cheers
Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,122,498
Messages
5,596,509
Members
414,073
Latest member
Contilly

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