Using IF > or < 0, but not = 0 1

[bmacbmac]

Hi I have a column with positive, negative, and 0 numbers in Column A:

1
1
0
-1
-1
1
0
2
-2
0

I would like column B to say "Too Many" with positive number, "Missing" with negative numbers, and not do anything if the number = 0, like this:

ColA ColB
1 Too Many
1 Too Many
0
-1 Missing
-1 Missing
1 Too Many
0
2 Too Many
-2 Missing
0

Right now I'm using:

=IF(A1>0, "Too Many", "Missing")

Is there a way I can ignore the 0's? Right now it's treating 0 as FALSE.

 

[SkipVought]

Hi,

Nest.
[tt]
=IF(A1>0, "Too Many", if(A1<0,"Missing","") )
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bmacbmac]

Perfect Thanks! Didn't know I could use If multiple times.

 

[straybullet]

I think I remember Excel 2K limiting you to 7 nestings of 'If' - not sure if this applies to 2007 though...

Let them hate - so long as they fear... Lucius Accius

 

[xlbo]

Yup but if you have > 7 nested IFs then you are using the wrong functions!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

I totally agree with Geoff.

But just for the sake of being thorough, one can get around the limit of 7 nested IFs by joining together multiple sections of nested IFs.

[tab]Example:
=If(A1 < 0, 0, If(A1 < 10, 1, ...If(A1 < 70, 7[red], 0[/red]))))))) [red]+ If[/red](A1 < 80, 8, If(A1 < 90, 9 ...

If dealing with strings instead of numbers, use an ampersand (&) instead of plus (+).

To reiterate, that is not the best, easiest or shortest way to get those results. But it is possible. Bear in mind that just because you can doesn't mean you should.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lionelhill]

you can also get round it by splitting it into several columns, so column B contains the result if one of the first 6 ifs is appropriate, or returns a blank if none matches. Column C does the same for the next 6 ifs. And column D is equal to whichever is not blank (e.g. =if(B1<>"",B1,C1).

I prefer big things split over several cells, even if it means I eventually hide the "intermediate" columns.

 

[strongm]

One other alternative is to use a custom cell format

Create a custom numerical format of:

"Too Many";"Missing";""

Copy colum A to Column B, and apply the custom format

 

[Gavona]

stongm - that creativity deserved a star in my book!


Gavin