Help please- pivot table

  • Thread starter Thread starter opeyemi1
  • Start date Start date
O

opeyemi1

Hello everyone:
I really need someone to help me with the following:

I have a Pivot table question. Is there a way that I can find th
average from the Grand total column without having to do it manually?
I have Sum of Current Due in the Data field, Bill Month in the Colum
field and Bill Day in the Row field. I want to find the average of th
Current Due based on the number of months selected, i.e. Grand Total o
Current Due/ # of Months selected.

Can anyone please tell me how to do this because having to do i
manually is a real pain since my data keeps expanding.

Thank you very much in advance.


Kem
 
You can right click in the data field and select average, then right click
in the bill day field
and select group and show detail, select group and do it by month. I would
get rid of Bill Month
altogether and just use the group, that way you can select and unselect the
months from the dropdown
and the layout is more slick.
 
Hello Peo:

Thanks for your reply, I have taken out the month as advised, but d
you might going a little slower.
When I right-clicked on my pivot table there is no 'select average
option there for me to click on, also when I right-clicked on the bil
day field in my pt and clicked on select group and show detail, then o
select group, I get the message "Cannot group that selection".
The PT is presently structured as: Bill Day in Row field, Analyst i
column field and Current Due in Data field.

Thank you very much for your patience.


Kem
 
Right click somewhere in the pivot and select wizard, click the layout and
double click
the sum of button that is in the data field, from there you should be able
to select average.
It opens the same pop up as right click and field settings..
Now are the dates real dates and not text? I created a small table with
dates in one column and
amounts in one, then I dragged the dates to the row field and the amounts to
the data field.
And it works fine, I can group by days, weeks (7 days), months, quarters and
years
 
Hello Peo:

Thanks for your reply, I have taken out the month as advised, but d
you might going a little slower.
When I right-clicked on my pivot table there is no 'select average
option there for me to click on, also when I right-clicked on the bil
day field in my pt and clicked on select group and show detail, then o
select group, I get the message "Cannot group that selection".
The PT is presently structured as: Bill Day in Row field, Analyst i
column field and Current Due in Data field.

Thank you very much for your patience.


Kem
 
Blanks in the data could also have the same effect.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Peo Sjoblom said:
Right click somewhere in the pivot and select wizard, click the layout and
double click
the sum of button that is in the data field, from there you should be able
to select average.
It opens the same pop up as right click and field settings..
Now are the dates real dates and not text? I created a small table with
dates in one column and
amounts in one, then I dragged the dates to the row field and the amounts to
the data field.
And it works fine, I can group by days, weeks (7 days), months, quarters and
years
Click to expand...
 
Correct, thanks for the heads up..

--

Regards,

Peo Sjoblom

Ken Wright said:
Blanks in the data could also have the same effect.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :-)
-------------------------------------------------------------------------- --
Click to expand...
 
Opeyemi - I almost ALWAYS want my data to be Summed and not counted, and almost
ALWAYS want to be able to group date fields. Now whilst I don't think date
fields shouldn't have any blanks anyway, the numeric data often does. In this
case I would normally select the entire range of numeric source data, do Edit /
Go To / Special / Blanks then type 0 and hit CTRL+ENTER which will put a 0 in
every cell that had contained a blank. This means that all your fields will
default to a SUM and not a COUNT.

If by any chance your source data has blanks in the dates where the date is
supposed to be the same as the last non-blank cell (ie as a lot of VM systems
tend to spit out their reports), eg:-

A B C D
23-12-2003 23 45 65
22 87 52
15 45 74
24-12-2003 23 25 31
18 48 47
25-12-2003 20 24 25
19 34 36
12 85 75
26-12-2003 15 65 98
12 25 24

then you can select the entire range of dates, do Edit / Go To Special / Blanks,
then type = and hit the UP ARROW once and then hit CTRL+ENTER. This will fill
in any blanks in the dates with the date from above. then select the entire
column and copy and paste special as values to get rid of the formulas.
 
LOL - Peo, I can honestly say now that I have no idea how I could have gotten
through the last few proposals we have submitted without Pivot Tables. I feel
like I am totally addicted, and would go into a cold sweat at the thought of
having to do a proposal without them. When you think back to when it was all
done with pencil and paper and calculators, it is just mind-blowing as to how
much analysis you can now do in literally just a few seconds, that back then
would have taken an army of people, weeks to accomplish. Scary stuff :-)
 
Back
Top