Thread: How to Chart the Maximum Point within Two Loops

I have the following dataset. For each person in Column A, I want to
find their maximum sales $ in Column B for each of the location in
Column C and plot them out in a bar chart. There will be always two
locations (series) for each person.

A B C D
1 Faye 14,874 China 7/4/2005
2 Faye 18,050 China 6/27/2005
3 Faye 31,255 US 6/20/2005
4 Faye 56,351 US 6/13/2005
5 Faye 59,352 US 6/6/2005
6 Faye 74,887 US 5/30/2005
7 Jean 42,671 China 5/23/2005
8 Jean 45,560 China 5/16/2005
9 Jean 60,170 China 5/9/2005
10 Jean 100,357 US 5/2/2005
11 Jean 112,163 US 4/25/2005
12 Jeff 18,078 China 4/18/2005
13 Jeff 26,859 China 4/11/2005
14 Jeff 49,090 US 4/4/2005
15 Jeff 74,579 US 3/28/2005
....
....

Thanks for any input. I appreciate it.

Faye Larson

You can use the PivotTable and PivotChart report, and layout the report with
the columns A and C in the rows, and the Max of column B in the data. That
will make all the calculations for you.

Hope this helps,
Miguel.

"Faye" wrote:

> I have the following dataset. For each person in Column A, I want to
> find their maximum sales $ in Column B for each of the location in
> Column C and plot them out in a bar chart. There will be always two
> locations (series) for each person.
>
> A B C D
> 1 Faye 14,874 China 7/4/2005
> 2 Faye 18,050 China 6/27/2005
> 3 Faye 31,255 US 6/20/2005
> 4 Faye 56,351 US 6/13/2005
> 5 Faye 59,352 US 6/6/2005
> 6 Faye 74,887 US 5/30/2005
> 7 Jean 42,671 China 5/23/2005
> 8 Jean 45,560 China 5/16/2005
> 9 Jean 60,170 China 5/9/2005
> 10 Jean 100,357 US 5/2/2005
> 11 Jean 112,163 US 4/25/2005
> 12 Jeff 18,078 China 4/18/2005
> 13 Jeff 26,859 China 4/11/2005
> 14 Jeff 49,090 US 4/4/2005
> 15 Jeff 74,579 US 3/28/2005
> ....
> ....
>
> Thanks for any input. I appreciate it.
>
> Faye Larson
>
>

How can I include the column D in the pivat table?

Faye China 18050 6/27/2005
US 74887 5/30/2005
Jean China 60170 5/9/2005
US 112163 4/25/2005
Jeff China 26859 ...
US 74579 ...
....
....

Thanks, again.

Faye Larson

Good question, the pivot table won't allow to put the date directly on the
data section, and droping in any other area will distort the table.
I don't know how to do that in an easy way, sorry. Also, because it comes
from a pivot table, you cannot play too much with the source data options, so
if having the date is a must then the pivot chart may not be the correct
solution for your case.

Miguel.

"Faye" wrote:

> How can I include the column D in the pivat table?
>
> Faye China 18050 6/27/2005
> US 74887 5/30/2005
> Jean China 60170 5/9/2005
> US 112163 4/25/2005
> Jeff China 26859 ...
> US 74579 ...
> ....
> ....
>
> Thanks, again.
>
> Faye Larson
>
>