Excel If/Then's

  • Thread starter Thread starter Brandon.Galindo
  • Start date Start date
B

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

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

Thanks, Brandon
 
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
 
Back
Top