Resampling an array while ignoring zero values

  • Thread starter Thread starter Mozman
  • Start date Start date
M

Mozman

Hi all-

I have a problem where (for reasons I won't go into) I have an array
with zero values (the zeros need to be there). I want to resample
(with replacement) from the array to generate a new array, but if a
zero is picked I want to disregard it and pick again until I have a new
array that consists of non-zero values from the first array.

I can do this with a series of nested IF() statements. However, I am
doing this procedure sequentially many thousands of times and even with
10 worksheet pages of nested IF() statements many times a zero gets
through. This, for various reasons, messes up my simulation.

What I need is a simple way to tell excel to keep picking numbers with
replacement from an array until a non-zero value is obtained.

Any help will be greatly appreciated.

Thanks,

MM
 
I have a problem where (for reasons I won't go into) I have an array
with zero values (the zeros need to be there). I want to resample
(with replacement) from the array to generate a new array, but if a
zero is picked I want to disregard it and pick again until I have a new
array that consists of non-zero values from the first array.
Click to expand...

I'm not sure I fully understand what you want but I've had a go. I'm
assuming your 'array' is in fact a column of cells.

The following function will step down the first 20 cells in column A and
copy all the cells which are non-zero to column B starting at row 1.

Is this what you want ?

Function diff()

Set myRange1 = Range("A1:A20")

count = 1

For Each c In myRange1
if c.value > 0 then
cells(count,3) = c.value
end if
count+count+1
Next

End Function
 
Assuming your "array" is numerical data in a range of cells, and this range
of cells defined as "rng", use the following formula to choose a random
datum (with replacement) from your range:

=SMALL(IF(rng<>0,rng,MAX(rng)+1),1+ROUND(RAND()*(SUM(IF(rng<>0,1,0))-1),0))

The above is an array formula: You will need to hit Ctrl-Shift-Enter instead
of hitting Enter after typing (or pasting) in the formula.

Since RAND() is a volatile formula, it will resample each time any change is
made to the worksheet.

/i.
 
=SMALL(IF(rng<>0,rng,MAX(rng)+1),1+ROUND(RAND()*(SUM(IF(rng<>0,1,0))-1),0))

This works beautifully! Thank you!



Mozma
 
Back
Top