Simple problem I can't solve

[Johnegee]

Hello, all,

I face what I think is a simple problem, yet everything I’ve tried, including a fair amount of research online, has failed.

I have a spreadsheet in which a random number generates a tip for restaurant servers of $5 or $10. I’d like to collect those tip amounts in a series of cells that would grow downwards. The main purpose would be to calculate the average, to make sure it settles down to the amount expected.

I’ve set the random number so it calculates a tip of five dollars 45% of the time and ten dollars 55% of the time, which means the average will approach $7.75, but I want to see whether it really does. FWIW, I already know some macro code for finding the first empty cell in Column D starting below the visible part of the spreadsheet, and it does work. I’ve got the spreadsheet set up so I or the server can initiate another run, and I want to start many runs myself to see the results collected in Column D.

How can I collect the results of each run, always located in Cell D4, in a new row in Column D? That column would be a collection of $5 and $10 amounts.

Thanks.

--Johnny

 

[Logit]

.
Paste in the Sheet Module :

Private Sub Worksheet_Calculate()
Dim ShtChng
On Error Resume Next

    Dim Xrg As Range
    Set Xrg = Range("D4")
    If Not Intersect(Xrg, Range("D4")) Is Nothing Then
Dim KeyCells As Range

Set KeyCells = Range("D4")
On Error Resume Next

Application.ScreenUpdating = False

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
    Application.EnableEvents = False
    
    'Copy A Range of Data
    Worksheets("Sheet1").Range("D4").Copy
    
    'PasteSpecial Values Only
    Worksheets("Sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues
      
    With Worksheets("Sheet1")
        .Rows("5:5").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End With
    
    Worksheets("Sheet1").Range("D4").Select
 
    Application.EnableEvents = True
 
End If

'Clear Clipboard (removes "marching ants" around your original data set)
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
    End If
End Sub

 

[Anthony47]

Johnny, I don't know how you set your worksheet; I'd use in D1

=IF(RAND()>0.45,10,5

Then copy D1 down for 10-100-1000 cells

Then I calculate in F1 the average of the available numbers using

=AVERAGE(D:D)

In addition, whenever you press F9 (recalculate) or enter anything on the worksheet the cells will recalculate thus offering a new set of simulated value and its average.

This is in addition to what has been suggested by @Logit, above

Bye

 

[Sulprobil]

I have uploaded a sample program here:
(please download, open and use at your own risk, but I am using an up-to-date virus scanning program)

Dropbox - File Deleted - Simplify your life

www.dropbox.com

With this program you can choose exactly how many random instances you want:

sbExactRandHistogrm_MrExcel_Simple_Problem.xlsm
	A	B	C	D
2	Value	Expected count	Real count	Generated Tip Sequence
3	5	45	45	10
4	10	55	55	10
5				10
6				5
sbExactRandHistoGrm

Cell Formulas
Range	Formula
C3:C4	C3	=COUNTIF($D$3:$D$120,A3)
D3:D6	D3	=INDEX($A$3:$A$4,TRANSPOSE(INT(sbExactRandHistogrm(SUM(B3:B4),1,3,$B$3:$B$4))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Please note that the array formula in column D goes down to D102.

 

[Johnegee]

I wish I had exaggerated how simple (I think) the problem is. While I don't understand much of what you three (Logit, Anthony47 and Sulprobil) have suggested, all I need is what I assume is a single line copying the value in D4 to the next row down in Column D. My spreadsheet already determines whether the tip is $5 or $10, and it already finds the next empty cell in Column D.

The macro code for finding the next empty cell is as follows:

ActiveSheet.Range("D6:D10006").Find("").Select

I've tried several lines of code to copy the value in Cell D4 to the next empty cell in Column D, including Logit's

Worksheets("Sheet1").Range("D4").Copy

Worksheets("Sheet1").Range("D4").Copy, but nothing seems to work.

Please try again, and thanks.

--Johnny


P.S. Some day I'll figure out how to use this forum's interface. Until then please be patient with me.

 

[Sulprobil]

Hmm, I thought your challenge was also to have these tips randomly, but at the end you want EXACTLY 45% times $5 and 55% times 10$?

 

[Johnegee]

It's simpler than I seem to have expressed. I know how to select the cell to be copied to, and all I want to do is copy whatever the value in D4 is to that cell to be copied to. The average will take care of itself, as will, I think, everything else.

All I want to know is the VBA code needed to copy the value in D4 to the cell that has the focus. After researching the question online for another half hour I still can't find what I assume is a simple command. (Perhaps it involves Paste Special.)

Thanks.

 

[Anthony47]

Maybe:

Cells(Rows.Count,"D").End(xlUp).Offset(1,0).Value = Range("D4").Value

Bye