Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noelline - 15 Oct 2007 17:18 GMT

Hello all, I need your help, I am working with large excel data and I need a
formula so that I can generate a random selection every 70 rows, what kind of
formula do I need and how do I integrate it in my worksheet? All answers are
welcome.

Thanks

Signature

Reply to this Message

JE McGimpsey - 15 Oct 2007 18:34 GMT

So do you need a random selection:

http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html

or a sample every seventy rows? Or a random selection from each 70 rows.

J1: =INDEX($A:$A,(ROW()-1)*70+1)

copy down as far as necessary...

or one sample from each 70 rows:

J1: =INDEX($A:$A,(ROW()-1)*70 + INT(RAND()*70)+1)
copy down...

> Hello all, I need your help, I am working with large excel data and I need a
> formula so that I can generate a random selection every 70 rows, what kind of
> formula do I need and how do I integrate it in my worksheet? All answers are
> welcome.

Reply to this Message

Noelline - 16 Oct 2007 19:37 GMT

Hi, thank you for your help but I am a real dummy when it comes to formulas
so I cannot get this to work, I keep getting an error message, how do I
correct it?

Thanks again,

Signature

> So do you need a random selection:
>
[quoted text clipped - 15 lines]
> > formula do I need and how do I integrate it in my worksheet? All answers are
> > welcome.

Reply to this Message

JE McGimpsey - 16 Oct 2007 21:14 GMT

I happen to be a real dummy at mind reading... so I'm unsure what error
message you received. Thus I don't have a clue how to correct it.

> Hi, thank you for your help but I am a real dummy when it comes to formulas
> so I cannot get this to work, I keep getting an error message, how do I
> correct it?

Reply to this Message

Bernie Deitrick - 15 Oct 2007 18:34 GMT

NR,

You could use two columns of formulas. Let's say that you have two blank columns, B and C. In cell
B2, enter the formula

=RAND()

and copy this formula down to match your entire data set.

Then in cell C2, enter the formula

=IF(B2=MAX(OFFSET($B$1,INT((ROW()-ROW($A$2))/70)*70+1,0,70,1)),"Select me","")

and copy that down.

Then apply a filter, and show just the "Select me" values in column C.

If you need to do something with the selection, apply the filter, select the column(s) with the data
that you need, use Edit / Goto.. / Special... "Visible Cells only" "OK" and then copy and paste the
selection somewhere else.

HTH,
Bernie
MS Excel MVP

Reply to this Message

Noelline - 16 Oct 2007 19:35 GMT

I tried these formulas and I keep getting an error message, how do I fix it.
I have never worked with formulas so all of this is brand new to me.

Thanks for you help,

Signature

> NR,
>
[quoted text clipped - 27 lines]
> >
> > Thanks

Reply to this Message

David Biddulph - 16 Oct 2007 20:29 GMT

If we have to guess what the error message is, then you might have to guess
what answer we *would* have given if we had known what the error message
was.

The one bit of advice we can give without knowing what error message you are
getting is please don't try to retype the formula. Copy it from here to the
formula bar. the formula as given shouldn't give an error message. The
only thing that might is if your list separator character is set not to a
comma but to a semi-colon; if that is the case, you can change the commas
in the formulae to semi-colons, or you can change the separator in your
Windows Regional Options.

Signature

David Biddulph

>I tried these formulas and I keep getting an error message, how do I fix
>it.
[quoted text clipped - 40 lines]
>> >
>> > Thanks

Reply to this Message

Noelline - 31 Oct 2007 13:26 GMT

Thanks, it worked very well.

Signature

> NR,
>
[quoted text clipped - 27 lines]
> >
> > Thanks

Reply to this Message