Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum sub levels.
Hi there,
My name is Jerome. I would appreciate your help with the following problem. I have an excel sheet whcih contains data displayed in various levels. There are 11 levels in total. There are over 5000 rows. However each level has different categories. I have to add the values of level 2 in level 1 and all the values of level 3 into 2 and so forth. See tteh format below. You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES, PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS) I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ASS, TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND, RENT ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS) LEVEL CODE AMOUNT 1 2 1 96623 PAYMENTS 2 982623 PAYMENTS 2 988623 TRANSFERS AND SUBSIDIES 2 996623 PAYMENTS FOR CAPITAL ASSETS 1 6318623 DIRECT EXCHEQUER PAYMENTS 2 6319623 DIRECT EXCHEQUER PAYMENTS 1 1623 RECEIPTS 2 2623 TAX RECEIPTS 2 3623 SALES GOODS & SERV NON CAP ASS 2 40623 TRANSFERS RECEIVED: 2 65623 FINES, PENALTIES AND FORFEITS 2 72623 INTEREST, DIVIDEND, RENT ON LAND 2 102623 SALES OF CAPITAL ASSETS I have to do this for the entire worksheet. Could you please advise me how I should go about this. Thank you very much!:O) Best regards, Jerome |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum sub levels.
Hi Jerome,
Was wondering why there really wasn't any data, then it occurred to me that this must be a class work problem. Take a look at Data, Subtotals that is from the menus. You can probably find a video tutorial at http://www.datapigtechnologies.com/ExcelMain.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jerome" wrote in message ... Hi there, My name is Jerome. I would appreciate your help with the following problem. I have an excel sheet whcih contains data displayed in various levels. There are 11 levels in total. There are over 5000 rows. However each level has different categories. I have to add the values of level 2 in level 1 and all the values of level 3 into 2 and so forth. See tteh format below. You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES, PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS) I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ASS, TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND, RENT ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS) LEVEL CODE AMOUNT 1 2 1 96623 PAYMENTS 2 982623 PAYMENTS 2 988623 TRANSFERS AND SUBSIDIES 2 996623 PAYMENTS FOR CAPITAL ASSETS 1 6318623 DIRECT EXCHEQUER PAYMENTS 2 6319623 DIRECT EXCHEQUER PAYMENTS 1 1623 RECEIPTS 2 2623 TAX RECEIPTS 2 3623 SALES GOODS & SERV NON CAP ASS 2 40623 TRANSFERS RECEIVED: 2 65623 FINES, PENALTIES AND FORFEITS 2 72623 INTEREST, DIVIDEND, RENT ON LAND 2 102623 SALES OF CAPITAL ASSETS I have to do this for the entire worksheet. Could you please advise me how I should go about this. Thank you very much!:O) Best regards, Jerome |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum sub levels.
Good morning David,
Thank you for your response. I'm afraid that won't work with my problem. I need something that calculates the sum for a particular level based on the amounts on the level immediately below it. However there are many descriptions or categories for a particular level ( each one has to be calculated individually) I need to add all the amounts (that column is not shown in my request) in each level (except level 1 of course) and place the total in the level immediately above it. Col 1 as Lvl (has levels 1 - 11) Col 2 as "Code" (Unique values) Col 3 as "Amount" (values will be entered in here) Col 4 as "Total" (This is where the formula will be entered) There are over 5000 rows. lvl code desc total amount 1 2 3 4 1 6333623 revenue fund receipts revenue fund receipts 2 6338623 debt portfolio debt portfolio 3 6351623 dbl pmt r150 settlemnt 21/7/00 dbl pmt r150 settlemnt 21/7/00 3 6351623 dbl pmt r150 settlemnt 21/7/00 dbl pmt r150 settlemnt 21/7/00 3 6350623 prem on debt protfolio restruct prem on debt protfolio restruct 3 6350623 prem on debt protfolio restruct prem on debt protfolio restruct 3 6349623 premium on issuance of bonds premium on issuance of bonds 3 6349623 premium on issuance of bonds premium on issuance of bonds 3 6348623 profit on conv of foreign loans profit on conv of foreign loans 3 6348623 profit on conv of foreign loans profit on conv of foreign loans 2 6335623 fines, penalties and forfeits fines, penalties and forfeits 3 6339623 penalties penalties 4 6341623 amnesty unit amnesty unit 4 6341623 amnesty unit amnesty unit 4 6340623 retail bonds retail bonds 4 6340623 retail bonds retail bonds Yes, i want to add the levles as two seperate numbers, however there are many different categories of levels for instance level 1 (Revenue) and level 1 (Direct Payments) etc ... each with their own sublevels. The sheet is structured so that the sublevels (which need to be added for the level above it) are immediately below the level above it. I sincerely hope this explains it more clearly "David McRitchie" wrote: Hi Jerome, Was wondering why there really wasn't any data, then it occurred to me that this must be a class work problem. Take a look at Data, Subtotals that is from the menus. You can probably find a video tutorial at http://www.datapigtechnologies.com/ExcelMain.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jerome" wrote in message ... Hi there, My name is Jerome. I would appreciate your help with the following problem. I have an excel sheet whcih contains data displayed in various levels. There are 11 levels in total. There are over 5000 rows. However each level has different categories. I have to add the values of level 2 in level 1 and all the values of level 3 into 2 and so forth. See tteh format below. You'll notice that I have to add (PAYMENTS, TRANSFERS AND SUBSIDIES, PAYMENTS FOR CAPITAL ASSETS - which are in level 2 ) in level 1 (PAYMENTS) I should do the same for (TAX RECEIPTS, SALES GOODS & SERV NON CAP ASS, TRANSFERS RECEIVED, FINES, PENALTIES AND FORFEITS, INTEREST, DIVIDEND, RENT ON LAND, SALES OF CAPITAL ASSETS) and add it in level 1 (RECEIPTS) LEVEL CODE AMOUNT 1 2 1 96623 PAYMENTS 2 982623 PAYMENTS 2 988623 TRANSFERS AND SUBSIDIES 2 996623 PAYMENTS FOR CAPITAL ASSETS 1 6318623 DIRECT EXCHEQUER PAYMENTS 2 6319623 DIRECT EXCHEQUER PAYMENTS 1 1623 RECEIPTS 2 2623 TAX RECEIPTS 2 3623 SALES GOODS & SERV NON CAP ASS 2 40623 TRANSFERS RECEIVED: 2 65623 FINES, PENALTIES AND FORFEITS 2 72623 INTEREST, DIVIDEND, RENT ON LAND 2 102623 SALES OF CAPITAL ASSETS I have to do this for the entire worksheet. Could you please advise me how I should go about this. Thank you very much!:O) Best regards, Jerome |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum sub levels.
Hi Jerome,
You would need to write a macro. Do you only have Level 1 and Level 2 You would have to generate formulas where you have 1 in column A with a macro if that is the case. The numbers are just random constants for test. =SUBTOTAL9,D2:D14) =SUBTOTAL(9,D3:D5) 8 37 52 =SUBTOTAL(9,D7:D7) 46 =SUBTOTAL(9,D9:D14) 76 11 81 69 61 83 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum sub levels. | Excel Discussion (Misc queries) | |||
sum sub levels. | New Users to Excel | |||
How to setup a nested if function with 10 levels? | Excel Worksheet Functions | |||
IF function with 14 nested levels | Excel Worksheet Functions | |||
Levels in Subtotals | Excel Worksheet Functions |