Excel Table Madness!!

CrazyAMonkey

New Member
Joined
Nov 3, 2009
Messages
19
Hello again all, i have a new and annoying problem.

I have created a scatter graph to compare 2 sets of values. On Y i have stress and on the X i have strain (irrelevant i know, just setting the scene :))

my info table is thus,

<table style="border-collapse: collapse; width: 308pt;" width="409" border="0" cellpadding="0" cellspacing="0"><col style="width: 53pt;" width="70"> <col style="width: 98pt;" width="130"> <col style="width: 81pt;" width="108"> <col style="width: 76pt;" width="101"> <tbody><tr style="height: 16.5pt;" height="22"> <td colspan="2" class="xl71" style="border-right: 1pt solid black; height: 16.5pt; width: 151pt;" width="200" height="22">Test Data For Sample Y</td> <td colspan="2" class="xl71" style="border-right: 1pt solid black; border-left: medium none; width: 157pt;" width="209">Results For Sample Y</td> </tr> <tr style="height: 19.5pt;" height="26"> <td class="xl68" style="height: 19.5pt; width: 53pt;" width="70" height="26">Load (N)</td> <td class="xl69" style="width: 98pt;" width="130">Extension (mm)</td> <td class="xl69" style="width: 81pt;" width="108">Stress (N/m<sup>2</sup>)</td> <td class="xl69" style="width: 76pt;" width="101">Strain</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">2000</td> <td class="xl67" style="width: 98pt;" width="130">0.6</td> <td class="xl70" style="width: 81pt;" width="108">100.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">1.09E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">4000</td> <td class="xl67" style="width: 98pt;" width="130">0.8</td> <td class="xl70" style="width: 81pt;" width="108">200.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">1.45E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">6000</td> <td class="xl67" style="width: 98pt;" width="130">1</td> <td class="xl70" style="width: 81pt;" width="108">300.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">1.82E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">8000</td> <td class="xl67" style="width: 98pt;" width="130">1.18</td> <td class="xl70" style="width: 81pt;" width="108">400.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">2.15E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">9400</td> <td class="xl67" style="width: 98pt;" width="130">1.3</td> <td class="xl70" style="width: 81pt;" width="108">470.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">2.36E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">9200</td> <td class="xl67" style="width: 98pt;" width="130">1.5</td> <td class="xl70" style="width: 81pt;" width="108">460.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">2.73E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">9200</td> <td class="xl67" style="width: 98pt;" width="130">1.6</td> <td class="xl70" style="width: 81pt;" width="108">460.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">2.91E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">10000</td> <td class="xl67" style="width: 98pt;" width="130">1.75</td> <td class="xl70" style="width: 81pt;" width="108">500.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">3.18E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">11000</td> <td class="xl67" style="width: 98pt;" width="130">2.1</td> <td class="xl70" style="width: 81pt;" width="108">550.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">3.82E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">12000</td> <td class="xl67" style="width: 98pt;" width="130">2.4</td> <td class="xl70" style="width: 81pt;" width="108">600.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">4.36E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">14000</td> <td class="xl67" style="width: 98pt;" width="130">5</td> <td class="xl70" style="width: 81pt;" width="108">700.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">9.09E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">12000</td> <td class="xl67" style="width: 98pt;" width="130">7.2</td> <td class="xl70" style="width: 81pt;" width="108">600.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">13.09E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">10000</td> <td class="xl67" style="width: 98pt;" width="130">7.25</td> <td class="xl70" style="width: 81pt;" width="108">500.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">13.18E-3</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 53pt;" width="70" height="22">8000</td> <td class="xl67" style="width: 98pt;" width="130">7.27</td> <td class="xl70" style="width: 81pt;" width="108">400.00E+6</td> <td class="xl70" style="width: 76pt;" width="101">13.22E-3
</td> </tr> </tbody></table>

As you can see the values of strain are ever increasing, however whenever i make the graph of stress Vs strain i get a negative going tail for the last two values (i.e. graph is saying strain is decreasing).

I can not understand what is happening with it?!

I know this has to be a quick fix and my error, please someone put me out of my misery!! :LOL:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
The last 3 values have quite different stress but very similar strain.
 

CrazyAMonkey

New Member
Joined
Nov 3, 2009
Messages
19
There is quite a marked inflection even though the numbers are actually increasing. I think i know what you mean, i.e. you might not notice the graph moving forward due to the small movement. That i could handle happily, this is definitely showing a reverse in direction though which i cannot accept :(

Sorry if i have caught the wrong end of the stick :)

Picture Posted
 
Last edited:

CrazyAMonkey

New Member
Joined
Nov 3, 2009
Messages
19
CrazyGraph.jpg
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

The problem data is here I believe

23vav53.png
 

CrazyAMonkey

New Member
Joined
Nov 3, 2009
Messages
19
ok, is this an understanding problem between maths and excel then?

Here are the values formatted specially and normally :

<table style="border-collapse: collapse; width: 152pt;" width="202" border="0" cellpadding="0" cellspacing="0"><col style="width: 76pt;" width="101" span="2"> <tbody><tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 76pt;" width="101" height="22">4.36E-3</td> <td class="xl67" style="width: 76pt;" width="101">0.004363636</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 76pt;" width="101" height="22">9.09E-3</td> <td class="xl67" style="width: 76pt;" width="101">0.009090909</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 76pt;" width="101" height="22">13.09E-3</td> <td class="xl67" style="width: 76pt;" width="101">0.013090909</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 76pt;" width="101" height="22">13.18E-3</td> <td class="xl67" style="width: 76pt;" width="101">0.013181818</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl66" style="height: 16.5pt; width: 76pt;" width="101" height="22">13.22E-3</td> <td class="xl67" style="width: 76pt;" width="101">0.013218182</td> </tr> </tbody></table>
I thought about this myself earlier and got rid of the exponential terms to leave pure decimals. The graph still looked identical.

The value IS increasing so why does the line not continue to the right as i would expect and dearly love it to? :LOL:
 

CrazyAMonkey

New Member
Joined
Nov 3, 2009
Messages
19
OK, i got it. :ROFLMAO:

Its the bloomin curve drawn by Excel. Just held a pen up to get a straight edge and it DOES move forward!! Really sorry for wasting your time and for possibly testing your patience VoG, much appreciated though, you made me look closer and from a different angle.

Thank you VoG! ;)

Ewan
 

Forum statistics

Threads
1,144,583
Messages
5,725,132
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top