Creating 0.45 Power Chart

[jondavis1987]

I can't provide specifics because I don't even understand what I'm trying to create really. Does anyone know how to create a .45 Power Chart? I'm trying to plot a gradation on a .45 chart. I have converted the square opening of the sieves from inches to microns. I have a different column that takes those microns to the .45 power. After that, i don't know what i'm doing.

 

[thisoldman]

Set up the data something like this:

Book1
	B	C	D	E	F
2	Sieve		^45	% Passing	Line
3	1-1/2 in	38100	115.1904	100	#N/A
4	1 in	25400	95.97877	97	100
5	3/4 in	19000	84.22463	87	#N/A
6	1/2 in	12700	70.26057	68	#N/A
7	3/8 in	9510	61.68524	59	#N/A
8	No. 4	4760	45.1776	40	#N/A
9	No. 8	2380	33.07194	30	#N/A
10	No. 16	1190	24.21007	24	#N/A
11	No. 30	595	17.72281	19	#N/A
12	No. 50	297	12.96404	8	#N/A
13	No. 100	149	9.504599	4	#N/A
14	No. 200	74	6.936722	3.1	#N/A
15	Pan	0	0	0	0
Sheet1

The Line column provides the straight line on the chart. It has two points: 100% passing at the maximum sieve opening, and zero at the intercept.

Select the rightmost three columns then, from the ribbon, select Insert > Scatter chart > Line with Markers.

The straight line was formatted to be a dashed line with no markers. If your chart does not connect the points for this series, with the chart selected go to the Design tab > Select Data. Click on the button labeled "Hidden and Empty Cells". Select "Connect data points with line" and OK your way out.

After some formatting, I end up with this:

With this second chart, I removed the x-axis and the vertical gridlines. I added a dummy series with no marker and no lines and used vertical error bars and data labels to show gridlines for the sieve sizes used.

My example spreadsheet can be downloaded from https://www.dropbox.com/s/ofk9064puut58yj/gradation_power_chart.xlsx?dl=0

 

[jondavis1987]

You are a life saver! Thank you very much!

 

[thisoldman]

You're welcome. I'm happy to have helped.

 

[Jojee]

I am having trouble creating the second chart. I followed your intructions but I do not know how to do the gridline in error vertical bars. Please help.

 

[jondavis1987]

Go to format the chart. Go to the dummy series. Go to the error bar options for it. Click custom. Do 100% for positive and 0 for negative