Out of all the techniques available in the internet for plotting/ drawing the Mohr’s circle one can find very few posts which explains the same using excel (no VBA coding). Here is the simple technique to draw Mohr’s Circle in Excel.

 

Let us consider this example below:

For the initial stress element shown, draw the mohr’s circle and also determine  the principle stresses and the maximum shear stress

Alt Inital stress Diagram
Inital stress Diagram

1. Enter the Stress details

First enter the stress details in the excel sheet considering the sign conventions.

Alt Stesses

2. Draw the Diametre of the Circle

  • Plot the 2 end points on the graph

Plot the points (σ along x-axis & τ along y-axis).  The two end points of the Diametre are:

  • (σx , τxy) = (400 , 200) ;  (Normal stress on the X – Face , Shear stress on the X – Face)
  • (σy , τyx) = (-300 , -200) ;(Normal stress on the Y – Face , Shear stress on the Y – Face) 
  • The point where it cuts the x-axis is the centre of the Mohr’s circle.

Distance of this point from the origin will be average of the normal stresses.

These three points are to be tabulated in the same excel sheet as below:

unrotated axis

Alt Normal stresses entry

Select this table and insert the scattered graph (Insert →Scatter→Scatter with Straight lines and Markers)

Alt plot scatter graph

3. Plot the circle around the diameter created

Radius will be the hypotenuse of the triangle as in the figure below:

Alt Triangle

 Calculate Radius of the  Mohr circle in any cell with the formulaAlt Formula Radius

Alt Calculate Mohrs circle Radius

Plot the circle with Radius ‘R’

We need Calculate Cartesian co ordinates of the points of the Circle whose Radius is ‘R’ & with ( (σx+σy)/2, 0) as origin( for 0 – 360 degree)

First create a table

Alt Table

(σ , τ) = ((σx+σy)/2 + Radius x Cos(angle in radians) , Radius x Sin(angle in radians))

Alt Formula

Alt Cosine

alt sine

Then complete the table till  360 degrees

Alt cartesian points of mohr circle

Select the Chart (in which already the Diametre is plotted )→Right click →select data →add series →select all the Cartesian co-ordinates of the circle from the table

Alt Add series- Circle Points

3. Highlight the Maximum/Minimum Stresses

Create a Table just below the σ –τ table

SERIES NAME

X axis

Y axis

Max Normal stess

σavg + R

0

Min Normal stess

σavg – R

0

Max clockwise Shear Stress +INDEX(all σ values, MATCH(Cell containing +R, all τ values,0))

+R

Max anti-clockwise Shear Stress +INDEX(all σ values, MATCH(Cell containing -R, all τ values,0))

-R

Add series and give the series name, x values & y values

Highlight series

Format these max/ min stresses points by right clicking on the points and formatting marker propertiesFormat Max stresses

Now the Mohr’s circle is complete.Do a little bit of formatting the  Mohr Circle looks like this:

Alt Mohr'sCircle_Using Excel_scatter charts_graph

you can download this by clicking on the link below:

Excel_MohrCircle_sadakchapobserver

(Note: every time when the  stress values are changed , the formed circle may not be a perfect circle so the chart  needs to be re-sized each time when the values are changed)

did you find this post useful? please don’t forget to leave your comments and suggestions. thank you

Advertisements