A
allie357
The first spreadsheet shows departments and their spending
The second spreadsheet calculates total spend by departments over 100k
and some percentages.
I need to be able to count and sum how many transactions there are per
department over 100k. Is there a formula and/or a way to do this with
a pivot table and a formula? I need it to look like the format in the
second spreadsheet. My current formulas are as follows
Column B =SUM(G2:G164) in a hidden column to calculate the number of
contracts over 100k
Column C =B2/$G$912 to calculate % of total
Column D running total for cummulative percentages (formula)
Column I =SUM(H3:H66) in a hidden column to calculate the total spent
per department. There is a separate sum of each department. Is there
an easier way to calculate this?
Column J =I2/$I$914 to calculate % of total
Column B =SUM(G2:G164) to calculate the number of contracts over 100k
Column C =B2/$G$912 to calculate % of total
Column D running total for cummulative percentages (formula)
Column I =SUM(H3:H66) to calculate the total spent per department
Column J =I2/$I$914 to calculate % of total
Column K running total for cummulative percentages (formula)
Is there an easier way to do this with formulas and or pivot tables?
Any help is appreciated. I am sort of new to pivot tables. If this
can be done easier with a pivot table, I would like to know. I am
also using excel 2007, by the way. Thanks in advance.
The second spreadsheet calculates total spend by departments over 100k
and some percentages.
I need to be able to count and sum how many transactions there are per
department over 100k. Is there a formula and/or a way to do this with
a pivot table and a formula? I need it to look like the format in the
second spreadsheet. My current formulas are as follows
Column B =SUM(G2:G164) in a hidden column to calculate the number of
contracts over 100k
Column C =B2/$G$912 to calculate % of total
Column D running total for cummulative percentages (formula)
Column I =SUM(H3:H66) in a hidden column to calculate the total spent
per department. There is a separate sum of each department. Is there
an easier way to calculate this?
Column J =I2/$I$914 to calculate % of total
Column B =SUM(G2:G164) to calculate the number of contracts over 100k
Column C =B2/$G$912 to calculate % of total
Column D running total for cummulative percentages (formula)
Column I =SUM(H3:H66) to calculate the total spent per department
Column J =I2/$I$914 to calculate % of total
Column K running total for cummulative percentages (formula)
Is there an easier way to do this with formulas and or pivot tables?
Any help is appreciated. I am sort of new to pivot tables. If this
can be done easier with a pivot table, I would like to know. I am
also using excel 2007, by the way. Thanks in advance.