Forum Discussion
If function
Hi, I want to make an IF function that the data is inputted from 3 different columns. Column A is a combo box of text data. Column B is a numeral numbers that has 3 different ranges. Column C is a...
Alternatively, IFS statement instead of nested IF. Also you can apply it to a range of inputs and have it 'SPILL' all the results using 1 formula:
=IFS(A1:A100="water", "not allowed", (A1:A100="apple")*(B1:B100=2)*C1:C100, "cashier A", [next expression], [next result], .... , TRUE, "no result found")
OR better yet define it as a table (Home -> 'Format as Table') and then use the Table references. So if Column A header is "Item", Column B is "Price" and Column C is "Taxable" then:
=IFS([@Item]="water", "not allowed", ([@Item]="apple")*([@Price]=2)*[@Taxable], "cashier A", [next expression], [next result], .... , TRUE, "no result found")
and this formula will auto populate each new line as you add to the table.
=IFS(A1:A100="water", "not allowed", (A1:A100="apple")*(B1:B100=2)*C1:C100, "cashier A", [next expression], [next result], .... , TRUE, "no result found")
OR better yet define it as a table (Home -> 'Format as Table') and then use the Table references. So if Column A header is "Item", Column B is "Price" and Column C is "Taxable" then:
=IFS([@Item]="water", "not allowed", ([@Item]="apple")*([@Price]=2)*[@Taxable], "cashier A", [next expression], [next result], .... , TRUE, "no result found")
and this formula will auto populate each new line as you add to the table.
Thank you.
- As always, there are so many options in Excel. FYI - In some cases I prefer nested IF() instead of IFS(). There are some discussions about the benefits of each but basically if you are not using dynamic arrays and have complicated formulas that you do NOT want to get calculated then nested IF can be better. So for example
IF( simple case is true, "answer A", IF( complicated computationally taxing case,, "answer B", "answer C"))
is better than using IFS because IFS calculates all parts regardless while IF will not (as long as you don't put dynamic arrays inside).