Nesting greater than 7 levels with IF function

jmschiff

New Member
Joined
Oct 10, 2002
Messages
1
I would like to generate an entire page of randomly chosen letters (one letter in each cell) I have tried to use a combination of the IF and RAND functions however I am limited to 7 nestings, as opposed to 26 for each letter. Can someone suggest a better method to do this which falls under the nesting limit.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
To get round this limit use select case:

select case MYLETTER
case "a"
do something
case "b"
do something
case else
do someting
end select

you can go on here for ages!

I posted something about this earlier this week with some example code as well

Hope this helps

Chris
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Put letters down a column say A1:A26.
Put =RAND() in the next empty column. Say B1
Copy or FillDn
Convert the random numbers to constants:
copy (ctrl+c), edit, paste special, values
Sort on the random numbers column
Delete the random numbers column.
 
L

Legacy 98055

Guest
Hi
=CHAR(RANDBETWEEN(65,90))
will generate random, uppercase letters.
=LOWER(CHAR(RANDBETWEEN(65,90)))
for lower case.

Tom

This will randomly select Upper or Lower case.
=IF(RANDBETWEEN(1,2) = 1,LOWER(CHAR(RANDBETWEEN(65,90))),CHAR(RANDBETWEEN(65,90)))
This message was edited by TsTom on 2002-10-11 12:37
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

You could try:

=CHAR(RANDBETWEEN(65,90))
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
Ummm read the post again and seems I've not understood the question correctly.

How about some VBA

set rng = Range("A1:f10")
for each c in rng
c.value = asc(rnd(26)+64)
next

I've not tried it but it should work.

HTH

Chris
 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
566
Office Version
  1. 365
  2. 2016
there has been many postings re limit of 7 nestings..you can get around this..altough I am usually humbled by Aladins solutions.....but...you can get around 7 nestings by naming your nest as a formula...eg if test1=...then test2..with test1 as 6 ifs..if you know what I mean..

regards chef
 

Forum statistics

Threads
1,144,329
Messages
5,723,732
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top