 # 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

see the attached as an alternative to clicking the F9 key repeatedly.
p45cal

## Files

• 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

The attached example plots the random numbers in A1:A2 twenty times.
The chart then uses these value to chart an xy scatter.

You should be able to adjust to suit your random calculation.

## Files

[h4]Cheers
Andy
[/h4]

• 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.

[h4]Cheers
Andy
[/h4]

• 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)

## Files

• 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.

[h4]Cheers
Andy
[/h4]

• 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

• Re: Scatter Plot With Line Best Fit

If you do not want cells updating the random values due to recalcs you will need to populate the cells using code.

VBA uses the RND function.

[h4]Cheers
Andy
[/h4]

## Participate now!

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