Number of Mid Functions within an If sum

L

Lisa King

How many mid functions can I input into an If sum.
I need to put seven in but it will only let me put in 6.
If it won't allow me to put in any more how can I get
round it?
 
B

Bob Phillips

There could be a number of ways, but you need to give some data examples,
and a clear indication of what you want to do to get a meaningful answer.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Jerry W. Lewis

Excel is limited to 7 levels of nesting (6 nested MID's in an IF). This
is clearly documented in Help for "Excel specifications and limits"
subtopic "Calculation specifications".

Also, remember that MID returns a character string that will be ignored
by SUM, even if those characters are digits.

Jerry
 
B

Bob Phillips

Yeah, but it is simple to coerce it to a value.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Kassie

While Excel may be limited to 7 nested IF's, you are not. Merely split your
argument, and continue in the next column. Eg in D5 enter
=If(arg1,1,if(arg2,2,If(arg3,3,if(arg4,arg4,if(arg5,arg5,if(arg6,arg6,7)))))).
In E5, type =If(D5<7,D5,if(arg7,7, If(arg8,8,..... etc. I have in this way
nested up to 23 arguments. Just plan your arguments and splits carefully
 
Top