Converting this Chart into EXCEL

[choenk]

Hi Guys,

This is my first post here, really need your help to solve this
I have this graph in Jpeg format and i want to make it in excel format
I managed to make the equation but it seems i'm stuck when convert it into the graph

Please help me to solve this
Efficiency Chart.jpg
Efficiency.xlsx

[MrShorty]

The main part of the chart is a simple "XY scatter" chart. Both X and Y axis scales are logarithmic (setting in the format axis dialog). Basic instructions for creating an XY scatter chart: https://support.office.com/en-us/art...rs=en-US&ad=US

There are a couple of different ways to create a Y axis like that (assuming that line off to the left is the y axis in 2 different scales). Easiest first attempt will be to use a primary and secondary y axis. Basic instructions for adding a secondary axis: https://support.office.com/en-us/art...rs=en-US&ad=US

In order to get this "contour plot" you are also going to need to rearrange your source data. I usually use a format like:

Please Login or Register  to view this content.

This table then becomes the source data range for the scatter chart.

That is probably not the end of the task, but does that much make sense? Are there parts of that that you need help with?

[choenk]

Dear MrShorty,

Thanks a lot for trying to help me, i tried your suggested method but ended up with this
Could you please help to show me the example in excel format?
Chart.jpg
Really appreciate your help on this

[teylyn]

Yes, that's plotting your data exactly. Half of the data points are at Y value 60, the other half are at X value 3000.

If you expect a different chart, you'll need different data. The pink photo shows several lines. For a line in an XY scatter chart you need at least two pairs of XY values: one XY pair for the start of the line and another XY pair for the end of the line. Your data does not have such pairs.

[choenk]

Dear Teylyn,

Thanks for your reply
Actually i planned to do as what you said, so i made this data source hoping that the same percentage value will make a line automatically
BUt it seems i was wrong
Efficiency table.jpg
Could you please advise what the next step i should take?

Thanks

[choenk]

Double Post

[MrShorty]

It appears that you have chosen a different data layout to what I suggested. That's ok, sometimes I use this data layout, too, to plot a contour plot. Next steps that I see:

1) It appears that you inserted a "scatter plot with markers and no lines" as your chart type. Now, format the data series (or change the chart type) so that the data series is lines with no markers. (right click on the data series -> format data series -> adjust the various settings to remove the markers and add and format the line)
2) With the data table exactly as is, Excel will draw a line connecting each contour. To break the connection between contours, you will need to
a) insert a blank row in between each contour. So, insert a blank row between E=0.2 and E=0.4, another blank row between E=0.4 and E=0.7, and so on throughout the data table.
b) Go into the "Hidden and Empty cell" dialog (part of the Select Source Data dialog) and make sure that it is leaving a gap for empty cells.

That should give you something very close to the contour plot for fn vs fd that you show in your picture.

From there, you will need to add a secondary axis, as explained in the help file I linked to above. Then format that axis so that the minimum and maximum limits correctly match up with the primary axis limits.

[choenk]

Dear MrShorty,

Thanks for your kind explanation, based on that i somehow managed to get the form, but i dont know how to arrange the axis value to match the original Picture
Could you give me another suggestion for this?
efficiency 2.png

THanks

[MrShorty]

Confusingly, the position of the Y axes is a property of the horizontal X axis -- the "vertical Y axis crosses at" property of the horizontal X axis. You can move the primary vertical axis by going into the "format horizontal X axis" dialog, and choosing the appropriate options for the "vertical axis crosses:" property.

In order to change the position of the secondary vertical axis, you will need to display (at least temporarily), the secondary horizontal axis (see "how to display secondary axes" link above). Then you can select the secondary horizontal axis, and change the "vertical axis crosses at" property of the secondary horizontal axis.

How important will it be to have the axes "offset" from the main chart like in your picture? You should be able to easily position the axes at either side of the chart. However, getting the axes to be separate from the main chart is going to be a significant step up in difficulty.