Thank a lot Myrna, it work



"Myrna Larson" <anonymous@discussions.microsoft.com> wrote in message
news:3atrj112mnsc2fel82m9o4je4uuodtirp0@4ax.com...
> You can only have 7 nested IF statements, but I believe you have not hit
> that
> limit.
>
> If you can change the name of the Gem and Jewel ranges to Gems and Jewels,
> respectively, you can shorten your formula (considerably!) with this
> modification:
>
> =VLOOKUP(ROUND(RAND()*100,0),INDIRECT(B20),2)
>
> Since you are not using an exact match in your VLOOKUP formulas, you can
> probably eliminate the ROUND function altogether, though that depends on
> what
> you have in the first column of your tables.
>
> =VLOOKUP(RAND()*100,INDIRECT(B20),2)
>
> On Fri, 30 Sep 2005 22:08:45 -0400, "Martin" <rpg_mfa@sympatico.ca> wrote:
>
>>Hi,
>>
>>I am Using Office XP and i am writing a function in Excel that is giving
>>me
>>an error "The formula you type contains an error...."
>>
>>here is my function
>>
>>=IF(B20="Semiprecious",VLOOKUP(ROUND(RAND()*100,0),semiprecious,2),
> IF(B20="Fancy",VLOOKUP(ROUND(RAND()*100,0),fancy,2),
> IF(B20="Precious",VLOOKUP(ROUND(RAND()*100,0),precious,2),
> IF(B20="Gems",VLOOKUP(ROUND(RAND()*100,0),gem,2),
> IF(B20="Jewels",VLOOKUP(ROUND(RAND()*100,0),jewel,2),
> IF(B20="Ornamental",VLOOKUP(ROUND(RAND()*100,0),ornamental,2)
> ))))))
>>
>>
>>The problem seem to be the last round(rand... function.
>>If i remove the last ROUND it work fine but when i put it in i am getting
>>the error. Is there a limitation with the number of function you can have
>>in
>>a single cell ?
>>
>>
>>Any help greatly appreciated
>>
>>
>>Martin
>>
>>