Filetype Excel XLS | Posted on 09 Aug 2022 | 3 years ago
Authentication
digital resources
297x Filetype XLS File size 0.52 MB Source: www.learnthermo.com
Sheet 1: Why Excel
| Engineering Calculations in Excel | Baratuci | ||||||||
| Why Excel ? | 3-Feb-04 | ||||||||
| How can Excel help engineers and scientists ? | |||||||||
| 1 - | Excel provides a relatively friendly environment that allows you to import, export, store process, sort and present data. | ||||||||
| 2 - | Excel provides a natural way to work with and present tabular data. | ||||||||
| This is perfect for scientists and engineers because experimental results always involve repeated tests in which independent, or controlled variables, are set and dependent, or measured variables, are sampled to see how they respond. | |||||||||
| So, data collection, by its very nature, produces tables and at some level a spreadsheet like Excel is a big table ! But spreadsheets have become much more than tables to hold data. | |||||||||
| 3 - | The computational power of Excel has grown dramatically over the past decade and many engineers and scientists have devoted a considerable amount of time and effort to demonstrate the wide range of problems that can be solved using Excel. | ||||||||
| - | Excel makes it easy to sort your data and to carry out statistical analyses of data. | ||||||||
| - | Fitting lines and curves to data sets is easy in Excel too. | ||||||||
| - | You can interpolate between data points in Excel, linearly, or by other polynomials. Cubic splines are very popular for data interpolation. | ||||||||
| - | Sophisticated manipulations of vectors and matrices are straightforward in Excel. These powerful functions can save you time and increase your odds of getting your computations correct the first time ! | ||||||||
| - | Numerical integration techniques, such as the Trapezoidal Rule or Simpson's Rule are easy to setup in Excel. | ||||||||
| - | You can numerically solve individual equations or sets of non-linear equations in Excel. | ||||||||
| - | You can use Excel to solve optimization problems as well. | ||||||||
| 4 - | Beneath the cells and formulas in Excel lies the Visual Basic for Applications (VBA) programming environment. | ||||||||
| - | VBA allows you to write subroutines and functions that you can execute from cells in your spreadsheet or from user controls that you can create. | ||||||||
| - | VBA is a very powerful programming tool that can provide almost unlimited functionality for an Excel spreadsheet. | ||||||||
| - | VBA is not well suited to tasks that require enormous computing power. It is not nearly as fast as a lower level programming language such as C and it is not as fast as many computational tools such as Matlab or MathCad. | ||||||||
| - | One of the coolest features of Excel is its ability to record macros. Macros are a series of operations that are converted directly into VBA code for you. These operations (code) can be assigned to a key or combination of keys to be rapidly re-executed at any time. This can make your life MUCH easier if you know what you are doing. | ||||||||
| 5 - | The powerful formatting tools in Excel let you make the spreadsheet fill dual roles. | ||||||||
| - | The same spreadsheet that carries out sophisticated calculations can also be carefully designed to effectively present the methods and results to a broader audience. | ||||||||
| - | Symbol fonts, super- and sub-scripts, combined with the built-in equation editor make it relatively easy to create a portion of a presentation-quality report in the same Excel file that contains your data and calculations. | ||||||||
| 6 - | Excel provides an environment in which you can create presentation-quality graphics in the same file in which your calculations are done. | ||||||||
| 7 - | Excel includes a reasonable set of drawing tools that you can use to create some pretty nice looking graphics inside your spreadsheets. | ||||||||
| 8 - | Excel is fairly well integrated with the other members of the MS Office suite of tools. | ||||||||
| - | It is easy to embed sections of your spreadsheet in a Word document or a Powerpoint presentation. | ||||||||
| 9 - | As part of the MS Office Suite of tools, Excel has achieved remarkable market penetration. | ||||||||
| - | As a result, the odds are very good that when you sit down at your computer on the first day of a new job, you will find Excel installed and ready to run any applications that you have developed in the past. | ||||||||
| - | The only real difficulty is Microsoft's annoying tendency to pretend that backward compatibility is not important. But that is another story. | ||||||||
| Engineering Calculations in Excel | Baratuci | ||||||||
| Basic Calculations | 3-Feb-04 | ||||||||
| Rule #1 - | Never code the values of constants into formulas. | ||||||||
| Example : | Calculate the volume that 8 moles of an ideal gas will occupy at 95 kPa and 328 K. | ||||||||
| Given : | n = | 8 | moles | ||||||
| P = | 95 | kPa | |||||||
| T = | 328 | K | |||||||
| Solution : | The ideal gas equation will help us answer this question : | ||||||||
| R = | 8.314 | J/mole-K | |||||||
| 1 | kPa = | 1000 | N/m2 | 1 | m3 = | 1000 | L | ||
| TIP - you can insert an equation into Excel by using the Insert - Object menu option. Then, scroll down and select - Microsoft Equation 3.0 and click the OK button. | |||||||||
| Now, solve the Ideal Gas Equation for V. | |||||||||
| You can make your eqns look nicer by pressing ctrl-space after the n and the R to insert space between these variables. You can also improve the look of your eqns by eliminating the box around them. Do this by right-clicking on the eqn and selecting the Format Object menu item. When the Format Object window opens, select the Colors and Lines Tab. Change the Line Color to No Line to eliminate the box. Change the Fill to No Fill to make the eqn transparent. Then, press the OK button. This is often useful when you want to put equations on diagrams and graphs that do not have a white background. | |||||||||
| You will need the value of R and some unit conversions to finish this problem. | |||||||||
| Define R in a separate cell at the TOP of your solution, as I have done here. | |||||||||
| Don't forget the units ! | |||||||||
| Put your unit conversion factors in separate cells at the top of your solution also. | |||||||||
| This makes it very easy to calculate V: | V | 0.2296 | m3 | ||||||
| V | 230 | L | |||||||
| Notice that I have used subscripts in my units. You can use them anywhere in text that you enter in Excel. The menu item is: Format - Cells | |||||||||
| Highlight any character or select an entire cell and select the Format - Cells menu item. | |||||||||
| When the window opens, you can choose superscript or subscript from the Effects group of check boxes. Then, press the OK button. | |||||||||
| The sortcut keys for superscripts are ALT-o-e-p, and the shortcut keys for subscripts are ALT-o-e-b. This makes it quick and easy to do super- and sub-scripts. | |||||||||
| To make the page look nice, you can change the height of any rows you like. | |||||||||
| Move your mouse over the line that separates two row number on the right-hand sidde of the spreadsheet. The cursor changes to indicate that you can click-and-drag to change the row height. Double-click to Auto-fit the row height. The same techniques work for the column widths. | |||||||||
| Finally,it is always nice to box and highlight your answers | |||||||||
| Click and drag to select the cell with the variable name, the cell with the value of interest and the cell with the units. Then, click the Borders item in the Formatting toolbar and select the dark outer box. Next, select the Fill Color item in the Formatting toolbar and choose a light background color for your answer cells. I like the lightest yellow. I also make the text bold in my answer box by pressing CTRL-b. If you want to change the color of the box around your answer cells, right click on the 3 cells you have selected and choose the Format Cells menu item. Select the Border tab and use the Color dropbox to choose the color for the box around your answer cells. Then, click the Outline button to apply this color to your border and click OK. | |||||||||
| Always remember to adjust the number of significant figures that Excel displays for your intermediate results and especially for your final answer ! How many significant figures do you need ? You should have learned a lot about this in your Physics courses. Just remember that if you display more than THREE significant figures in your final answer, you had beeter be able to demonstrate why extra digits are justified ! I generally keep 4 or 5 digits on my intermediate values. | |||||||||
| It is often convenient to merge a group of cells in order to create a textbox as I have done throughout this document. To do this, first type all of your text into one cell. Then, click and drag to select the group of cells that you would like to be part of the textbox. Then, right-click on the group of cells you have selected and choose the Format Cells menu item. When the Format Cells window opens, click the Wrap Text and Merge Cells check boxes and click the OK button. Repeat this process with progressively more cells until all of the text you typed is properly displayed. | |||||||||
| Engineering Calculations in Excel | Baratuci | ||||||||
| Graphs | 3-Feb-04 | ||||||||
| The Chart Wizard on the Standard toolbar is the starting point for creating a plot or graph. | |||||||||
| Excel offers a very wide range of choices when creating a graph. | |||||||||
| Here, I will describe the choices that are most commonly used by engineers and scientists. | |||||||||
| A gas is compressed in a syringe that is submerged in a constant temperature bath. | |||||||||
| As the plunger moves down into the syringe, the volume of the gas decreases and the pressure increases. | |||||||||
| If the gas behaves as an ideal gas, then the relationship between P and V is governed by the ideal gas eqn. | |||||||||
| We can solve this eqn for P : | |||||||||
| If we plot P vs V, we will not get a straight line. However, we can take the Ln of both sides of this eqn in an attempt to get a linear relationship. | |||||||||
| So, if we plot Ln P vs Ln V, we should get a straight line with a slope of (-1). | |||||||||
| Here is some sample data that we will plot. | |||||||||
| V (L) | P (kPa) | PIG | Ln V | Ln P | Ln PIG | ||||
| 2.50 | 100 | 100.0 | 0.916 | 4.605 | 4.605 | ||||
| 1.60 | 150 | 156.3 | 0.470 | 5.011 | 5.051 | ||||
| 1.25 | 200 | 200.0 | 0.223 | 5.298 | 5.298 | ||||
| 1.00 | 250 | 250.0 | 0.000 | 5.521 | 5.521 | ||||
| 0.85 | 300 | 294.1 | -0.163 | 5.704 | 5.684 | ||||
| 0.70 | 350 | 357.1 | -0.357 | 5.858 | 5.878 | ||||
| 0.65 | 400 | 384.6 | -0.431 | 5.991 | 5.952 | ||||
| 0.60 | 450 | 416.7 | -0.511 | 6.109 | 6.032 | ||||
| 0.50 | 500 | 500.0 | -0.693 | 6.215 | 6.215 | ||||
| 0.45 | 600 | 555.6 | -0.799 | 6.397 | 6.320 | ||||
| 0.40 | 700 | 625.0 | -0.916 | 6.551 | 6.438 | ||||
| 0.38 | 900 | 657.9 | -0.968 | 6.802 | 6.489 | ||||
| 0.37 | 950 | 675.9 | -0.995 | 6.856 | 6.516 | Slope | -1.16493746795821 | ||
| 0.36 | 1000 | 693.7 | -1.021 | 6.908 | 6.542 | Yint | 5.55146510983652 | ||
| If we ASSUME that the gas really is an ideal gas at 100 kPa, we can draw a line through this point that has a slope of (-1) to represent ideal gas behavior. Then, we can compare the data set to this line. | |||||||||
| The column labeled Ln PIG contains the points that make up this ideal gas line. | |||||||||
| Here is a plot of the results. | |||||||||
| To create a plot like this one, select the cell range D24..F38 and click the Chart Wizard button in the Standard toolbar. | |||||||||
| The most common type of graph used by scientists and engineers is an XY Scatter plot. | |||||||||
| You can choose to connect our data points with straight lines, cubic spline curves or no line at all. | |||||||||
| It is very common to represent data with no line connecting points. | |||||||||
| Then, we can represent an expected or theoretical trend with a line with no points on it, as shown here. | |||||||||
| In the XY Scatter window, I usually select points with no lines and then modify the plots later. | |||||||||
| When you hit the NEXT button, the Data Series window opens. If you have selected the data range properly and the independent variable is in the 1st column, then the 1st tab on this screen shouldn't require any changes. | |||||||||
| It is often necessary to select the Series tab. On this tab it is common to enter a new name for each data series. Click on a data series in the Series pane and then click in the Name field and type the new name you want to use. This name will be displayed in the Legend of the graph. | |||||||||
| Click the NEXT button and you will see the Chart Options window. | |||||||||
| Select the Titles tab and enter a good title for the graph, the x-axis and the y-axis. | |||||||||
| Note: Titles such as Ln P vs. Ln V are never acceptable. | |||||||||
| You will always need to make changes on the gridlines tab. The default in Excel is to use JUST horizontal gridlines. This is almost NEVER a good choice. Either use gridlines for BOTH axes or for neither. Only use gridlines when you expect people to actually try to read precise values from your graph, otherwise, no gridlines is the right choice. | |||||||||
| After you hit the NEXT button in the Chart Options window, the Chart Location window opens. | |||||||||
| You can either embed your graph in a worksheet as I have done here, or you can make a separate tab for the graph like the one in this file called Big Chart. It is easier to print a full-page graph if you place it on a separate tab. | |||||||||
| Once you have a basic chart like the one shown above, you need to fine tune it to get a nice looking chart like the one below. | |||||||||
| First, click on the gray background of the plot and drag the plot region so that it is almost as wide as the box. | |||||||||
| Next, right click on the x-axis and choose the Format Axis item. Select the Scale tab and then change the value in the box labelled "Value (Y) axis Crosses at" to -1.5. | |||||||||
| Click and drag the Legend into a convenient location on te plot that doesn't cover any data points. | |||||||||
| Right click on the gray plot area and select the Format Plot Area menu item. | |||||||||
| In the Border pane, change the color of the border to black. | |||||||||
| In the Area pane, click the None radio button to remove the gray background color in the plot. | |||||||||
| Click the OK button. | |||||||||
| Finally, right-click on the any point in the ideal gas data series in the plot. | |||||||||
| Select the Format Data Series menu item. | |||||||||
| In the Marker frame, click the None radio button. In the Lines frame of the window, click the custom radio button and use the drop down boxes to choose a nice thick, solid red line to represent the trend we expect for ideal gases. | |||||||||
| Finally, we need to get rid of all the trailing zeroes on the numbers on the axes. | |||||||||
| Right-click on the y-axis and select the Format Axis item. | |||||||||
| Then, select the Number tab and decrease the number of decimal places to the right of the decimal point to just one. Then, click the OK button. | |||||||||
| Repeat this process for the x-axis. | |||||||||
| Now, THAT is a nice looking plot ! | |||||||||
| But this still isn't the best way to present this data !! | |||||||||
| Can you quickly tell from the plot what pressure we would expect in the syringe when the volume is 0.6 L ? | |||||||||
| No, because most humans are not too good at doing logarithms in their heads ! | |||||||||
| A better way to represent this data is in a log-log plot, like the one shown below. | |||||||||
| Make a new plot like the one below. | |||||||||
| Instead of starting with the Ln V, Ln P and Ln PIG data, select the V, P and PIG data and click the Chart Wizard button in the Standard toolbar. | |||||||||
| Keep BOTH x and y gridlines in this plot. | |||||||||
| Right click on the x-axis and select Format Axis. Then, set the number of decimal places in the axis labels as we did before, but don't click the OK button yet. | |||||||||
| Select the Scale tab and check the Logarithmic Axis check box. While you are here, set the "Value (Y) axis Crosses at" box to 0.1 as well. Then, click the OK button. | |||||||||
| Change the y-axis to a logarithmic axis in the same manner. While you are doing this, change Minimum value for the y-axis to 100 and set the "Value (X) axis Crosses at" to 100 also. Then, click the OK button. | |||||||||
| The logarithmic axes has same effect on the shape of the PV data as the logarithm button does on the PV values. | |||||||||
| Remember, a semi-logarithmic (one log axis) or logarithmic (both log axes) are almost always better than plots of Ln(something) plotted on an ordinary linear axis. | |||||||||
no reviews yet
Please Login to review.
