Excel plot – Reverse Engineering

Posted on 2013-01-23
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
  • 6
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 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 20

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 20

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.
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 20

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 20

Author Comment

ID: 38847071
LVL 20

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 20

Author Comment

ID: 38847257
that's all right

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

685 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