Clever Geek Handbook

Excel function "ISBLANK" With function "IF"

I have multiple Excel workbooks that have many columns. There will be an English column and a Spanish column. What I am trying to do is compare the two columns to see if the Spanish column actually translated the text from English. It's easy enough:

IF(A2=C2, "NOT TRANSLATED", "TRANSLATED")

However, there are hundreds of cells that simply have no meaning in either the English or Spanish column. So the formula I used above would say "Yes, they are technically the same value, even if they are zero, so no translation was done." This gives him the impression that there are too many NON-TRANSLATION meanings than there actually are. This is what I tried:

IF(A2=C2, "NOT TRANSLATED", "TRANSLATED", IF(ISBLANK(A2)=TRUE, "NULL VALUE", "CHECK VALUE"))

It gives me an error though.

I think I need to have a function that first checks if a cell has a value to start with. If so, then the formula should go to validation to see if the two cells have the same values ​​or not. If any cell is unimportant, it should return "NULL VALUE". Any ideas?

+3


source to share


4 answers


Wooo !!! With a little help from you guys, I get it. It looks like this:

=IF(AND(ISBLANK(A2)=TRUE; ISBLANK(B2)=TRUE); "NULL"; IF(OR(ISBLANK(A2)=TRUE; ISBLANK(B2)=TRUE); "NOT TRANSLATED"; IF(A2=B2; "NOT TRANSLATED"; "TRANSLATED")))


It was a monster.

+1


source


You effectively provide the parameters to IF

function 4 with this second formula. IF

takes at least 1 required parameter (condition) followed by 2 optional parameters (what to do if it returns TRUE or FALSE).

Instead, you need to nest conditions IF

in something like:

IF(A2=C2, IF(ISBLANK(A2)=TRUE, "NULL VALUE", "CHECK VALUE"), "TRANSLATED")


Which checks if A2 = C2 is, if it then checks if A2 is empty if it (and A2 = C2 remembers) then it returns "NULL VALUE" if it is not (but A2 is still C2), then it returns "CHECK VALUE" and if A2 is not equal to C2 then it returns "TRANSLATE".

Obviously, you can customize it for your problem.

+1


source


You may find this formula helpful, it will check for null value in column

{=IF(OR(A1:B1=""),"Null Value",IF(A1=B1,"Not Translated","Translated"))}

Leave the curly braces and enter the function using Ctrl+ Shift+Enter

You can drag this down for the following results

a   a   Not Translated
b   c   Translated
d       Null Value
    e   Null Value
        Null Value
f   f   Not Translated
+1


source


Try:

=IF(OR(LEN(A2)=0,LEN(C2)=0),"IS NULL",IF(A2=C2, "NOT TRANSLATED", "TRANSLATED"))
0


source






More articles:


All Articles