Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
On Fri, 30 Sep 2005 22:08:45 -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 There is a nesting limit of 7. Your last RAND() exceeds that. --ron |
#3
|
|||
|
|||
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" 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),prec ious,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),or namental,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 |
#4
|
|||
|
|||
Thank a lot Myrna, it work
"Myrna Larson" wrote in message ... 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" 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),prec ious,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),or namental,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom function returning VALUE error | Excel Discussion (Misc queries) | |||
@ and "function not valid" error (Excel 2003) | New Users to Excel | |||
#Name? Error in User Defined VB Function | Excel Worksheet Functions | |||
Nested IF AND OR function Inexplicable error | Excel Worksheet Functions | |||
Why do I get a #Name error when I use the DateDif function? | Excel Worksheet Functions |