SOLVED

Bootstrap

%3CLINGO-SUB%20id%3D%22lingo-sub-2699818%22%20slang%3D%22en-US%22%3EBootstrap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2699818%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20don't%20know%20why%20if%20I%20type%20in%20any%20cell%20in%20an%20excel%20sheet%20that%20has%20bootstrap%20statistics%20analysis%2C%20it%20resamples%20again.%20Please%20see%20attached%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2699818%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2700136%22%20slang%3D%22en-US%22%3ERe%3A%20Bootstrap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20range%20under%20Bootstrap%20samples%2C%20beginning%20in%20B10%2C%20contains%20formulas%20that%20use%20the%20RAND()%20function.%3C%2FP%3E%0A%3CP%3ERAND()%20will%20automatically%20be%20recalculated%20whenever%20Excel%20calculates%2C%20for%20example%20after%20you%20edit%20a%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2700378%22%20slang%3D%22en-US%22%3ERe%3A%20Bootstrap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700378%22%20slang%3D%22en-US%22%3EHello%20Hans%2C%3CBR%20%2F%3EThank%20you%20for%20your%20reply!%20So%20how%20do%20I%20fix%20it%20so%20I%20can%20control%20the%20resampling%20process%3F%20I'd%20like%20to%20have%20a%20better%20control%20of%20the%20resampling%20because%20I%20will%20be%20editing%20in%20the%20sheet%2C%20and%20I%20don't%20to%20continue%20to%20resample.%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3ESam%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2700474%22%20slang%3D%22en-US%22%3ERe%3A%20Bootstrap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20attached%20version%2C%20the%20formulas%20have%20been%20replaced%20with%20values.%3C%2FP%3E%0A%3CP%3EI%20added%20a%20command%20button%20that%20runs%20a%20macro%20to%20resample%20the%20values.%3C%2FP%3E%0A%3CP%3EYou%20can%20view%20the%20code%20in%20the%20Visual%20Basic%20Editor.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2700667%22%20slang%3D%22en-US%22%3ERe%3A%20Bootstrap%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2700667%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Hans%2C%3CBR%20%2F%3EThank%20you%20so%20much%20for%20your%20help.%20In%20the%20attached%20sheet%20that%20I%20sent%20you%20earlier%2C%20I%20created%20a%20tab%20%22Bootstrap%20(2)%22.%20The%20original%20samples%20are%20in%20rows%20numbered(1-3)%2C%20but%20each%20sample%20row%20is%20a%20separate%20or%20independent%20row.%20So%2C%20I%20numbered%20them%20into%20rows%20number%201-3%20(red%2C%20green%2C%20blue).%20But%20in%20reality%20I%20have%206000%20independent%20rows%20in%20my%20lab%20test%20data%20Excel%20sheet.%20How%20do%20you%20make%20VBA%20code%20so%20it%20does%20the%20resampling%20for%20each%20row%20independently%20for%20the%20three%20rows%3F%20I%20can%20fix%20the%20VBA%20code%20once%20i%20see%20how%20you%20do%20it%3F%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello,

I don't know why if I type in any cell in an excel sheet that has bootstrap statistics analysis, it resamples again. Please see attached sheet.

 

Thanks,

Sam

10 Replies

@SamFares 

The range under Bootstrap samples, beginning in B10, contains formulas that use the RAND() function.

RAND() will automatically be recalculated whenever Excel calculates, for example after you edit a cell.

Hello Hans,
Thank you for your reply! So how do I fix it so I can control the resampling process? I'd like to have a better control of the resampling because I will be editing in the sheet, and I don't to continue to resample.

Regards,
Sam

@SamFares 

In the attached version, the formulas have been replaced with values.

I added a command button that runs a macro to resample the values.

You can view the code in the Visual Basic Editor.

Hi Hans,
Thank you so much for your help. In the attached sheet that I sent you earlier, I created a tab "Bootstrap (2)". The original samples are in rows numbered(1-3), but each sample row is a separate or independent row. So, I numbered them into rows number 1-3 (red, green, blue). But in reality I have 6000 independent rows in my lab test data Excel sheet. How do you make VBA code so it does the resampling for each row independently for the three rows? I can fix the VBA code once i see how you do it?

Regards,
Sam

@SamFares 

Are you sure you attached the correct workbook? I don't see a sheet named Bootstrap(2).

 

S0722.png

Hi Hans,
I attached two documents. Let me try again. In my lab tests, i have 30 gas releases in the wind tunnel. For each gas release the the measurement of concentration vs time  goes up Excel row 6025.   i'd like to generate 500 resamples for each row. Each row represents one time, but we did 30 releases( experiments).  Please let me know if you have any questions.

Thanks,
Sam

@SamFares 

You've totally lost me. Your PDF shows 6025 rows but Bootstrap #2 has only 3 rows.

 

Can you explain in tiny detail, step by step, what you want?

Hans,
I am attaching the actual Excel Sheet that shows 30 gas releases data. Each release is an experiment on its own. The gas concentrations in the air is a measured with a device and it reads it at different relative times which is shown in A26-A6025. At each relative time you will see the measurements for each relative time. In this experiment 30 samples per relative time is not enough. i need to generate resamples for each row(relative time) for up to 500. I am not sure if you can have the VBA code ask the question how many resamples i need so i can type the number<=500. if i decide to change the numbers of resamples, have it delete the existing and regenerate new resamples based on the new resampling number. this is sheet has the data from lab experiment.
it starts from A26:AF26 and up to row 6025.
Thanks a lot!
Sam

best response confirmed by SamFares (Contributor)
Solution

@SamFares 

Try this:

Sub Resample()
    Dim NumSamples As Long
    Dim LastRow As Long
    NumSamples = Val(InputBox("How many samples do you need (31 - 500)?"))
    If NumSamples < 31 Or NumSamples > 500 Then
        MsgBox "Invalid input. Must be between 31 and 500!", vbExclamation
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("AG26:SH" & LastRow).ClearContents
    With Range(Cells(26, 33), Cells(LastRow, NumSamples + 2))
        .FormulaR1C1 = "=INDEX(RC3:RC32,RANDBETWEEN(1,30))"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub
I appreciated it a lot. Thank you so MUCH Hans!

Sam