# Sum function from VLOOKUP results

#### r_worley

##### New Member
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

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.

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.

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:
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

Replies
1
Views
2K
Replies
0
Views
631
Replies
14
Views
1K
Replies
0
Views
722
Replies
2
Views
415

1,219,690
Messages
6,149,742
Members
450,911
Latest member
williamsisland

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