Scatter Plot With Line Best Fit

  • Hi,


    I got the following table and it generates a random number either 1 or 0 (win or loss)


    trade win/loss Account value
    0 10,000
    rand() 1 0 9,800
    rand() 2 1 11,000
    rand() 3 1 12,000
    rand() 4 0 11,500


    Each time I press F9, the Account value changes because the win/loss is generated randomly. Assuming, I press F9 100 times, how do I create a scatter plot chart with a best line fit for all the account values. I know how to create the scatter plot but how do I save all the values everytime I press F9.


    thanks

  • Re: Scatter Plot With Line Best Fit


    thanks P45cal but that is not what i was looking for.


    Based on the example you gave me, what I'm looking for is to record cell C101 every time I hit recalculate (F9) and plot in a chart. Say I hit F9 20x, then excel will plot a scatter plot of cell C101 20x in a chart.


    E.g. Start account : 10,000

    rand() 1 0 9,800
    rand() 2 1 11,000
    rand() 3 1 12,000
    rand() 4 0 11,500


    record 11,500, then F9


    rand() 1 1 10,500
    rand() 2 1 11,000
    rand() 3 1 12,000
    rand() 4 0 11,900


    record 11,900, then F9


    rand() 1 1 10,500
    rand() 2 1 11,000
    rand() 3 1 12,000
    rand() 4 1 14,000


    record 14,000, then F9


    rand() 1 0 9,900
    rand() 2 0 9,700
    rand() 3 0 9,000
    rand() 4 1 10,100


    record 10,100


    Now plot on chart 11,500, 11,900, 14,000, 10,100


    Hope that helps explain what i am trying to do


    thank u so much in advanced

  • Re: Scatter Plot With Line Best Fit


    Thanks Andy...


    One more quick question


    If I wanted to plot the values in Column 20 and Row 370, what do i need to modify from this code? This code seems to copy the values into column 20 and down.


    For lngRow = 1 To 20
    Cells(lngRow, 20).Value = Range("A1").Value


    thanks again

  • Re: Scatter Plot With Line Best Fit


    The adjust the row and column indexes


    Cells(<row number>,<column number>).


    Each set of values must appear in it's own set of cells in order for you to plot it.

  • Re: Scatter Plot With Line Best Fit


    Hi Andy,


    I got a small discrepancy in the values. How can I make A1 = E20 after running the macro?


    thanks


    I'm using this code for macro

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]I have attached my work here.


    Hope someone can correct it. I'm very close.


    Thanks in advanced[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I broke the code into its simplest form and this is what is happening. Using Andy's example with A1 and A2.


    Every time a new cell copies a value from A1 into E1, it automatically recalculates and I am unable to record the original value of A2 into F1.



    rand() A1=3
    rand() A2=6


    E1=3
    F1=7 (instead of 6)

  • Re: Scatter Plot With Line Best Fit


    A1 will not match E10. The act of populating E10 causes A1 to recalculate.


    Here is an example of the values as the code runs.


  • Re: Scatter Plot With Line Best Fit


    Yes Andy. This is the main problem i am having and hence the discrepancy. Is there a way to solve this?


    Reason is I have different account values for A1,A2 and A3 where A1 > A2 > A3 and when I run the macro, F1 is not a valid because when Cell E1 was populated, A2 is recalculated. Then F1 is populated at the next round.


    First calculation
    A1 = 8
    A2 = 5
    A3 = 3
    E1= 8


    8 8 (#1,when this populates) 7 (should be 5 not 7) G1
    5 (#2, recalculate, 7) E2 F2 G2
    3 E3 F3 G3


    F1 and G1 are not the same values from first calculation and so on


    thanks to all who have helped

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!