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(R OUND(RAND()*100,0),gem,2),IF(B20="Jewels",VLOOKUP( ROUND(RAND()*100,0),jewel,2),IF(B20="Ornamental",V LOOKUP(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" 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(RAN D()*100,0),fancy,2),IF(B20="Precious",VLOOKUP(ROUN D(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" 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(RAN D()*100,0),fancy,2),IF(B20="Precious",VLOOKUP(ROUN D(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" 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(RAN D()*100,0),fancy,2),IF(B20="Precious",VLOOKUP(ROUN D(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 |
All times are GMT +1. The time now is 11:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com