Solved

Graphical area under a curve in excel

Posted on 2010-08-24
24
1,862 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
  • 13
  • 9
  • 2
24 Comments
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
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

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:PGRBryant
Comment Utility
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
Comment Utility
"..a way 'instead'.." not indead... heh. Wish I could edit posts.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No, I still get the same error... :-/
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now