Function giving Error

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

On Fri, 30 Sep 2005 22:06:56 -0400, "Martin" <[email protected]> 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 ?
>
>
>


--ron

On Fri, 30 Sep 2005 22:06:56 -0400, "Martin" <[email protected]> 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
>

You may find all of the limits by typing 'specifications' into the HELP box.

In your case, the RAND() function exceeds the seven limit number of nested
functions.


--ron

I replied to your posting in the misc newsgroup. Please don't cross-post or
multi-post. The people who answer questions here ordinarily read all of the
groups.

On Fri, 30 Sep 2005 22:06:56 -0400, "Martin" <[email protected]> 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
>