1

Let's assume I have a have a scatter smoothed line chart on an Excel spreadsheet like the next one:

Fisher Transformation

Is there a way to extract the function line from the chart? As a shape?

Extracting the function shape from the chart

Probably the solution (if there is one) is with a VBA routine and that's fine by me. However I don't have any code to show you, because I don't really know how I could start solving this. If anyone could just point me on a solving direction, maybe I'll figure it out.

Pspl
  • 1,398
  • 12
  • 23
  • 1
    As you suspect, you'll likely have to write your own VBA code but in a slightly different direction. I don't believe it's possible to extract a data curve as a shape from a chart (can't find anything either). But you can recreate the curve by using the trendline formula to get a Y value given an X input. See [this answer](https://superuser.com/a/255149/925350) for some information. – PeterT Oct 04 '18 at 15:47
  • Don't know what you want to do but a workaround would be to remove all the formatting, text and lines. – SJR Oct 04 '18 at 15:50
  • Yes @PeterT, I know how to cheat on this matter using "another ways", eh eh! And I don't really need the trendline (I know the y function of x and I also know how to get the values from the chart). I only wanted to shape the function line! But I guess you're right! It's simply can't be done! – Pspl Oct 04 '18 at 15:53
  • @SJR that's not good enough. Sure you can remove all the formatting chart extras and leave only the line! But then you can't treat it like a shape (for example, you can't rotate it). – Pspl Oct 04 '18 at 15:55
  • Actually you can paste it as a picture and then rotate it. – SJR Oct 04 '18 at 15:58
  • But than it's not a vector shape any more :( You know! Like the ready-made shapes Excels has... – Pspl Oct 04 '18 at 16:00
  • No indeed, that is where it falls down. – SJR Oct 04 '18 at 16:01
  • 2
    However... I think I will dedicate some effort to your idea! Thanks for the suggestion @SJR. – Pspl Oct 04 '18 at 16:04

2 Answers2

1

Well, I think I've found some kind of solution.

First, as you were said, you could write some code to get rid of all that stuff that is not necessary.
Then, you can save that chart to emf with that code (Excel export chart to wmf or emf?)
Then write some code to insert that created emf and crop it a bit inside excel, so there are no borders etc.
Last, remove that picture from hard drive.

I think it could work for you and it shouldn't be hard.

You can then even extract that line itself in Inkscape (which is not possible if you simply copy that and paste).

I don't know much about programming but maybe there are some nice free libraries to edit .emf files to extract that line, so it would then do exactly what you want. (Or using inkscape in command line from inside excel http://www.inkscapeforum.com/viewtopic.php?t=32652)

Shelty
  • 296
  • 2
  • 5
1

Select the chart.

On the Home tab of the ribbon, click the dropdown next to Copy, and in the dropdown menu, choose Copy as Picture...

In the dialog, keep the defaults (especially Picture and not Bitmap) and click OK.

Paste elsewhere on the worksheet.

Right click on the pasted picture, and choose Group > Ungroup. Click on Yes to convert the picture to a grouped collection of shapes.

Right click again on the converted picture, again choose Group > Ungroup.

Press Esc to unselect everything.

Click on the curved line and drag it to where you want it.

Jon Peltier
  • 5,895
  • 1
  • 27
  • 27