Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Subtotal
Hi All,
I have a list of cables and with beginning total (length) of each one. throughout the year, users are filling rows with type of cable and the amount they took or received to the warehouse. (Headers: Cable Type,Income Amount,Outcome Amount). I need to calculate the balance between the received and taken so it will be dynamically. I can use Subtotal but I will need to do it all the time over and over again. Is there another solution that doesn't concern VBA? Thanks a lot in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Subtotal
There are a couple of ways to do this. One is to set your total at the
top of the spreadsheet, and have it total a large number of rows. Example, your "This transaction" positive or negative number might be in column D, and the total number of all those adds and subtracts might be in cell D3. The formula in D3 might be something like =SUM(D5:D5000), covering a long list of rows. Another way to do this is to add a "net total" column to each row, and set the formula to add "this transaction" to the "net total" from the previous entry. Continuing the previous example, if "this transaction" appears in col D the Net total column might be E. On each row the Net Total formula adds the Net Total from the previous row to column D on the current row. Each method has its pitfalls: in method 1 it's possible that the formula might cover 5000 rows but the users enter 6000 rows, and the total will be wrong unless the file is frequently inspected and maintained. The users might use blank rows to perform other calculations and unknowingly use column D, thus skewing the true results. (This happened to me recently.) Method 2 is more precise but because the number of transactions varies and the location of the current final total changes, it's difficult to keep a summary page up to date and accurate. Dave O Eschew obfuscation |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Subtotal
Or use Pivot Table.
Add a Calculated Item to the PT columns, named Net, with this formula: =In-Out The result might look like this: Row In Out Net W1 200 20 180 W2 600 500 100 W3 350 114 236 W4 120 32 88 W5 450 35 415 W6 350 350 W7 330 83 247 W8 400 399 1 Hit Refresh to update. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Subtotal, then Sort by Subtotal Amounts? | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
copy subtotal value only, subtotal value can be vlookup by others | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |