Ask Your Question
0

Random cell selection [closed]

asked 2012-07-03 10:33:45 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hi I have a range of 6 differing short texts I want to have displayed at random in response to an input. Easy to show just the one text, but I want to not always have the same predictable result. For a single line =IF (A1>0,D3) works fine, but I have 5 alternative responses at D4 to D9 I would like each of the 5 alternate texts randomly used instead of just the one at D3 every time.. I think I have to use the Rand function, but dont quite know how go about it.. any ideas please..

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 00:28:13.091614

2 Answers

Sort by » oldest newest most voted
1

answered 2012-07-06 01:23:15 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hello.. Try this

=IF(A1>0,OFFSET(D4,INT(RAND()*5),0),"")

Works here.

edit flag offensive delete link more
1

answered 2012-07-06 08:19:48 +0200

JohnSUN gravatar image

You can also try other variants:

=IF(A1>0;INDIRECT("D"&(3+RANDBETWEEN(1;6)));"No variants")
=IF(A1>0;INDEX(D4:D9;RANDBETWEEN(1;COUNTA(D4:D9));1);"")
edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-07-03 10:33:45 +0200

Seen: 1,695 times

Last updated: Jul 06 '12