# use more than 7 if condition in a cell

M

#### Montu

Hi,

I'm getting problem to use more than 7 if condition in a cell in excel 2003,
in there I can input only 7 if condition like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7","Out of Range")))))))
but I want more condition to be input like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7",if(a1<800,"8",if(a1<900,"9",......to
be continued the end of require,"Out of Range"))))))))))))).....
is there any solution for the above problem.

D

#### Dana DeLouis

=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(...etc

=ROUNDDOWN(A1,-2)/100

B

#### Bernard Liengme

First, why the quotes? Why not
=if(a1=0,0,if(a1<100,1,if(a1<200,2,.......

Second, except in Excel 2007, if more than 7 conditions are needed you have
to look for another solution.

best wishes

S

#### Sandy Mann

Does:

=INT(CEILING((A1/100),1))

do what you want or do you want "Out of Range to show as well:

=IF(A1<100000,"Out of range",INT(CEILING((A1/100),1)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

J

#### Jim Cone

=IF(A1=0,"0",INT(A1/100) +1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Montu"
wrote in message
Hi,
I'm getting problem to use more than 7 if condition in a cell in excel 2003,
in there I can input only 7 if condition like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7","Out of Range")))))))
but I want more condition to be input like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7",if(a1<800,"8",if(a1<900,"9",......to
be continued the end of require,"Out of Range"))))))))))))).....
is there any solution for the above problem.

H

#### Harry Hockenlocker

The solutions to the 7 issue is available out here, however have you considered just using the INT formula in the cell/cells where you want the 1 or 2 or x etc?

=INT(A1/100) in any cell other than A1 will result in the number 1, assuming cell A1 holds any number from 100 to 199, etc.

Example: cell A1 has the number 257
if cell A2 has the formula =INT(A1/100)
the result in A2 will be the whole number 2

R

#### Rajeev Kumar

Hi,

I'm getting problem to use more than 7 if condition in a cell in excel 2003,
in there I can input only 7 if condition like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7","Out of Range")))))))
but I want more condition to be input like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7",if(a1<800,"8",if(a1<900,"9",......to
be continued the end of require,"Out of Range"))))))))))))).....
is there any solution for the above problem.

monisankar_dutt wrote:

use more than 7 if condition in a cell
01-Mar-08

Hi

I'm getting problem to use more than 7 if condition in a cell in excel 2003,
in there I can input only 7 if condition like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7","Out of Range"))))))
but I want more condition to be input lik
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7",if(a1<800,"8",if(a1<900,"9",......to
be continued the end of require,"Out of Range")))))))))))))....
is there any solution for the above problem.

use more than 7 if condition in a cell
Hi

I'm getting problem to use more than 7 if condition in a cell in excel 2003,
in there I can input only 7 if condition like
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7","Out of Range"))))))
but I want more condition to be input lik
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7",if(a1<800,"8",if(a1<900,"9",......to
be continued the end of require,"Out of Range")))))))))))))....
is there any solution for the above problem.

=ROUNDDOWN(A1,-2)/10

-
HTH :>
Dana DeLouis

First, why the quotes?
First, why the quotes? Why no
=if(a1=0,0,if(a1<100,1,if(a1<200,2,......

Second, except in Excel 2007, if more than 7 conditions are needed you have
to look for another solution

best wishe
--
Bernard V Liengm
Microsoft Excel MV
http://people.stfx.ca/bliengm
remove caps from emai

Re: use more than 7 if condition in a cell
Does

=INT(CEILING((A1/100),1)

do what you want or do you want "Out of Range to show as well

=IF(A1<100000,"Out of range",INT(CEILING((A1/100),1))

--
HT

Sand
In Perth, the ancient capital of Scotlan
and the crowning place of king

Replace @mailinator.com with @tiscali.co.u

Re: use more than 7 if condition in a cell
=IF(A1=0,"0",INT(A1/100) +1
--
Jim Con
San Francisco, US
http://www.realezsites.com/bus/primitivesoftwar

"Montu
wrote in messag
Hi
I'm getting problem to use more than 7 if condition in a cell in excel 2003
in there I can input only 7 if condition lik
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7","Out of Range"))))))
but I want more condition to be input lik
=if(a1=0,"0",if(a1<100,"1",if(a1<200,"2",if(a1<300,"3",if(a1<400,"4",if(a1<500,"5",if(a1<600,"6",if(a1<700,"7",if(a1<800,"8",if(a1<900,"9",......t
be continued the end of require,"Out of Range")))))))))))))....
is there any solution for the above problem

use more than 7 if condition in a cell - monisankar_dutt
The solutions to the 7 issue is available out here, however have you considered just using the INT formula in the cell/cells where you want the 1 or 2 or x etc?

=INT(A1/100) in any cell other than A1 will result in the number 1, assuming cell A1 holds any number from 100 to 199, etc.

Example: cell A1 has the number 257
if cell A2 has the formula =INT(A1/100)
the result in A2 will be the whole number 2

if condition
more than 9 if condition not calculate

Submitted via EggHeadCafe - Software Developer Portal of Choice
Working with Compressed Resources in .NET

E

#### Ed Ferrero

Hi Rajeev,

To summarise;

You can't use more than 7 nested IF statements unless you upgrade to Excel
2007.

There are some simple formulas to do what you are specifically asking
eg =IF(C6=0,0,INT(C6/100)+1)
or =CEILING(A1,100)/100

For a more general case, I would not use long nested IF statements. Consider
using the CHOOSE function - like this
=IF(C6=0,0,CHOOSE(INT((C6)/100)+1,1,2,3,4,5,6,7,8,9,10))

Ed Ferrero
www.edferrero.com

S

#### sumesh.rmy

The solutions to the 7 issue is available out here, however have you considered just using the INT formula in the cell/cells where you want the 1 or 2 or x etc?

=INT(A1/100) in any cell other than A1 will result in the number 1, assuming cell A1 holds any number from 100 to 199, etc.

Example: cell A1 has the number 257
if cell A2 has the formula =INT(A1/100)
the result in A2 will be the whole number 2

thnks