N1.85 Graph

[Rick_Frye]

Hello,

Trying to create an N1.85 graph in Excel that has irregular spaced tick marks on the X-axis. Found the following information but no luck with it. Need US measure for this semi-log (10 X N1.85) graph. Also called a semi-expo (Q1.85) graph.

The resulting graph appears to be a log graph in reverse with one scale; the column widths are smaller at the left and become larger as they progress to the right. Any help is appreciated.

Thanks.

Info found:

A 1.85 graph can be constructed manually by establishing a series of 15 values (in the case of the example in D5.2.1) from a base measurement to the exponent of 1.85.

Step 1
Select a base measurement for the desired size of the graph. A base measurement of 1.0 mm will produce a graph to 15 which is approximately 150 mm wide; a base measurement of 1.5 mm will produce a graph approximately 300 mm wide. In the case of a 1 mm base measurement, the x-axis numbers will be the 1–15 series. In the case of a base of 1.5 mm, the numbers will be represented by the series: 1.5, 3.0, 4.5, 6.0 etc. for 15 values.

Step 2
Construct a series of columns to the 1.85 exponent values measured from the zero point. The rows representing the pressure values are linear.

NOTE – A good approximation of the above can be computer-generated by a spreadsheet programme by entering a column width established from the exponential figures by subtracting the preceding value in each case. The column dimensions are displayed in the number of standard characters able to be accommodated in the column width which is slightly inaccurate in linear dimension.

The figures below indicate the values for a graph based on 1.0 mm.

Linear scale Exponential value of linear values = Column width =
linear values to 1.85 power exponential value – preceding value

1 1 1
2 3.61 2.61
3 7.63 4.03
4 13.00 5.36
5 19.64 6.64
6 27.52 7.88
7 36.60 9.08
8 46.85 10.25
9 58.26 11.41
10 70.79 12.54
11 84.45 13.65
12 99.19 14.75
13 115.03 15.83
14 131.93 16.90
15 149.89 17.96

 

[tusharm]

Add a column containing all zeros. Then, create a XY scatter chart with the the 2nd column (1, 3.61, 7.63, etc.) as the x-values and the column with all zeros as the y-values. This constitues your new x-axis. Hide the true x-axis by double-clicking it and in the resulting dialog box, from the Patterns tab, set the various options to 'None'.

To label the simulated x-axis properly, use Rob Bovey's XY Chartlabeler (www.appspro.com) or John Walkenbach's Chart Tools (www.j-walk.com) and add the first column (1, 2, 3, etc.) as the data label to the dummy series created above.

To plot your actual data, convert each x value to x^1.85 and plot this tranformed value.

 

[Rick_Frye]

I will try that, thank you. By the way, great web site & great information.

Rick

 

[tusharm]

You are welcome and thank you for the compliment.

I will try that, thank you. By the way, great web site & great information.

Rick

 

[Rick_Frye]

Sir, if I were *French I would kiss you! Your suggestion worked and is the icing on the cake of an Excel application I've been programming on the side (unpaid) for my company for the past year.

Thank you, thank you very much!

Rick

*Nothing against the people of France - good people -, just a term picked up along the way.

 

[David Hyde]

Strangely, Google Alerts has just now found this thread and the reference to N1.85, so I'm almost 3 years late. But if you're still interested in N1.85 scales you might want to take a look at DPlot (http://www.dplot.com) and specifically at the page on that scaling: http://www.dplot.com/n185.htm. It's a snap moving your Excel data to DPlot and producing this type of graph.

 

[Drow]

For anyone wanting to use the 185 graph for the fire protection industry. Following the information contained in this thread I "stumbled" upon an alternate solution. In my industry we use the N1.85 graphs for water supply charting and just plotting the points is not enough, we need vertical plotted lines for the code official to reference for water flows (the abscissa is gpm and the ordinate is pressure, the abscissa is log the ordinate is not). I made vertical reference lines by using "y" points of 0 lower and 200 upper limits with a straight line chart and created 16 vertical lines allowing log increments of 200-gpm in the "x" and the 0 to 200 range in the "y". I used the "x" points of 1, 1, 3.61, 3.61, 7.63, 7.63... Then I plot each water supply individually, you can create a normal flow graph, then a 10% or 20% parallel curve, and you can plot your demand if known. I don't use the log function contained in XL as you have to start at 1 but I do adjust the flow points to be plotted as indicated in another post. BTW-I am a fan of D-Plot and own a personal copy of the software and David is correct, it will do the N1.85 graph for you.

 

[JStirfry]

For anyone wanting to use the 185 graph for the fire protection industry. Following the information contained in this thread I "stumbled" upon an alternate solution. In my industry we use the N1.85 graphs for water supply charting and just plotting the points is not enough, we need vertical plotted lines for the code official to reference for water flows (the abscissa is gpm and the ordinate is pressure, the abscissa is log the ordinate is not). I made vertical reference lines by using "y" points of 0 lower and 200 upper limits with a straight line chart and created 16 vertical lines allowing log increments of 200-gpm in the "x" and the 0 to 200 range in the "y". I used the "x" points of 1, 1, 3.61, 3.61, 7.63, 7.63... Then I plot each water supply individually, you can create a normal flow graph, then a 10% or 20% parallel curve, and you can plot your demand if known. I don't use the log function contained in XL as you have to start at 1 but I do adjust the flow points to be plotted as indicated in another post. BTW-I am a fan of D-Plot and own a personal copy of the software and David is correct, it will do the N1.85 graph for you.

@Drow, do you have a copy of this sheet you would be willing to share by any chance?