Graphing the maximum and minimum values

[easymac]

Hi, everyone.

Let's say I have two columns. Column A has the first ten letters of the
alphabet, each listed 4 times (A1=A, A2=A, A3=A, A4=A, A5=B, A6=B, etc), and
in column B there are, let's say, random numbers between -10 and 10. I want
to create a chart that will graph the range using the maximum and minimum
values for each letter in column A. This would of course be a one-axis graph
(graphing only the values in B on the x-axis with the different values in A
being placed on top of (or to the side) of each other.

Any suggestions?

Thanks,
Bobby

 

[Bernard Liengme]

In D110 enter the text A, B, C, .... J
In E1 enter =MAX(IF(A:A=D1,B:B))-MIN(IF(A:A=D1,B:B))
but commit it with SHIFT+CTRL+ENTER as it is an array formula (Excel will
enclose it in braces { } )
Copy this down to E10
Make chart using D1:J10 for you data
best wishes

 

[ShaneDevenshire]

Hi,

If I understand you question correctly you want to display a single bar that
spans for the min to the max.

You can start with some of the previous suggestion:
Suppose you have you letters in A2:A12 and the values in B2:B12, then in
D24 enter you letters
A
B
C

in cell E2 array enter

=MAX(IF(A$2:A$12=D2,$B$2:$B$12))

in cell F2 array enter

=MIN(IF(A$2:A$12=D2,$B$2:$B$12))

Copy both these formulas down and then plot the data.

Now, double click the series on the chart and choose the Options tab, and
set the Overlap to 100

 

[easymac]

Bernard,

Thanks for the reply, but I seem to be encountering an error. I've done
exactly what you've said (I think) and in my E column I am getting "#NUM"
errors with Excel telling me it is a (duh) Number Error.

I'm on Excel 2003 if that makes a difference.

Also, I've been able to set up a Pivot Chart where it can graph for each
letter in A the min and max values, but it stacks them on top of each other.
I'd like for it to subtract them. Would that be a simpler problem to solve?

Thanks

 

[easymac]

Shane, your formulas were helpful and worked for finding the minimums and the
maximums, but I can get that with Pivot Tables anyway.

I'm stuck at the part where you say "plot the data" and then "choose the
Options tab, and set the Overlap to 100." When I plot the data, I have to
drag fields into the series area before I can double click on anything, and
from what I can tell, the overlap is already 100.

The best I can do is seem to get the axes and the series field to look like
what I want (listing a min-max range that is correct), but with nothing
actually showing up on the graph.

 

[Bernard Liengme]

It worked for me, so try again.
Make sure all B cells have numbers in them
Be sure to use shift+ctrl+enter
You may send me a file -- my private email, not the group -- if you wish;
just remove capital letters in my dummy address

best wish

 

[easymac]

Well like I said in my response to Shane's reply, it's not finding the min
and max values that is the problem, it's getting it to chart that shows only
the range. I am able to get close: stacking the two values on top of each
other, for example, but the only problem is that it sums the two values
rather than taking the difference.

I'm also able to line the minimum value and maximum value up side by side
per letter in a bar chart, which shows the difference more clearly, but I
would like it if I could remove those two bars and just have one bar that
graphs that range of difference.

 

[arshad malik]

Hi, Here in the Column A I have cluster names from 1 to 16 and all cluster names have their caps value in the column B, the caps values for all the clusters are repeated 3 to 9 times, for example lets take cluster 6, in row 2 cluster 6 is 313.48, in row 5 the value is 311.59 and in row 11 the value is 304.98, Now I want to capture the max value of Cluster 6 from all these given repeated values and similarly for all clusters, by the away i can do it alternately by auto filter but i dont want to follow this lengthy process for all clusters, I sincerely thank you for your help on this.