Simple problem I can't solve

Johnegee

New Member
Joined
Feb 16, 2017
Messages
20
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
.
Paste in the Sheet Module :

VBA Code:
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
 
Upvote 0
Johnny, I don't know how you set your worksheet; I'd use in D1
Excel Formula:
=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
Excel Formula:
=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
 
Upvote 0
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)

With this program you can choose exactly how many random instances you want:
sbExactRandHistogrm_MrExcel_Simple_Problem.xlsm
ABCD
2ValueExpected countReal countGenerated Tip Sequence
35454510
410555510
510
65
sbExactRandHistoGrm
Cell Formulas
RangeFormula
C3:C4C3=COUNTIF($D$3:$D$120,A3)
D3:D6D3=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.
 
Upvote 0
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:

VBA Code:
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
Code:
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.
 
Upvote 0
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$?
 
Upvote 0
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.
 
Upvote 0
Maybe:
VBA Code:
Cells(Rows.Count,"D").End(xlUp).Offset(1,0).Value = Range("D4").Value

Bye
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top