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

Dana DeLouis

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

Would this work instead?

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

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

(e-mail address removed)
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
(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
 
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.
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 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.
Thanks in advance

Previous Posts In This Thread:

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

Would this work instead?
Would this work instead

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

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

(e-mail address removed)
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
(Excel Add-ins / Excel Programming

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

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
http://www.eggheadcafe.com/tutorial...0-c314ccfc92e8/working-with-compressed-r.aspx
 
E

Ed Ferrero

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
 
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
Click to expand...

thnks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Uneven column lookup 4
return blank cell if condition met 2
Referencing the values of multiple cells in conditinal formatting. 0
Sum of Variable Cells in a fixed Row 3
Formula for percentage calculation 1
Count the remaining weekdays in a data range excluding holidays and start/end dates 5
sorting data in another sheet 2
MORE THAN 8 IF CONDITION IN A FORMULA IS NOT ACCEPTING 3

Top