Pivot Table Formula with fluctuating cell data

[Sunshine8790]

I have a pivot table where an additional column was added on for percentages for each row of data.
Below is the formula for the first 2 rows of data located in columns AG40 through AG48:

=AF40/$AF$49

=AF41/$AF$49

=AF42/$AF$49

=AF43/$AF$49

=AF44/$AF$49

=AF45/$AF$49

=AF46/$AF$49

=AF47/$AF$49

=AF48/$AF$49

Then there is the "Total" row, where the formula in AG49 (where all the above values are being divided by) is:

=SUM(AG40:AG48

The trouble is, the data is updated daily - and sometimes there is an extra row or 2 of data, sometimes a row or 2 less of data. Which means sometimes the "Total" line which is currently on row 49, ends up in row 50, or 48, or 51.

Is there a way to change these formulas to work with that kind of fluctuation so that I don't have to keep manually updating it as it changes?

 

[Alex Blakenburg]

You seem to be mixing 2 different approaches.
If you are using a pivot table you would use the "Show Values as > % of Column Total" and the pivot table handle both the Sum formula and the divide by the Total formulas.
The formulas you are using seem to be those you would use if you are NOT using a pivot table and in that case you would use an Excel Table which would automatically expand and contract with the number of lines and adjust the total and the percentage calculation you are using.

Please provide and XL2BB of your data.

 

[Sunshine8790]

You seem to be mixing 2 different approaches.
If you are using a pivot table you would use the "Show Values as > % of Column Total" and the pivot table handle both the Sum formula and the divide by the Total formulas.
The formulas you are using seem to be those you would use if you are NOT using a pivot table and in that case you would use an Excel Table which would automatically expand and contract with the number of lines and adjust the total and the percentage calculation you are using.

Please provide and XL2BB of your data.

Data below:

FormulaFixForSiteUpdate.xlsx
	AB	AC	AD	AE	AF	AG
39	Process	Unselected	Selected	Waved	Total	%
40	Auto-Bagger	1751		4138	5889	17.27%
41	Auto-Boxer Standard SPO			1821	1821	5.34%
42	BHLDN			83	83	0.24%
43	Non-Con	44		281	325	0.95%
44	Rebin	480		4528	5008	14.69%
45	Ship Alone	26			26	0.08%
46	SIO	152	279		431	1.26%
47	Sorter	3819		14467	18286	53.62%
48	SPO	173	62	1997	2232	6.55%
49	Total	6445	341	27315	34101	100.00%
Sheet2

Cell Formulas
Range	Formula
AG40:AG48	AG40	=AF40/$AF$49
AG49	AG49	=SUM(AG40:AG48)

 

[Alex Blakenburg]

How do you feel about creating a % of column total and then hiding columns AF to AH ?
PS: I am in Australia and it is 1am here so will log off soon.

20210905 Pivot PerCentages.xlsx
	AB	AC	AD	AE	AF	AG	AH	AI	AJ
37		Values	Attribute
38		Sum of Value			% of Value2			Total Sum of Value	Total % of Value2
39	Process	Selected	Unselected	Waved	Selected	Unselected	Waved
40	Auto-Bagger		1751	4138	0.00%	27.17%	15.15%	5889	17.27%
41	Auto-Boxer Standard SPO			1821	0.00%	0.00%	6.67%	1821	5.34%
42	BHLDN			83	0.00%	0.00%	0.30%	83	0.24%
43	Non-Con		44	281	0.00%	0.68%	1.03%	325	0.95%
44	Rebin		480	4528	0.00%	7.45%	16.58%	5008	14.69%
45	Ship Alone		26		0.00%	0.40%	0.00%	26	0.08%
46	SIO	279	152		81.82%	2.36%	0.00%	431	1.26%
47	Sorter		3819	14467	0.00%	59.26%	52.96%	18286	53.62%
48	SPO	62	173	1997	18.18%	2.68%	7.31%	2232	6.55%
49	Grand Total	341	6445	27315	100.00%	100.00%	100.00%	34101	100.00%
Pivot

 

[Sunshine8790]

How do you feel about creating a % of column total and then hiding columns AF to AH ?
PS: I am in Australia and it is 1am here so will log off soon.

20210905 Pivot PerCentages.xlsx
	AB	AC	AD	AE	AF	AG	AH	AI	AJ
37		Values	Attribute
38		Sum of Value			% of Value2			Total Sum of Value	Total % of Value2
39	Process	Selected	Unselected	Waved	Selected	Unselected	Waved
40	Auto-Bagger		1751	4138	0.00%	27.17%	15.15%	5889	17.27%
41	Auto-Boxer Standard SPO			1821	0.00%	0.00%	6.67%	1821	5.34%
42	BHLDN			83	0.00%	0.00%	0.30%	83	0.24%
43	Non-Con		44	281	0.00%	0.68%	1.03%	325	0.95%
44	Rebin		480	4528	0.00%	7.45%	16.58%	5008	14.69%
45	Ship Alone		26		0.00%	0.40%	0.00%	26	0.08%
46	SIO	279	152		81.82%	2.36%	0.00%	431	1.26%
47	Sorter		3819	14467	0.00%	59.26%	52.96%	18286	53.62%
48	SPO	62	173	1997	18.18%	2.68%	7.31%	2232	6.55%
49	Grand Total	341	6445	27315	100.00%	100.00%	100.00%	34101	100.00%
Pivot

View attachment 46279

Unable to hide any columns or rows as there are mulitple other pivot tables in the spreadsheet.

Also, I'm not seeing the options in my pivot field list that you screenshotted.

 

[Alex Blakenburg]

You simply need to drag the field you have in your values box into the values box a second time.

 

[Alex Blakenburg]

If you don’t want to hide columns but want the number of rows to expand / contract automatically you might need to use the Data Model and Power Pivot. Power Query may be an option as well.

 

[Sunshine8790]

If you don’t want to hide columns but want the number of rows to expand / contract automatically you might need to use the Data Model and Power Pivot. Power Query may be an option as well.

I'm not that advanced at excel to understand what you just suggested. I would need to be instructed step by step on how to do that.
Was really hoping that I could just do something simple within the formula equation itself.

 

[Alex Blakenburg]

OK give the below a try:
Perhaps create a new sheet, and copy the below to AB36.
The components are:

• The formula in AG40 needs to be copied to be longer than the most lines you are expecting the pivot table to return.
• In the formula
  

  =IF(AB40="","",IF(AB40<>"Total",AF40/GETPIVOTDATA("Value",$A$38),100%))

  highlight the part GETPIVOTDATA("Value",$A$38) a then click on the total in your pivot which in your post #3 picture was AF49.
  If this does not produce a GETPIVOTDATA formula come back to me and I will need to supply more detail.
• I have then applied conditional formatting to make the % line and the Total line Bold and the same color as the pivot.
  =OR(RIGHT(AB39,5)="Total",AB39="Process")

20210905 Pivot PerCentages.xlsx
	AB	AC	AD	AE	AF	AG
36
37
38	Sum of Value	Attribute
39	Process	Selected	Unselected	Waved	Total	%
40	Auto-Bagger		1751	4138	5889	17.27%
41	Auto-Boxer Standard SPO			1821	1821	5.34%
42	BHLDN			83	83	0.24%
43	Non-Con		44	281	325	0.95%
44	Rebin		480	4528	5008	14.69%
45	Ship Alone		26		26	0.08%
46	SIO	279	152		431	1.26%
47	Sorter		3819	14467	18286	53.62%
48	SPO	62	173	1997	2232	6.55%
49	Total	341	6445	27315	34101	100.00%
50
51
52
53
54
55
56
57
58
59
60
61
62
63
Reconstruct Data

Cell Formulas
Range	Formula
AG40:AG63	AG40	=IF(AB40="","",IF(AB40<>"Total",AF40/GETPIVOTDATA("Value",$A$38),100%))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG39:AG63	Expression	=OR(RIGHT(AB39,5)="Total",AB39="Process")	text	NO