Comment from Bob Ribando
Time: Thursday, July 17, 2008, 9:36 am

Jon,

I just noticed I am getting a lot of referrals from your website – close to 50 just in July. I guess the “Peltier” effect is real! Thanks!

Bob

Comment from Megan
Time: Monday, December 15, 2008, 2:59 pm

I am doing control charting and am trying to figure out how to offset four different data series into one chart. I am then from there going to create a false data series to create my own data points. Any suggestions?

Comment from Megan
Time: Monday, December 15, 2008, 4:25 pm

I am control charting for an element. I was having trouble with stacking the data but I think I figured it out from your stacked charting link. I also changed my formulas to nest a NA() for empty cells which to me is ugly but gets the job done. Thanks,

Comment from Felix Marusic
Time: Tuesday, February 17, 2009, 6:09 pm

Jon,

I have an ‘07 template which uses a data connection to feed results from an Oracle 10g DB. In that connection I am using an MS Query to filter the data on certain parameters. The problem I’m having is that all the “result” data, which I need to graph and is a VarChar type in the DB, is coming over as text. The only thing I have been able to do is convert the table to a range, select the cells I want to convert , and using the error checker convert to a number. There must be a better way to do this or avoid the values coming over as text to begin with? Any suggestions?

Thanks,

Felix

Comment from Yohay
Time: Monday, March 30, 2009, 8:14 am

Hi,

I don’t know where to pose a question in this site so I’m doing it here.. Hope it’s the appropriate spot for this… Anyways there goes-

I have 2 sets of data of which I’ve created to seperate Scatter Plots. To each set I’ve created a Trend Line. Now both trend lines have different X-coeff, so mathematically speaking they should meet eachother in a certain point. I’d like to show that point where they meet. Only trouble is that I have no points to continue the lines so the lines stop where the points stop (and the corss-point is not shown). My question is how do i make the lines meet WITHOUT adding further (fictional) points?

Best wishes and have a lovely day,
Yohay.

Comment from Lou
Time: Saturday, April 4, 2009, 7:40 pm

Hi

Great site by the way. I’ve got a question. I’ve never quite worked out how to enter simply ifnormation or text onto a chart according to the dates on the x axis.

Say for stock ifnormation, you want to enter announcement informatino to a date.

There is already a basic stock price chart.

Now you want to enter news relating to the date.

How would you achieve this?

Any ideas?

Lou

Comment from Mark
Time: Thursday, May 7, 2009, 1:58 pm

Jon,

Have you finalized your Excel 2007 work-around for being able to shade the area under a curve?
If yes, is it available? If no, are you still working on it and/or do you have any suggestions on what it might take to accomplish?

Thanks

Mark

Comment from Yohay
Time: Tuesday, June 9, 2009, 5:00 am

Hi Jon, How are you?

I have a tough question now…
I would like to sample random numbers -without- replacements (I’m not sure but I think the exact name is Monte-Carlo sampling).

to be more specific; I have 56 names which I want to divide into 5 different groups, whereas every person will show up only once (and one person would not be selected).

maybe the key is with the randbetween function (most probably), but I get many confusions with it, and it’s not automated.

If this is possible, maybe you can tell me how can I do pretty much the same thing (non-replacemental sampling) with 1,084 names.

Thanks in advance &best wishes,
Yohay.

Comment from Yohay
Time: Tuesday, June 9, 2009, 7:56 am

Simple!! yet, I haven’t thought about it..

Thanks a lot :)

Yohay.

Comment from Jim
Time: Sunday, June 21, 2009, 4:20 pm

I am trying to figure out how to fill in the X-values and Y-values for a chart series. I download the source data from an SQL database, calculate all the chart values including the cell range for each series. I would like to specify that each series obtain the new row numbers (for this night’s data) from the contents of a cell that contains the row number rather than having to fill it in manually or scroll to it (the data range for this night’s data may be at row 30,000 or greater). I already determined the row values but cannot figure out how to get this data accepted into the chart series.

Thanks for any assistance you can provide. I already have recorded macros to do the data download and processing. I would love to automate the final step which is to fill in the ranges for each series. Thanks.

Comment from HalT
Time: Thursday, July 30, 2009, 1:11 pm

I have a dynamic chart embedded in a worksheet whose ranges expand or contract based on the number of data points.

I need the VBA code to automatically adjust the chart width (also based on the number of data points) so the x-axis labels are not crowded together or spread too far apart.

The number of data points is returned by the worksheet function:

=COUNTA(AreaDatabase!$N:$N)-3

which is entered on a separate worksheet.

A multiplier that I can change easily is entered into an adjacent cell; I multiply the number of data points by the multiplier and store the result in another adjacent cell. This final numerical result should be the desired chart width, measured in “points”.

I’ve tried all variations of code that I found by searching every Excel website & knowledge base I could find, but without success.

Could you suggest a way to proceed?

Thanks, Hal

Comment from HalT
Time: Friday, July 31, 2009, 8:52 am

RE: Automatically adjust chart width based on number of data records

Jon: Your suggestion works fine. Thanks!

I replaced the string “NumberOfPoints” in your suggested statement with the appropriate “Range” argument.

The desired chart width is still calculated as in the previous post, 30July2009, 13:11hrs but, to simplify things, I moved the result to the same sheet as the embedded chart.

Desired chart width is in cell “A2″.

The relevant VBA statements follow:

ActiveSheet.ChartObjects(“Chart 1″).Activate
ActiveChart.ChartArea.Select
ActiveChart.Parent.Width = Range(“A2″).Value
ActiveWindow.Visible = False

Works great.

Thanks again!

–Hal

Comment from Mike Buis
Time: Wednesday, August 12, 2009, 4:03 pm

I worked thru your example Excel Panel Charts with Different Scales. I used the data you provided and everything worked well. However, when I tried altering the example to fir more what I needed I ran into a problem. It is when adding the new axis series. The procedure is you select the two colums you want (in the online example it was I1:I12 (X-left) and N1:N12). Select the chart and perform a Paste Special. Select the new sereis and change the chart type to an XY chart.

With my data everything still seems to be working up to this point. But, when I double click the series and choose Primary on the axis tab the chart does not look right. The following is a link to view the before and after: http://www.flickr.com/photos/41390963@N05/3815754356/sizes/l/

The series is now on the far right (should be far left) and the x-axis scale is way off. I suspect it has something to do with dates versus numbers. I am not using dates as my units/labels on the x-axis. I am using speed (revolutions per minute) on the x-axis ranging from 0-5000.

Any ideas on how I can fix this?

I can send you an example of how I want the final chart to look in a .pdf format.

Comment from Mike Buis
Time: Wednesday, August 12, 2009, 5:37 pm

Between the time I posted my comment and you posted yours, I had the same thought. So, I decided to start over from scratch. I used the chart type XY Scatter. However, when you look at the chart the X values do not match what I have selected. The X-axis should range from 400 to 4100, but what it is actually ranging from is 0 to 60. There are a total of 38 points to be plotted. For some reason the chart is using the values 1 to 38 as the x-values, even though I have the range selected as the 400 to 4100 values. I have tried deleting the entire column and redoing the equations to create the values between 400 to 4100 and this did not work either. When you hover the pointer over point #21 (as an example) a text box appears saying the following:

Series “CFM” Point “2400″
(21,0.169687935)

The coridinate value should read (2400,0.169687935) and it should be point #21. It is like some type of formating is crossed up.

Comment from Robert Thomson
Time: Thursday, October 1, 2009, 4:43 am

Thanks for the tutorial on filling line charts using VBA. I did experience some problems using the shadebelow subroutine in both excel 2000 and 2003.

When plotting the function for a circle with radius 1000, and centre at (2500,0) for values from 1500 – 2500 in intervals of 50; that is (x-2500)^2 +y^2 = 1000^2; the routine would return an runtime error 1004 “Application defined or object defined error”.

I worked around the problem by using your logic to create an array of points and used the addpolyline function to create the shape directly. Code is below for those interested.

I’d be interested to hear if anyone else has experienced this bug and found the cause of it.

    Sub ShadeBelowPoly()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer, Ipts As Integer
      Dim myShape As Shape
      Dim Xnode As Single, Ynode As Single
      Dim Xmin As Single, Xmax As Single
      Dim Ymin As Single, Ymax As Single
      Dim Xleft As Single, Ytop As Single
      Dim Xwidth As Single, Yheight As Single
      Dim pointArray() As Single

      Set myCht = ActiveChart
      Xleft = myCht.PlotArea.InsideLeft
      Xwidth = myCht.PlotArea.InsideWidth
      Ytop = myCht.PlotArea.InsideTop
      Yheight = myCht.PlotArea.InsideHeight
      Xmin = myCht.Axes(1).MinimumScale
      Xmax = myCht.Axes(1).MaximumScale
      Ymin = myCht.Axes(2).MinimumScale
      Ymax = myCht.Axes(2).MaximumScale

      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.count
      ReDim pointArray(0 To Npts + 2, 0 To 1)

      ' first point - on x axis below the first data point
      Xnode = Xleft + (mySrs.XValues(1) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + Yheight
      pointArray(0, 0) = Xnode
      pointArray(0, 1) = Ynode

      ' points from the series data
      For Ipts = 1 To Npts
        Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin)
        Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
        pointArray(Ipts, 0) = Xnode
        pointArray(Ipts, 1) = Ynode
      Next

      ' penultimate point - on x axis below the last data point
      Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + Yheight
      pointArray(Npts + 1, 0) = Xnode
      pointArray(Npts + 1, 1) = Ynode

      ' Last point - same as first data point to complete polygon
      Xnode = Xleft + (mySrs.XValues(1) - Xmin) * Xwidth / (Xmax - Xmin)
      Ynode = Ytop + Yheight
      pointArray(Npts + 2, 0) = Xnode
      pointArray(Npts + 2, 1) = Ynode

      Set myShape = ActiveChart.Shapes.AddPolyline(pointArray)

      With myShape
        ' USE YOUR FAVORITE COLORS HERE
        .Fill.ForeColor.SchemeColor = 13  ' YELLOW
        .Line.Visible = False
      End With

    End Sub

Comment from Robert Thomson
Time: Thursday, October 1, 2009, 4:57 am

Sorry to post twice. I forgot to mention that the runtime error occured in the line “Set myShape = myBuilder.ConvertToShape”. Also I could stop the error occuring by skipping the first “real” data point by changing the for loop to start at 2 instead of 1 when iterating through the series data. This is ok for the circle function since the first two points are the same.

Comment from Robert Thomson
Time: Thursday, October 1, 2009, 8:54 am

Versions I tested on were Microsoft Excel 2000 9.0.8961 SP-3 and Microsoft Excel 2003 (11.8012.6568) SP2. I’m still googling. I’ll post back here if I find anything relevant.

Comment from Dominique TUSSEAU
Time: Wednesday, October 28, 2009, 6:41 am

I tried you PTS Box and Whisker Chart Utility application with Excel 2003 I would like to use it with Excel 2007. On the chart utilities it is said that “works in Excel versions 2000, 2002, 2003, and 2007 (Windows versions only)”.
Have you any news for an application to Excel 2007.
Thank you for your answer.
KInd regards.
Dominique

Comment from Dominique TUSSEAU
Time: Wednesday, October 28, 2009, 10:11 am

Dear Jon
Thank you for your quick answer.
At the moment I was wondering if it works with Excel 2007 because it was said (http://peltiertech.com/Utility/BoxPlotUtility.html) that it works only for Excel 2003 and Words 2007.

I have try to use former version of PTS Box and Whisker Chart Utility application (1.0.0.0) and patche BoxCharter.xla Create Box and Whisker charts from columns or rows of population data. PTS Inc © 2007) under Excel 2007. I have succeded to install the macro and the dongle in the add on of Excel 2007. I have got some results but the drawing it not the one expected as it was before.

Comment from Dominique TUSSEAU
Time: Thursday, October 29, 2009, 6:00 am

Dear Jan
After a working night I succed to make it working now
It remains some problems concerning :
- the category label which is not independant from the chart itself as it was in previous versions. For a more beautiful presentation it should be nice, i;e., to have it on one line only.
- changing the colour of average pooint may be difficult when it is too near from the median;
- will it be possible to keep some changed coulours and forms as a model for future presentations.

Thanks in advance for your help.

Comment from Yohay
Time: Tuesday, November 10, 2009, 10:08 am

Hi Jon, How are you?

I need to transform the string “1.03.07″ to 03/01/2007 (as a date format).. that, and about a 1000 more dates..

Do you know how is that possible without going crazy with left, concatenate and many more formulas?

Many thans,
Yohay.

Comment from Mary
Time: Wednesday, November 11, 2009, 5:29 pm

I have a workbook with 2 sheets. Sheet 1 Col. 1 contains a model # with the next 6 columns containing additional information re: the model #. This # in sheet 1 in Col 1 is repeated serveral times based on date. Sheet 2 Col. 1 has the same model # listed only once with the next 5 columns containing data for the model # including an amount of money.

I want to write a formula on sheet 1 that will look at Sheet 1 Col. 1 and compare it to sheet 2 col 1 and when it matches, returns a value from Col 5 (the money) on sheet 2.

I have tried vlookup, if, dget…… It is not working. There has to be a way. I’m hoping you might have and answer

Thank you for your time.

Mary

Comment from Mary
Time: Wednesday, November 11, 2009, 8:52 pm

thanks for the response to my VLOOKUP issue. Tried your formula (same thing I tried all day) and what I’m getting are zeros for answers. Here are some of things I tried.

My sheet2 was named…I renamed it sheet2 again. There was a blank col in sheet2…I deleted it. I changed to many things I’m getting goofy. Could there be and issue with 2007?

Comment from Mary
Time: Wednesday, November 11, 2009, 9:11 pm

VLOOKUP issue. I saved and closed the file. Got back in re-wrote the formula and now it works. Must have been a user problem.

Thanks for your help.

Mary

Comment from Yohay
Time: Wednesday, December 9, 2009, 4:03 am

Hi Jon,

How are you?

I’d like to indicate somehow that a certain cell’s color is blue and the other one is white. meaning- i’m on A1 and would like the output to be, in whichever’s way, either a code of B1’s certain color or which ever indication of color (not binary indication, like the command “cell”)

This should be for excel 2003, but if there’s a certain way to do so for excel 2007 i’m also intrigued.

Many thanks,
Yohay.

Comment from Yohay
Time: Wednesday, December 9, 2009, 8:15 am

Hi Jon,

Thanks a lot for the answer.
I do not want A1 to *command* the color of B1, i want A1 to *declare* what color B1 has. Is this thing possible with convensional Excel without VB?

Thanks again,
Yohay.

Comment from Yohay
Time: Wednesday, December 9, 2009, 8:21 am

Ok,

Bumper. Thanks though, of course.

Yohay.

Comment from J. Staadlsky
Time: Tuesday, December 29, 2009, 7:23 am

Do you know of a simple add-in for Excel that could be used to quickly generate a scatter plot by highlighting two columns of data- not necessarily side-by -side?

Comment from Yohay
Time: Thursday, December 31, 2009, 5:55 am

Hi Jon,

Any chance you know the following?

I have a chart with dates for rows (1-Jan-2009, 2-Jan-2009 and so on) and count data for columns (count of player1, player2, player3, … playerN, for N games I have).
I would like to create 2 dynamic ranges; the 1st one would be calculating the average count of players from the last row of the table up to 7 days ago (say if the last row is 15-Jun-2009 I would like the average of the count of players starting from 9-Jun-2009, and once another row is appended- of 16-Jun-2009 I would like the same cell to show the average of 10-Jun-2009 all the way to 16-Jun-2009) and the second one would be of the same concept only with monthly average (30 days ago).

Afterwards I would like to have the last row of the table conditionally formatted with certain unclear elements as for now (I know what I want but not how to create it… I’ll probably ask you about it soon as well).

Where do I start? Does it involve Visual Basic or could it be without it? (maybe something with defining Names or something like that…)

Many thanks and Happy New Year,
Yohay.

Comment from Yohay
Time: Thursday, December 31, 2009, 12:21 pm

Thanks Jon,

You wouldn’t know how hard I banged my head against the wall for that… and it’s funny cause although I was reading about Offset, for some reason I thought it wouldn’t fit.. never mind..

Anyway, I’ve earlier mentioned Conditional Formtting.
Well, my boss wants the following-
for every cell at the last row to have not one but two commands of conditional formatting (one regards the 7-days avg and the other one regards the 30-days avg) is such a thing possible at all? to have more than one condition on the same cell?
Furthermore he’d like to have it such that once you type in another row, the conditions would automatically switch down to the new (most bottom) row.
Possible? if so how?

Many thanx & have a good weekend & happy new year,
Yohay.

Comment from David Huang
Time: Saturday, January 9, 2010, 7:11 pm

Here is an example of how Excel can be used in Sports Science:

I used an XY scatter chart and some trigonometric formulas to create a 3D model of the golf swing. It can be rotated to different viewpoints, and used to isolate the motion of different body parts.

Comment from David Huang
Time: Saturday, January 9, 2010, 9:31 pm

Jon -

Thanks for the idea. I can answer your question in two ways.

- The input to the spreadsheet is a set of measurements from a set of golf swing pictures. If a user used their own pictures, then the swing drawn would be theirs.

- If a user entered measurements from a professional swing, and wanted to customize it for height and build, they could add a scaling formula to one of the dimensions, or change the scale on one of the chart axes.

Maybe I’ll add this functionality (I’ll credit you in my blog).

Comment from David Huang
Time: Saturday, January 16, 2010, 10:54 am

Correction to what I said above. The chart cannot be corrected for height and build by simply altering the dimensions. E.g. if I stretched the model horizontally, it would work if the model were standing straight up, but would start going awry if the model started leaning to one side.

I’ve actually gone the other way. I’ve shortened my driver to 40 inches to maintain the same proportions as my model golfer, with not bad results.

Comment from Ignacio Manzanares
Time: Tuesday, January 19, 2010, 6:06 pm

Hi Jon,

Your tutorials are amazing, im trying to fill a polygon in excel 2010 beta, but i can´t do it, do you have a book or app that i can buy? or can you send me a excel file with a example to my email?

Sorry for mi english, i spoke spanish.

Thanks in advance for your help.

Comment from S K Menon
Time: Tuesday, January 26, 2010, 11:39 am

Hi Jon,
I have a gif chart but not the source. Can I get the data in excel format? Thankyou.
Menon

Comment from Erich Neuwirth
Time: Wednesday, February 24, 2010, 9:27 am

RExcel is an Excel (2002 .. 2010) plugin which connects Excel to the statistics program R (http://www.r-project.org) and allows to call R functions from VBA and within worksheet functions.