| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
How many similar functions can be put in one cell?
I have a formula in one cell with several functions used multiple times. It
seems though that 25 times is the max for the Round function before it blows up. Why? |
|
#2
|
|||
|
|||
|
How many similar functions can be put in one cell?
Hi!
What's the formula look like? What does "before it blows up" mean? Biff "Forrest" wrote in message ... I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? |
|
#3
|
|||
|
|||
|
How many similar functions can be put in one cell?
It looks like this:
=INT(ROUND(A28*3.2808,2))&"' - "&TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12)&" "&IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=1,"1/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=2,"1/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=3,"3/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=4,"1/2",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=5,"5/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=6,"3/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=7,"7/8","")))))))&"""" What occurs is Excel says there is an error in the formula, once it gets to a certain size and highlights one of the functions. In this case it is the Round function. There isn't a logic or syntax error though. It could be a nesting problem. I have seven nested If statements, each with 3 Round functions. This equation has 59 functions, so I am guessing that there is a limit to the number in one cell. "Biff" wrote: Hi! What's the formula look like? What does "before it blows up" mean? Biff "Forrest" wrote in message ... I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? |
|
#4
|
|||
|
|||
|
How many similar functions can be put in one cell?
So, you're trying to convert a value into feet and fractions of an inch.
What is in A28 and what does 3.2808 have to do with it? That is one nasty formula, 928 characters! <g Why don't you explain exactly what you're trying to do and maybe we can come up something more manageable. Biff "Forrest" wrote in message ... It looks like this: =INT(ROUND(A28*3.2808,2))&"' - "&TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12)&" "&IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=1,"1/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=2,"1/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=3,"3/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=4,"1/2",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=5,"5/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=6,"3/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=7,"7/8","")))))))&"""" What occurs is Excel says there is an error in the formula, once it gets to a certain size and highlights one of the functions. In this case it is the Round function. There isn't a logic or syntax error though. It could be a nesting problem. I have seven nested If statements, each with 3 Round functions. This equation has 59 functions, so I am guessing that there is a limit to the number in one cell. "Biff" wrote: Hi! What's the formula look like? What does "before it blows up" mean? Biff "Forrest" wrote in message ... I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? |
|
#5
|
|||
|
|||
|
How many similar functions can be put in one cell?
Congratulations on figuring out what the equation does. 3.2808 is the
conversion from meters to feet. A28 is simply the cell with the metric length to convert. The purpose is to copy this one cell formula into any spreadsheet next to a cell with a metric length to convert. If I spilt up the equation into two cells it works fine, but that defeats my goal. "Biff" wrote: So, you're trying to convert a value into feet and fractions of an inch. What is in A28 and what does 3.2808 have to do with it? That is one nasty formula, 928 characters! <g Why don't you explain exactly what you're trying to do and maybe we can come up something more manageable. Biff "Forrest" wrote in message ... It looks like this: =INT(ROUND(A28*3.2808,2))&"' - "&TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12)&" "&IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=1,"1/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=2,"1/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=3,"3/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=4,"1/2",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=5,"5/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=6,"3/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=7,"7/8","")))))))&"""" What occurs is Excel says there is an error in the formula, once it gets to a certain size and highlights one of the functions. In this case it is the Round function. There isn't a logic or syntax error though. It could be a nesting problem. I have seven nested If statements, each with 3 Round functions. This equation has 59 functions, so I am guessing that there is a limit to the number in one cell. "Biff" wrote: Hi! What's the formula look like? What does "before it blows up" mean? Biff "Forrest" wrote in message ... I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? |
|
#6
|
|||
|
|||
|
How many similar functions can be put in one cell?
Hi Forrest
Try =INT(CONVERT(E17,"m","ft"))&" feet "&INT(MOD(CONVERT(E17,"m","ft"),1)*12)& " "&CHOOSE((ROUND(((E17*3.2808-INT(ROUND(E17*3.2808,2))) *12-TRUNC((E17*3.2808-INT(ROUND(E17*3.2808,2)))*12))/0.125,0)) ,"1/8","1/4","3/8","1/2","5/8","3/4","7/8")&" inches" I used the convert function, to go from metres to feet (I think you need the Analysis Toolpak loaded for this TollsAddinsAnalysis Toolpak). I couldn't be bothered to alter your calculation for the fractions of an inch, I just made it one calculation with a choose dependent upon its result. With 10 in E16 it returns 32 feet 9 3/4 inches. Regards Roger Govier Forrest wrote: Congratulations on figuring out what the equation does. 3.2808 is the conversion from meters to feet. A28 is simply the cell with the metric length to convert. The purpose is to copy this one cell formula into any spreadsheet next to a cell with a metric length to convert. If I spilt up the equation into two cells it works fine, but that defeats my goal. "Biff" wrote: So, you're trying to convert a value into feet and fractions of an inch. What is in A28 and what does 3.2808 have to do with it? That is one nasty formula, 928 characters! <g Why don't you explain exactly what you're trying to do and maybe we can come up something more manageable. Biff "Forrest" wrote in message ... It looks like this: =INT(ROUND(A28*3.2808,2))&"' - "&TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12)&" "&IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=1,"1/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=2,"1/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=3,"3/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=4,"1/2",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=5,"5/8",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=6,"3/4",IF((ROUND(((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12-TRUNC((A28*3.2808-INT(ROUND(A28*3.2808,2)))*12))/0.125,0))=7,"7/8","")))))))&"""" What occurs is Excel says there is an error in the formula, once it gets to a certain size and highlights one of the functions. In this case it is the Round function. There isn't a logic or syntax error though. It could be a nesting problem. I have seven nested If statements, each with 3 Round functions. This equation has 59 functions, so I am guessing that there is a limit to the number in one cell. "Biff" wrote: Hi! What's the formula look like? What does "before it blows up" mean? Biff "Forrest" wrote in message ... I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? |
|
#7
|
|||
|
|||
|
How many similar functions can be put in one cell?
On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest"
wrote: I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? The error has nothing to do with the ROUND function. You have run into the seven level function nesting limitation of Excel. What are you trying to do? Perhaps someone can suggest an shorter formula. --ron |
|
#8
|
|||
|
|||
|
How many similar functions can be put in one cell?
Can you point out at where the nesting is being exceeded? I have only seven
nested if statements. The equation converts meters into feet, inches and fractions of an inch to the nearest eighth. It works fine if I spilt it into 2 cells, but I don't want that. "Ron Rosenfeld" wrote: On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest" wrote: I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? The error has nothing to do with the ROUND function. You have run into the seven level function nesting limitation of Excel. What are you trying to do? Perhaps someone can suggest an shorter formula. --ron |
|
#9
|
|||
|
|||
|
How many similar functions can be put in one cell?
Forrest wrote...
Can you point out at where the nesting is being exceeded? I have only seven nested if statements. The equation converts meters into feet, inches and fractions of an inch to the nearest eighth. It works fine if I spilt it into 2 cells, but I don't want that. .... It's not a limitation on the number of nested IF calls, it's a limitation on the number of nested *function* calls. Your formula tries to go 10 levels deep. |
|
#10
|
|||
|
|||
|
How many similar functions can be put in one cell?
I think I found it. Since I have two orders nested within the If functions
it can't operate past the 5th If. Why doesn't Microsoft come into the 21st century and eliminate this silly nesting restriction. "Ron Rosenfeld" wrote: On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest" wrote: I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? The error has nothing to do with the ROUND function. You have run into the seven level function nesting limitation of Excel. What are you trying to do? Perhaps someone can suggest an shorter formula. --ron |
|
#11
|
|||
|
|||
|
How many similar functions can be put in one cell?
Hi Forrest
You can replace a whole heap of your IF statement with one CHOOSE. See my earlier posting, and watch out for line wraps as you copy and paste. Regards Roger Govier Forrest wrote: I think I found it. Since I have two orders nested within the If functions it can't operate past the 5th If. Why doesn't Microsoft come into the 21st century and eliminate this silly nesting restriction. "Ron Rosenfeld" wrote: On Wed, 26 Oct 2005 11:16:06 -0700, "Forrest" wrote: I have a formula in one cell with several functions used multiple times. It seems though that 25 times is the max for the Round function before it blows up. Why? The error has nothing to do with the ROUND function. You have run into the seven level function nesting limitation of Excel. What are you trying to do? Perhaps someone can suggest an shorter formula. --ron |
|
#12
|
|||
|
|||
|
How many similar functions can be put in one cell?
Forrest wrote...
I think I found it. Since I have two orders nested within the If functions it can't operate past the 5th If. Why doesn't Microsoft come into the 21st century and eliminate this silly nesting restriction. .... The good news is that Microsoft will finally raise this limit (one which has set Excel appart from other spreadsheets for decades - at the LOW end of the capability range in this regard) in the next version. |
|
#13
|
|||
|
|||
|
How many similar functions can be put in one cell?
On 27 Oct 2005 09:27:14 -0700, "Harlan Grove" wrote:
Forrest wrote... I think I found it. Since I have two orders nested within the If functions it can't operate past the 5th If. Why doesn't Microsoft come into the 21st century and eliminate this silly nesting restriction. ... The good news is that Microsoft will finally raise this limit (one which has set Excel appart from other spreadsheets for decades - at the LOW end of the capability range in this regard) in the next version. Harlan, Didn't you also point out that the problem is the parser, and that equations with more than seven nested functions that were created in other programs would work OK in Excel? --ron |
|
#14
|
|||
|
|||
|
How many similar functions can be put in one cell?
Ron Rosenfeld wrote...
.... Didn't you also point out that the problem is the parser, and that equations with more than seven nested functions that were created in other programs would work OK in Excel? Whether Excel's formula parser is the problem is speculation. However, OpenOffice accepts the OP's original formula (once commas are changed to semicolons), and gives the intended result. If the file were saved in XLS format, Excel has no problem opening the file and using/recalculating the formula as-is. Excel just can't edit it. So, yes, the OP could install OpenOffice and use it to create deeply nested formulas, save as XLS, then use (but not edit) in Excel. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I copy cell formats in functions? | Excel Worksheet Functions | |||
| Cell color based upon cell value | Excel Discussion (Misc queries) | |||
| Copying and Filling Cell With Functions | Excel Discussion (Misc queries) | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| cell format for financial functions | Excel Discussion (Misc queries) | |||
Hybrid Mode
