ExcelFormulas.md

Excel tips and tricks

A place to store formulas and other handy things from Excel

How to fill a column with values based on conditions in another column

This checks the cell B2 to verify if it is higher than zero to assign a value of Happy. If not it checks if it is negative to assign a value of Sad or Neutral if B2 is zero.

=IF(B2>0;"Happy";IF(B2<0;"Sad";"Neutral"))

How to categorize products or services based on specific conditions

Suppose you have a dataset where you want to categorize products or services based on various criteria. Here's an Excel formula that does exactly that:

=IF(
    OR(CategoryCell="Cancelled", CategoryCell="Invalid"),
    "Cancelled",
    IF(
        ISNUMBER(SEARCH("subscription", ProductDescriptionCell)),
        "Subscription",
        IF(
            OR(QuantityCell=0, OR(ISNUMBER(SEARCH("free", ProductDescriptionCell)), ISNUMBER(SEARCH("gratis", ProductDescriptionCell)))),
            "Free",
            IF(
                OR(ISNUMBER(SEARCH("%", ProductDescriptionCell)), ISNUMBER(SEARCH("discount", ProductDescriptionCell))),
                "Discount",
                "Regular"
            )
        )
    )
)

This formula categorizes products or services based on the following conditions:

• If the category in the cell (CategoryCell) is "Cancelled" or "Invalid", the result is "Cancelled".
• If the product description (ProductDescriptionCell) contains the word "subscription", the result is "Subscription".
• If the quantity (QuantityCell) is 0, or the product description contains the words "free" or "gratis", the result is "Free".
• If the product description contains the symbol "%" or the word "discount", the result is "Discount".
• If none of the above conditions are met, the result is "Regular".