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.
Thanks in advance

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

Would this work instead?

=ROUNDDOWN(A1,-2)/100

--
HTH :)
Dana DeLouis


"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.
Thanks in advance

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.
How about: =CEILING(A1,100)/100

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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.
Thanks in advance

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


"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.
Thanks in advance

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


"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.
Thanks in advance

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

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.
Thanks in advance



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 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.
Thanks in advance

Previous Posts In This Thread:

On Saturday, March 01, 2008 10:08 AM
monisankar_dutt wrote:

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 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.
Thanks in advance

On Saturday, March 01, 2008 10:23 AM
Dana DeLouis wrote:

Would this work instead?
Would this work instead?

=ROUNDDOWN(A1,-2)/100

--
HTH :)
Dana DeLouis

On Saturday, March 01, 2008 10:24 AM
Bernard Liengme wrote:

First, why the quotes?
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.
How about: =CEILING(A1,100)/100

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Montu" wrote in message
...

On Saturday, March 01, 2008 10:31 AM
Sandy Mann wrote:

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)))

--
HTH

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


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


"Montu" wrote in message
...

On Saturday, March 01, 2008 10:33 AM
Jim Cone wrote:

use more than 7 if condition in a cell
=IF(A1=0,"0",INT(A1/100) +1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"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.
Thanks in advance

On Saturday, October 11, 2008 5:15 PM
Harry Hockenlocker wrote:

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

On Tuesday, February 24, 2009 9:36 AM
partha sarathi sahoo sahoo wrote:

if condition
more than 9 if condition not calculate


Submitted via EggHeadCafe - Software Developer Portal of Choice
Working with Compressed Resources in .NET
http://www.eggheadcafe.com/tutorials...pressed-r.aspx

Hi Rajeev,

Not sure what you are asking, since you seem to have already received
answers to your post.

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

On Sunday, October 12, 2008 1:15:30 AM UTC+4, Harry Hockenlocker wrote:
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