Create and decode Google Sheets IF statements with a brand new builder

Before you start

Text case sensitivity

Almost all IF Formula Builder operators are case-insensitive. They do not take text case into account. Words like east and East are treated as the same.

The only exceptions are 2 operators: is exactly and is exactly not. They consider the text case.

Formula recognition

Though the add-on recognizes existing IF formulas and breaks them down to pieces, we highly recommend using this feature only on formulas created by the tool.

You see, the same IF-THEN logic can be formulated in different ways with a different order of the arguments. But our add-on recognizes only those formula patterns that we put into the code and that are used to build IF statements. Since we can't foresee them all, this feature will be most useful when applied to the formulas build by the tool only.

Another thing to keep in mind is that the add-on recognizes operators based on the types of data in cells. If the formula references blank cells, the add-on will have difficulties understanding the formula.

How to use IF Formula Builder

Start the add-on

Go to the Extensions menu in Google Sheets, find IF Formula Builder in the list of your tools and click Start:
Run the IF Formula Builder on Google Sheets.

How to create IF THEN formulas in Google Sheets

Once the add-on opens, it will invite you to start threading your IF statements for Google Sheets:
Build Google Sheets IF formulas using the add-on.

Pick a cell where you want to see the resulting formula.
By default, the add-on picks up your currently selected cell. To change it, do one of the following:
- Type a required cell reference manually.
- Click in this Source range field to place your cursor there and then select any other cell in the sheet. The add-on will pick that another cell.
- Click the Select range icon within the field to specify a cell for the formula from there:
  
  Here you can also either enter the reference manually or pick a cell on the sheet.
This Preview formula field is like a formula bar in Google Sheets. It shows the outcome formula based on all the info you enter to the formula.
Tip. You can copy the formula from this Preview any time and paste it someplace else.
Now you are to start setting up your IF formula. The first part is the criterion itself – If. It is that logical expression that will be tested in your sheet.
When you enter your records, the add-on autodetects their types (formats). Each format is represented by a special icon to the right of the record.
To change it, click the Data type button and pick the format from a drop-down list:
Pick the comparison operator that suits your condition in the best way:

All operators are grouped according to the data type: Common, Value (numerics), Text, Date or Time, Boolean.

Note. The operator called is (is equal to) is different for Numeric values, Text, and Date or Time.

To invert all operators, e.g. switch is to is not or turn is empty to is not empty, toggle the corresponding Invert button at the top of the list.
If there's an excess part of the formula, you can remove it by clicking the Delete icon.
To add more logical expressions and their possible outcomes, click the Plus icon instead.
By adding more fields to your logical expression, you can specify several criteria to test against:
- AND will check if all conditions are true at the same time.
- OR – if at least one of them is met.
THEN is whatever you need to return when your logical expression is true.
Tip. Turn THEN into THEN IF to continue adding more conditions and values to return for them:
The value for ELSE will be returned if your logical expression is false.
Tip. To nest even more IF expressions, select ELSE IF. More fields will appear for you to enter more conditions and corresponding values for when they are met or not:

How to quickly edit existing IF formulas

The IF Formula Builder can break down the existing Google Sheets IF formulas to pieces:

isolate each logical expression
separate expected results
distinguish between values and operators

All these can be easily edited and pasted back to the formula.

Just select the cell with an IF formula in it and run the tool. All parts will be shown as if you created this formula:
Decode existing Google Sheets IF formulas.

Note. Though the add-on recognizes existing IF formulas and breaks them down to pieces, we highly recommend using this feature only on formulas created by the tool.

Responses

Robz says:

March 5, 2022 at 8:29 am

This used to be so helpful.

Now it is completely useless. It doesn't even open on Chrome. On safari it opens and then I'm not even able to select where I want to see the resulting formula (always get the error "the entered range is incorrect)

Katerina Bespalaya (Ablebits Team) says:

March 5, 2022 at 5:47 pm

Hello Rob,

Thank you for your comment. Sorry to hear that you are having difficulties with our add-on.

Please note that there is currently an issue on the side of Google that occurs when you are logged in under more than one account. I kindly ask you to sign out and log back in only under the account you used to grant permissions to the add-on and try to reproduce the issue.

If this doesn't help, then please specify the version of Google Chrome you use and send us a short video or a few screenshots showing what happens when you open the add-on. As for Safari, please also specify the browser version and send us a video or screenshots illustrating the problem you have there. You can email all the details to support@ablebits.com.

We will look into the issue and try to help.

Lisa says:

February 27, 2022 at 10:12 am

NEED IMMEDIATE HELP

I have 2 columns of check boxes - Column A and Column B
In Column C are reference numbers
When Column A and B are true - I want Column D to indicate the reference number from Column C in list order AS BOTH A & B ARE TRUE. NOT ON THE SAME ROW. .

Example:
A2 AND B2 = C2 REF# CT99999X
A5 AND B5 = C5 REF# CT11111X
A10 AND B10 = C10 REF# CT55555X

A10 and B10 are true before A2 and B2 and A5 and B5. Then A5 and B5 becomes true next, then A2 and B2 becomes true next - THEREFORE IN COLUMN D SHOULD LIST THE REFERENCE NUMBRS IN ORDER AS BOTH A AND B MATCH ARE TRUE
1) A2=CT55555X (READY FIRST)
2) A3=CT11111X (READY SECOND)
3) A4=CT99999X (READY THIRD)

Ekaterina Pechyonkina (Ablebits Team) says:

February 27, 2022 at 10:40 am

Hello Lisa,

Thank you for the detailed description. For us to be able to help you better, please share your sample spreadsheet with us at support@apps4gs.com. To grant us access to your data, press the Share button at the upper right corner of Google Sheets and enter support@apps4gs.com.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this email.
If you have confidential information there, you can replace it with some irrelevant data, just keep the format. We will do our best to help.

Jason Weaver says:

October 7, 2021 at 9:03 pm

How do you "reset" the IF Formula Builder to start fresh?

Ekaterina Pechyonkina (Ablebits Team) says:

October 11, 2021 at 10:58 am

Hi Jason,

If you need to delete a sketch of some function you've entered into the add-on's window, then you can use the Undo button at the bottom of the pane or simply restart the tool. In case the 180-day trial has expired, I'm afraid the only option is to buy a subscription. Thank you.