Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Graphical area under a curve in excel

Posted on 2010-08-24
24
Medium Priority
?
1,907 Views
Last Modified: 2012-05-10
As part of this excel project I'm developing, I'm attempting to intelligently draw circles over a map image. Since, sadly, I don't see anyway to just have excel graph an equation, I've solved the circle equation for Y and have two separate columns which are the solutions.

Now, when I graph these two columns I just get the lines of the curves, but what I'd like is to fill the circles with a shaded region.

Is it possible to somehow fill the area under a curve? After some research I found some very hokey ways that work for just the positive side of the curve (http://peltiertech.com/WordPress/fill-below-an-xy-chart-series-xy-area-combo-chart/), but I'd like something that is a) more robust and b) works for the entire circle.

Any suggestions? I'm open to VBA solutions as well.
0
Comment
Question by:PGRBryant
[X]
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
  • 13
  • 9
  • 2
24 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33516380
VBA won't be able to change the intrinsic properties of the charts of Excel.

In my opinion the only way to achieve this is using Shapes through VBA, but I think it's a hell of a job to position it correctly.... I'll give it a quick look.

Could you show the sample worksheet?
0
 
LVL 50
ID: 33516568
Maybe you could use a bubble chart instead of drawing a circle with an XY? You can exactly position the bubble with the XY coordinates and calculate the size of the bubble from your data. A bubble is easy to fill with color, transparency, etc

cheers, teylyn
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33516810
Good thinking teylyn, I like that approach.

Just for demonstration, not for points, attached a sample book showing 4 circles and X, Y & relative size data ranges.

One additional thing: you can change absolute size in the format Data series interface (right-click any bubble) on Tab 'Options' and changing the Scale Bubble size (in %) and other format option are also available (lto you to find them out).


Book4.xls
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:PGRBryant
ID: 33516940
I was super excited about this concept; it seems like a brilliant and simple solution. Perhaps I'm missing something, but...

I need the bubbles to be precisely defined. That is, I need to be able to have a bubble fill up the entire chart, or have a bubble be itty bitty. Specifically, I'm actually drawing these circles as an overly onto a map, so, if there is a way to indead of this "relative" feature to be able to actually say "the radius is X" then this would entirely solve my problem...

As it stands, I think I'm still stuck with trying to figure out how to fill in the circle. :-/

0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33516948
"..a way 'instead'.." not indead... heh. Wish I could edit posts.
0
 
LVL 50
ID: 33517007
Hmm, I see what you mean (both with the editing posts and with the failure of the bubble to fill an entire chart. Excel seems to have a size limit for the biggest possible bubble and will just resize the smaller ones if the bubble size of the biggest one is cranked up.

Maybe you'll need a VBA solution with shapes instead. VBA would need to grab the map dimensions, then position the shape above the map, and size it accordingly. Way over my head, unfortunately.

cheers, teylyn
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33522966
Working on a possible solution, I've been able to position a circle on the chart left top, tonight I'll try to finish it off.
0
 
LVL 17

Assisted Solution

by:calacuccia
calacuccia earned 2000 total points
ID: 33537267
Attached what I could come up with.

It's a demo workbook.

It works with random data to fill up an X/Y chart with dots... but it has 8 defined circles in the green cells.

Clicking the button will generate 8 circles with the coordinates X/Y & Diameter according to the table on both Charts (one embedded chart, one Chart Sheet).

The button performs a cleanup first to get rid of the old circles (if you run this a second time, this will remove the old ones and keeps circles up to date with data).

The circles are embedded with the Chart (in other words, moving & scaling the Chart form on the sheet will not influence their position, but proportions might distort).

However, changis X or Y axis minimum & maximum values will result in the circles loosing their position on the right X/Y location, hence the need to redraw them using the ButtoN.

Have a look & play around if this can fulfill your needs.

The book was saved without any charts so you can see them popping up as you click the button.
ChartMAgic--version-1-.xls
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33537278
Just for quick viewing this thread, I also have pasted the code for both type of Charts below (not the button code)
Sub Chart_In_Worksheet(X As Double, Y As Double, Z As Double)
Dim mb As Workbook
Dim ms As Worksheet
Dim mCh As ChartObject
Dim mCA As ChartArea
Dim mPA As PlotArea
Dim diameter As Double
Dim mShape As Shape
Dim myX As Double, myY As Double, myZ As Double
Dim xScalar As Double, yScalar As Double, DiaScalar As Double
Dim DeltaX As Double, DeltaY As Double, xAxis0 As Double, xAxis1 As Double, yAxis0 As Double, yAxis1 As Double

Set mb = ThisWorkbook
Set ms = Worksheets("Sheet1")
Set mCh = ms.ChartObjects(1)
Set mCA = mCh.Chart.ChartArea
Set mPA = mCh.Chart.PlotArea


xAxis0 = mCh.Chart.Axes(xlCategory).MinimumScale
xAxis1 = mCh.Chart.Axes(xlCategory).MaximumScale
yAxis0 = mCh.Chart.Axes(xlValue).MinimumScale
yAxis1 = mCh.Chart.Axes(xlValue).MaximumScale
DeltaX = xAxis1 - xAxis0
DeltaY = yAxis1 - yAxis0
xScalar = DeltaX / mPA.InsideWidth
yScalar = DeltaY / mPA.InsideHeight
'Trim Chart to be perfectly square


DiaScalar = xScalar * 0.5 + yScalar * 0.5
myX = X / xScalar
myY = mPA.InsideHeight - Y / yScalar
myD = Z / DiaScalar

Set mShape = mCh.Chart.Shapes.AddShape(msoShapeOval, mPA.InsideLeft - myD / 2 + myX, mPA.InsideTop - myD / 2 + myY, myD, myD)
mShape.Fill.ForeColor.SchemeColor = 10
mShape.Fill.Transparency = 0.75
End Sub

Sub Chart_on_Own_Sheet(X As Double, Y As Double, Z As Double)
Dim mb As Workbook
Dim mCh As Chart
Dim mCA As ChartArea
Dim mPA As PlotArea
Dim diameter As Double
Dim mShape As Shape
Dim myX As Double, myY As Double, myZ As Double
Dim xScalar As Double, yScalar As Double, DiaScalar As Double
Dim DeltaX As Double, DeltaY As Double, xAxis0 As Double, xAxis1 As Double, yAxis0 As Double, yAxis1 As Double

Set mb = ThisWorkbook
Set mCh = Sheets("Circles")
Set mCA = mCh.ChartArea
Set mPA = mCh.PlotArea


xAxis0 = mCh.Axes(xlCategory).MinimumScale
xAxis1 = mCh.Axes(xlCategory).MaximumScale
yAxis0 = mCh.Axes(xlValue).MinimumScale
yAxis1 = mCh.Axes(xlValue).MaximumScale
DeltaX = xAxis1 - xAxis0
DeltaY = yAxis1 - yAxis0
xScalar = DeltaX / mPA.InsideWidth
yScalar = DeltaY / mPA.InsideHeight
'Trim Chart to be perfectly square


DiaScalar = xScalar * 0.5 + yScalar * 0.5
myX = X / xScalar
myY = mPA.InsideHeight - Y / yScalar
myD = Z / DiaScalar

Set mShape = mCh.Shapes.AddShape(msoShapeOval, mPA.InsideLeft - myD / 2 + myX, mPA.InsideTop - myD / 2 + myY, myD, myD)
mShape.Fill.ForeColor.SchemeColor = 10
mShape.Fill.Transparency = 0.75
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33560745
Thanks a lot for your extended effort calacuccia, I'll be looking over it today to confirm that it works!
0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33582248
Calacuccia, your worksheet works beautifully in the supplied excel sheet.

However, as I try to transfer the logic onto my project I've run into some confusing problems.

First, how did you create that button on your excel sheet? For some reason I can't seem to emulate it excatly when I try.

Second, how do you know which number the chart is that you're working with? You say "ChartObjects(1)"

Well, I only have 1 chart on my worksheet, but nothing seems to be appearing, and eventually I'll want a couple of different Charts, so is there a quick way to check what my chart "number" is?


Sorry for my delay, and thanks much for all your hard work so far. Too bad there isn't a multiplier greater than A for once this issue is finally nailed down.
0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33582665
After a more careful review of the sheet, it seems that the circles aren't actually drawing correctly... at least not as far as I can tell.

It's really close, definitely on the right track, but it's not 100% accurate.

I know that you say the graphical area needs to be trimmed to a perfect square, but even then the circles aren't aligning themselves properly (an easy check is to put a data point at what should be the origin of the circle should be, and then make the circle something easy to estimate, like 10,10,10).

Frustrating. Perhaps excel is just limited here, but it sure would be nice to make this work. Doesn't need to be perfect just close enough for the naked eye.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33584988
The problem of the non-perfection comes from the trim area, I think. Haven't seen your first comment, I will look at both your comments during the day.
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 2000 total points
ID: 33585854
Attached version trims the chart and should position them as perfect as is mathematically possible with forms on the Excel Chart. I think it's close enough for the naked eye, especially if you have prepared your charts relatively close to what it should be.


The button: in Excel, open the Control Toolbox menu (right-click menu bar to activate it) and draw a small button on your sheet. Right-Click on it and select View code.

Now you should see an empty sub in which you have to call the Chart procedures with the right arguments.

Private Sub CommandButton2_Click()

End Sub

The chart detection is a bit of trial and error.

To help you, you could use a test module like this, which you run from the worksheet which contains your charts

Sub DetectCharts()
Dim ms As Worksheet
Dim co As ChartObject
Set ms = ActiveSheet

For Each co In ms.ChartObjects
    co.Chart.ChartArea.Interior.ColorIndex = 3
    Application.Wait Now + TimeValue("00:00:02")
    MsgBox "The red chart can be called as ChartObjects(" & co.Index & ") or as ChartObjects(""" & co.Name & """)"
    co.Chart.ChartArea.Interior.ColorIndex = 2
Next co

End Sub
ChartMAgic--version-2-.xls
0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33587301
On the code line

"xScalar = DeltaX / mPA.InsideWidth"

It says, "Run-Time error '16':
        Error, expression too complex."

Very odd...
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33587337
lol, that's the first time I see this error.
Do you have this within my sample workbook?
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33587390
The error normally would occur when too many flloating point expressions are in a single expression.

Problem is: this is as simple an expression as you can get it: a = b/c

Did you not have this problem with the first sample? Because this line did not change between sample 1 & 2.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33587447
Maybe this works.

Small additional refresh inside the loop:

    Application.ScreenUpdating = True
    mCh.Chart.Refresh
    DoEvents

This might make your circles flicker a bit, but it should resolve the issue.

Attached workbook where this is implemented.
ChartMAgic--version-3-.xls
0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33587544
No, I still get the same error... :-/
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33587612
Could you tell in which part of the macro you are when the error pops up?

This line is situated before the Do loop and inside the Do loop.
I'd be interested to know when it happens.
0
 
LVL 1

Author Comment

by:PGRBryant
ID: 33587766
It's inside the Do While Loop.

I'm wondering if there's some setting I can force which will lower the variable numbers or something.

I have no idea why it would matter but maybe rounding the values, or doing something like 'Option Explicit'...

Anyway, I'll keep looking on my end too.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33587842
Maybe change
Do While xScalar <> yScalar And t > 0.002 And i < 100

to
Do While xScalar <> yScalar And t > 0.002 And i < 50

or even lower. This is just an iteration stop to prevent endless loops.

0
 
LVL 1

Author Closing Comment

by:PGRBryant
ID: 33588111
Okay, all I did was wrap mPA.InsideWidth in a round and it worked fine:

 xScalar = DeltaX / Round(mPA.InsideWidth, 20)

Sweet... Thanks much for your help. You definitely answered the question I started with and fully deserve the points.

Now to ask other ones... =]
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33599180
Could you tell me if this also solves the problem of the Error Expression too complex?

xScalar = CDbl(DeltaX / mPA.InsideWidth)


I opened another question for this issue, and I just got this suggestion of using CDbl().

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26447486.html#a33599173

0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

670 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