Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table - deducting sub-totals of income and expenditure
In Excel 2007, I use pivot tables to analyse income and expenditure.
I would like to be able to deduct the expenditure from the income, but Excel seems to only provide an addition for Grand Totals, not the ability to deduct. Any suggestions gratefully received. Many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table - deducting sub-totals of income and expenditure
Hi,
Yes you are correct, what you can do is to have another column in your file with the difference and then you can include it in the Pivot table hope this helps "DRW" wrote: In Excel 2007, I use pivot tables to analyse income and expenditure. I would like to be able to deduct the expenditure from the income, but Excel seems to only provide an addition for Grand Totals, not the ability to deduct. Any suggestions gratefully received. Many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table - deducting sub-totals of income and expenditure
Thanks for the suggestion.
However, I don't think that would do it. If the income is sales invoices and the expenditure purchase invoices, then they don't necessarily match up individually - they only match up when aggregated. "Eduardo" wrote: Hi, Yes you are correct, what you can do is to have another column in your file with the difference and then you can include it in the Pivot table hope this helps "DRW" wrote: In Excel 2007, I use pivot tables to analyse income and expenditure. I would like to be able to deduct the expenditure from the income, but Excel seems to only provide an addition for Grand Totals, not the ability to deduct. Any suggestions gratefully received. Many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table - deducting sub-totals of income and expenditure
Hi
I would have a second Values column in my source table. Presumably, you have some way of identifying purchase rows from income rows. Let's assume that column C contains a an I for Income or P for Purchase. Let's also assume the existing Value column is column F so in the new Values column (Value2) =IF(C2="P",F2*-1,F2) Copy down Extend your PT source to contain this new Value2 column, and drag that to the data area in place of the existing Value column. -- Regards Roger Govier "DRW" wrote in message ... In Excel 2007, I use pivot tables to analyse income and expenditure. I would like to be able to deduct the expenditure from the income, but Excel seems to only provide an addition for Grand Totals, not the ability to deduct. Any suggestions gratefully received. Many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot table - deducting sub-totals of income and expenditure
Amazingly simple when you think about it!
Many thanks, Roger. Might it also be great if Excel did allow deductions as well as Sum?? "Roger Govier" wrote: Hi I would have a second Values column in my source table. Presumably, you have some way of identifying purchase rows from income rows. Let's assume that column C contains a an I for Income or P for Purchase. Let's also assume the existing Value column is column F so in the new Values column (Value2) =IF(C2="P",F2*-1,F2) Copy down Extend your PT source to contain this new Value2 column, and drag that to the data area in place of the existing Value column. -- Regards Roger Govier "DRW" wrote in message ... In Excel 2007, I use pivot tables to analyse income and expenditure. I would like to be able to deduct the expenditure from the income, but Excel seems to only provide an addition for Grand Totals, not the ability to deduct. Any suggestions gratefully received. Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set up an income and expenditure account in excel? | Excel Worksheet Functions | |||
I'm looking for an income expenditure budget sheet in excel please | Excel Discussion (Misc queries) | |||
Pivot Table Chart - With Pivot Table Totals | Charts and Charting in Excel | |||
personal income and expenditure chart | New Users to Excel | |||
is there a template for an income and expenditure statement in Ex. | Excel Discussion (Misc queries) |