Choosing a random entry from a range, ignoring errors and certain values?

[fishoutofwater]

I have a range A1:D40. There are all kinds of pieces of data in the range including: #n/a, 0, ....

In E1, I want to pick something random from the range, as long as it meets these criterion:

(1) LEN(ofpick)>5
(2) ISERROR(ofpick)= False

In other words if I have this:

A...................B.....................C
RiceMeal..........0....................#N/A
Oatmeal..........OhNo...............#N/A
Pumpkins.........#N/A...............Ice
#N/A..............Kittens.............Dog
#N/A..............22...................0
0...................Happiness.........BigMac
Soda..............Sunshine..........Sledding

I want to have some sort of command that looks like this:
RAND(A1:C7) IF cell has text lenght > 5
So, I'd want E1 to give me something random from:

RiceMeal
Oatmeal
Pumpkins
Happiness
Sunshine
BigMac
Sledding

since every other entry in the range is either too shart LEN or Is an #N/A error.

Is this possible?

ps. For some reason I only have RAND with my version of excel, so another constraint here is that I cannot use RANDBETWEEN

 

[T. Valko]

YAY!!!! HOORAY!!!!! IT WORKS IT WORK IT WORKS!!!!

THANKS BIFF!!

You have no idea how deeply, how totally, how sincerely I appreciate this help! Getting to make my idea go from just stuff in my head to a real excel thing that works is just beyod words... and something I'd never be able to do without the occasional help from you all here on mrexcel when things are over my head.

THANKS!!!!

You're welcome. Thanks for the feedback!

 

[martindwilson]

where has your randbetween gone, its there just activate the analysis tool pack

 

[mikerickson]

Here's my attempt at the single column case, using named ranges dataRange (e.g. A2:A10) and dataCol (the whole column A:A)

=INDEX(dataCol, LARGE(IF(LEN(dataRange)>5,ROW(dataRange)), RANDBETWEEN(1,SUMPRODUCT(--(LEN(dataRange)>5)))),1)