Function giving error

[Martin]

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

[Ron Rosenfeld]

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
>
>

There is a nesting limit of 7. Your last RAND() exceeds that.
--ron

[Myrna Larson]

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
>
>

[Martin]

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
>>
>>