How to create a drop-down and multiple dependent drop-down lists.in Excel To allow a user to switch between two or more lists, you can use the IF function to test for a value and conditionally return a list of values based on the result. Formula =IF(A1=”See full list”,long_list,short_list) Explanation In the example shown above, the …
This tutorial shows how to calculate If cell equals in Excel using the example below; Formula =IF(A1=”red”,true result,false result) Explanation If you want to do something specific when a cell equals a certain value, you can use the IF function to test the value, then do something if the result is TRUE, and (optionally) do something else …
This tutorial shows how to calculate Invoice status with nested if in Excel using the example below; Formula =IF(balance=0,”Paid”,IF(current_date<due_date,”Open”,”Overdue”)) Explanation To determine invoice status (i.e. paid, open, overdue), you can use a nested IF formula and the TODAY function. In the example shown, the formula in G5 is: =IF(F5=0,”Paid”,IF(TODAY()<C5,”Open”,”Overdue”)) How this formula works Note: the “current date” …
This tutorial shows how to calculate If NOT this or that in Excel using the example below; Formula =IF(NOT(OR(A1=”red”,A1=”green”)),”x”,””) Explanation If you want to do something specific when a cell is NOT this or that (i.e. is NOT equal to X or Y, etc.) you can use the IF function in combination with the NOT and OR functions …
This tutorial shows how to calculate If cell is greater than in Excel using the example below; Formula =IF(A1>30,”Yes”,”No”) Explanation If you want to take an action when a cell value is greater than a certain value, you can use the IF function to test a value and return one value if the test is …
This tutorial shows how to calculate If this AND that OR that in Excel using the example below; Formula =IF(AND(A1=”x”,OR(B1=”y”,B1=”z”)),”x”,””) Explanation To test for various combinations of this AND that, or this OR that, you can use the IF function with the AND and OR functions. In the example shown, the formula in D6 is: =IF(AND(B6=”red”,OR(C6=”small”,C6=”medium”)),”x”,””) When an item …
This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions, …
This tutorial shows how to calculate IF with boolean logic in Excel using the example below; Formula = IF(criteria1*criteria2*criteria3,result) Explanation In the example shown, the formula in F8 is: {=SUM(IF((color=”red”)*(region=”East”)*(quantity>7),quantity))} Note: this is an array formula, and must be entered with control + shift + enter. How this formula works Note: This example demonstrates how to replace a nested …
This tutorial shows how to calculate If cell is not blank in Excel using the example below; Formula =IF(A1<>””,”Not blank”,”Blank”) Explanation If you want to test a cell and take some action if the cell is not blank (not empty), you can use a simple formula based on the IF function. In the example shown, we have …
This tutorial shows how to calculate If cell is this OR that in Excel using the example below; Formula =IF(OR(A1=”this”,A1=”that”),”x”,””) Explanation If you want to do something specific when a cell equals this or that (i.e. is equal to X or Y, etc.) you can use the IF function in combination with the OR function to …
This tutorial shows how to calculate If cell is x or y and z in Excel using the example below; Formula =IF(AND(OR(A1=x,A1=y),B1=z),”yes”,”no”) Explanation You can combine logical statements with the OR and AND functions inside the IF function. If color is red or green and quantity is greater than 10 In the example shown, we …
This tutorial shows how to calculate If else in Excel using the example below; Formula =IF(test, true result, false result) Explanation If you need to test a condition, then take one action if the condition is TRUE, and another action if the condition if FALSE, you can use the IF function. Context In the example shown, we …
This tutorial shows how to work Win loss points calculation in Excel using the example below; To assign points based on win/loss/tie results for a team, you can use a simple VLOOKUP formula, or a nested IF formula, as explained below. Formula =VLOOKUP(result,points_table,2,0) Explanation In the example shown, the formula in D5 is: =VLOOKUP(C5,points_table,2,0) How this …
This tutorial shows how to calculate If this AND that in Excel using the example below; Formula =IF(AND(A1=”this”,B1=”that”),”x”,””) Explanation If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result …
This tutorial shows how to calculate Nested IF function example in Excel using the example below; Formula =IF(T1,R1,IF(T2,R2,IF(T3,R3,IF(T4,R4,R5)))) Explanation In the code above, T1-T5 represents 5 different logical tests, and R1-R5 represents 5 different results. You can see that each IF function requires it’s own set of parentheses. This article describes the Excel nested IF construction. Usually, …
This tutorial shows how to Running count group by n size in Excel using the example below; Formula =CEILING(COUNTA(expanding_range)/size,1) > Explanation To creating a running count of groups of a variable size, you can use the COUNTA and CEILING function. In the example shown, C5 contains this formula: =CEILING(COUNTA($B$5:B5)/size,1) where “size” is the named range F4. How …
This tutorial shows how to calculate Return blank if in Excel using the example below; Formula =IF(A1=1,B1,””) Explanation To return a blank result using the IF function, you can use an empty string (“”). In the example shown, the formula in D5 (copied down) is: =IF(B5=1,C5,””) How this formula works This formula is based on the IF function, …
This tutorial shows how to Highlight numbers that include symbols in Excel using the example below; Formula =IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)=”<“,(MID(B4,2,LEN(B4))+0)<input)) Explanation To highlight numbers less than a certain value, including numbers entered as text like “<9”, “<10”, etc., you can use conditional formatting with a formula strips the symbols as needed and handles the result as a …