PDA

View Full Version : allocating the totals - to numbers equally



kfm2000
07-23-2014, 04:48 AM
I have a pivot table and the grand totals column location varies from day to day.

Looking for a formula what will calculate the grand totals equally in column A - to allocating the totals to numbers ranging from 1 to 8 deepening what has been entered in cell C1.

The number of rows will vary as well day by day.

I’ve attached an example spreadsheet.

Thanks

Bob Phillips
07-23-2014, 05:27 AM
Doesn't look much like a pivot table to me.

kfm2000
07-23-2014, 05:30 AM
the origanal table is.

mancubus
07-23-2014, 08:00 AM
not a pivot table.

2 possibilities, afaik: either a part of the pivot table is copied or the pivot table is copied and pasted as values.

kfm2000
07-23-2014, 08:25 AM
the pivot table has been copied and pasted as values.

kfm2000
07-24-2014, 01:00 AM
did you want me to upload a pivot table for use?

mancubus
07-24-2014, 01:11 AM
:)

pivot table and source data. you can replace the sensitive information, if any, with fake data.

kfm2000
07-24-2014, 02:56 AM
ok here's the pivot table and source data

mancubus
07-24-2014, 11:34 PM
i don't know if i can help but can you clarify your requirement?
'calculate grand totals equally' means?


you have numbers in A5-A30.
do you want to return these numbers via farmulae using the value in C1?

if so how will it be done (the logic i mean)?
which pivot values will be used?
any other info?

kfm2000
07-25-2014, 05:31 AM
I need to be able to make sure that the classes are evenly balanced in numbers. To do this currently I have created a formula which adds up the classes and divides them by the figure shown in C5, but to make sure this works I have to manually type in a corresponding number in column A ie. 1 or 2 etc. (as shown in the Before and After pivot tables) on the attached spreadsheet.

I would be grateful if you could create a formula that would do all this automatically.

mancubus
07-25-2014, 07:48 AM
for the pivot in your workbook, you are trying to find 26 numbers between 1-8 that sum up to a certain number, which is returned by =INT(GETPIVOTDATA("DateAccredited(Date)",$B$3)/$C$1) formula.

is that right?

if so, though i'm not sure, below links may help.
http://answers.microsoft.com/en-us/office/forum/office_2007-excel/add-up-all-combinations/56ea283d-5671-4763-abb2-46a3abbceebf
http://www.tushar-mehta.com/excel/templates/match_values/

kfm2000
07-28-2014, 01:59 AM
no not quite,

OK lets me start again.

when i create the pivot table (before pivot tab) the grand total is 466
the user will select 1 number between 1 and 8 in cell c1

in this example I've select 5 in cell C1, what I'm looking for is a formula or vba code that will place 1 number in cells a5 to a30 depend how many i've select in cell C2

for each number placed in the cells (a5 to a30) I want to distribute the totals equally in this case approx. 93

see the after pivot table for an example.

i hope this makes sense... help!!!