Excel If/Then's

[Brandon.Galindo]

I have three columns that will be populated with a positive, a
negative or the number zero. How do I populate a fourth column with
"remove" once the three previous columns have been filled with
something? I know I probably can use an if then statement but I am
not for sure what the text will be since the numbers can be pos, neg,
or equal?

Brandon Galindo

 

[Guest]

Assume the three cells in question are A1, B1, and C1. Then
=IF(NOT(OR(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))),"remove","don't remove")
will return "remove" only if each of A1, B1, and C1 are populated.
Alternatively, if you want to test for the presence of numbers (as opposed to
any value), use this modification:
=IF(OR(ISNUMBER(A1),ISNUMBER(B1),ISNUMBER(C1)),"remove","don't remove")

Dave

 

[Guest]

Here's a more efficient version: =IF(--(NOT(ISBLANK(A1:C1))),"remove","don't
remove")

Or if you want to test for numbers: =IF(--(ISNUMBER(A1:C1)),"remove","don't
remove")

Dave

 

[Roger Govier]

Hi

You could use
=COUNT(A1:C1)=3
This would return TRUE where there are numbers in all three columns, and
FALSE where there isn't

If you only wanted to show the True rows then
=IF(COUNT(A1:C1)=3,"Remove","")

 

[Brandon.Galindo]

Here's a more efficient version: =IF(--(NOT(ISBLANK(A1:C1))),"remove","don't
remove")

Or if you want to test for numbers: =IF(--(ISNUMBER(A1:C1)),"remove","don't
remove")

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.





what does the -- represent? Just for my clarifiacation?

- Show quoted text -

 

[Brandon.Galindo]

Here's a more efficient version: =IF(--(NOT(ISBLANK(A1:C1))),"remove","don't
remove")

Or if you want to test for numbers: =IF(--(ISNUMBER(A1:C1)),"remove","don't
remove")

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.







- Show quoted text -

Hi Dave thanks for your response but what does the -- represent? Just
for my reference.

Thanks, Brandon

 

[Guest]

Hi, the -- coerces TRUE or FALSE values into 1s and 0s, respectively.

So if the following formula finds 3 1s, it returns "remove":
=IF(--(NOT(ISBLANK(A1:C1))),"remove","don't remove")

In other words, if A1, B1, and C1 ALL have values in them, it is NOT true
that any of them are blank. Therefore, it is TRUE that each is NOT blank.
So, three 1s are returned. Finally, that tells Excel to return "remove".

Dave