Excel plot – Reverse Engineering

Posted on 2013-01-23
Medium Priority
Last Modified: 2013-02-02
I have the plot below as a picture and I need to remake the graphic in Excel with the proper equation.

Initial plot
First I printed on the paper the graphic above and I measured with ruler various values that I could see.
I inserted those values in Excel and I made a XY Scatter graphic.
Then I added a trend line and I found that a power trend line fits almost perfect to the measured points.
Excel generates automatic the equation, found as y=2.3728*xpower(-1.067).
I calculated back the y values from x values using the found equation for a double check and looks good.

As we can see on x axes we have log10 scale on original graph. I did the same on Excel.
Now, I need few things:
1) To have also a log scale on y axes as we can see in the initial graphic. I could not find how to set the chart in excel to have the same as in initial graphic and I need that. That is the most important thing.
2) On x and/or at least on y axes I would like to have the values corresponding to minor gridlines.

Question by:viki2000
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 38810702
select the chart
select the Y axis, then right click > select Format Axis
you will see a window to format the axis

select Logarithmic scale

in the Axis Options
minimum   fixed 1
maximum  fixed 100

LVL 21

Author Comment

ID: 38811360
Of course I have already tried that and is not working.
Did you try in the excel file above?
Do you get a chart which is looking similar with the initial plot from picture above?
LVL 21

Author Comment

ID: 38839457
The solution has been found and was not easy.

In the beginning I thought that maybe in Excel cannot plot the data right when I use log scales.
Therefore I tried some other plotting programs from here:

From around 10 which I tried, I found 2 good enough for the present application:
1) DADiSP student free version which can handle 32000 points.
2) SciDAVis – free software.

DADiSP is moving faster when we use over 10K points.
Unfortunately I found out that was not my problem.

When we look on the initial graphic we can see that the top part of the curve is almost a straight line, but on log axes.
That means that the equation which may generate such line is from the “power family”.
Initially I was able to generate pretty the top side because Excel was able to emulate the power trendline.
The bottom side is not the same equation. It had to be emulated with a polynomial type, but excel offer only up to 6 degree of power and is needed more.

The initial approach was to print on A4 paper and to measure the x/y pairs of data.
Then I did it better, with more points on A4 paper.
Then I almost tried on A3. I stopped because I started to analyze the x, y pair on PC with picture editor – I made many thin lines ready to be printed. Then I stopped because another idea better came into my mind: to find software to do the job for me.

I found several, they are called graph digitizers. Then I got more data and more exactly.

Next was to find a better equation from bottom side of the curve.
Using other software for regression analysis I was able to find better emulating curves and equations for the bottoms ide, using polynomials up to 20 degrees.
With the found equations I came back in Excel and I calculated the Y data from given X.
Then I plot on the same axes the initial data from digitizers and the calculated from regression.
Takes time to find then point where the equations really overlap their Y value.

After the calculated curve was found I came back to the log axes.
I found that Excel cannot put labels as we want on the log axes.
I found next article which tricks Excel:

At this point I could say I solved the problem: I have the equations and the plot in Excel with log axes and labels.

Now I had to make a proof for a non-mathematician that my chart is the same as original curve from picture.
Printing on paper and having the 2 similar A4 papers overlapped on the light will be a good proof.

Here comes the Excel problem again: it has no scale.
I follow:
And read:

And I was able to make an approx. 2x2 mm square graph paper sheet in Excel.

After printing and measuring with a ruler, few more times resize the chart was done until I got approx. good precision on the printed paper. I had to print several times.
That generated graph printed on the paper overlap the original pdf printed page pretty well.

To make it easier I used a photo editor and I replaced the original graph with mine.
I have now two pictures on the same size which can be printed and the curves can be checked.
A software analyzer can be also used to measure the deviations.
Practically the error in measurements with a ruler is a lot higher than the differences between original curve and the generated one.

Now a formula can be used instead of reading the initial graph from the picture.

The equations used to emulate the original graph are:
1) When  x = 0.03 to 1.193:
                y = 2,4483*POWER(x;-1,062)
2) When x = 1.194  to 6.548
                y = (-18,0056835370987)*X^0+(85,5731089479288)*X^1+(-146,61946188333)*X^2+(136,68257293501)*X^3+(-77,6752669056633)*X^4+(27,3081809597662)*X^5+(-5,15877653319879)*X^6+(-0,0698746384160011)*X^7+(0,355028363843178)*X^8+(-0,115325918146572)*X^9+(0,0216591010625132)*X^10+(-0,00273253468079752)*X^11+(0,000239121568072857)*X^12+(-0,0000143606448025004)*X^13+(5,64801488768492E-07)*X^14+(-1,30728335248294E-08)*X^15+(1,34370398419619E-10)*X^16
3) When x = 6.549 to 10
                y = 1,02*1^(1/x)

P.S. I contacted the manufacturer of the graph (ABB) and I was informed that they have no formula for that graph. The graph was generated by many real measurements and a trendline was done.

I wanted to make these e comments because I thought it may be interesting for you too.
Maybe later I will write an article about it as general procedure and particular step by step example.

Initial graphCalculated
Open the above pictures in 2 different tabs of the browser and then click back and each tab to see the similarities.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 21

Author Comment

ID: 38843770
On the top side of chart from the attached file there is a scroll bar.
Do you have any idea how to make that scroll bar to behave logarithmic?

If the chart would have been with linear axes  then would be no problem for the scroll bar.
But now the scroll bar gives linear values and the blue/red lines do not move smooth/proportional/similar with top of the  curve.

Any ideas?
LVL 21

Author Comment

ID: 38847071
LVL 21

Author Closing Comment

ID: 38847108
I gave you the points because I wanted to have the present solution added to the knowledge base for other users, not for your answer - because what you said I already tried before I asked the question.
On the other hand I think you know a lot about Excel looking at your qualifications.
Maybe you have been to busy with other things...
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38847231

you don't need to do that.

you can have your post at http:#a38811360 selected as the solution.

to change the selected answer click on the Request Attention located just below your original question, and ask to reopen the thread.
LVL 21

Author Comment

ID: 38847257
that's all right

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question