Excel Conundrum

shaz77_2

Hi All,

This follows on from an earlier query and I just can't find anything online that can resolve my problem.

In column A I have a list of dates in dd/mm/yyyy format and in column B I have percentages achieved. I need to display this in pivot format by month, what is the best way to do this?

There maybe several entries for each month etc and this is causing part of the confusion.

Thanks in advance

ThemeOne

If you reformat your dates to just show the month, you could then use the Subtotal feature to summarise monthly totals.

johnmc

If they are all the same year I would use another column to display just the month =text(A1,"mm").

If they are not then try =text(A1,"yyyymm") which will enable you to sort by year and then month.

You can then use a pivot table to sum / count etc. in the normal manner.

paddyrg

shaz77 wrote: »

Hi All,

This follows on from an earlier query and I just can't find anything online that can resolve my problem.

In column A I have a list of dates in dd/mm/yyyy format and in column B I have percentages achieved. I need to display this in pivot format by month, what is the best way to do this?

There maybe several entries for each month etc and this is causing part of the confusion.

Thanks in advance

It sounds easy right up until the fact column B are percentages, and that you have multiple values per month - you need to clarify how that works.

For instance
28/07/2013 10%
29/07/2013 15%

What do you want to show for that month? The sum 25% or the higher of the figures ie 15%

But this kind of thing is EXACTLY what pivot tables are for, so you're going in the right direction:-)