All Content

- All of Microsoft

Timespan

Excel

Apr 18, 2023 9:00 AM

I took our 1,121 character long nested IF formula and made it 80% shorter. In this fun and insightful video, let me show you how I made that happen.

Welcome to the “Honey, I shrunk” series.

One way to reduce the length of an IF formula is to use nested IF functions. However, this can make the formula difficult to read and understand. Another approach is to use the logical functions AND, OR, and NOT to simplify the conditions in the IF statement.

For example, consider the following lengthy IF formula:

=IF(A1>0,IF(A1<10,”Small”,IF(A1<20,”Medium”,”Large”)),”Negative or zero”)

This formula checks if the value in cell A1 is positive, and if so, it checks if it is less than 10, between 10 and 20, or greater than 20, and returns a size category accordingly. If the value in A1 is negative or zero, it returns “Negative or zero”.

To simplify this formula, we can use the AND and OR functions as follows:

=IF(A1<=0,”Negative or zero”,IF(AND(A1>0,A1<10),”Small”,IF(AND(A1>=10,A1<20),”Medium”,”Large”)))

In this simplified formula, we first check if the value in A1 is negative or zero. If it is, we return “Negative or zero”. If it is positive, we use the AND function to check if it is between 0 and 10, or between 10 and 20, and return the corresponding size category using the IF function. If it is greater than 20, we return “Large”.

By using the AND and OR functions to simplify the conditions in the IF statement, we can make the formula easier to read and understand, while still achieving the same result.

- 0:00 - The long nested IF formula for calculating discount rate
- 1:15 - Making the original long formula.
- 4:10 - How I shrunk the formula.

The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or ...

Apr 29, 2016 — Learn Excel IF Formula – 5 Tricks you Didnt know · 1. Sum alternative rows / columns: · 2. Count how many times each item on list A is in List B ...

Results 1 - 19 of 19

Missing: shrunk | Show results with: shrunk

Mar 13, 2023 — Our pic of the best co-op games to play online with friends, ... This glorious co-op platformer takes the concept of Honey I Shrunk the Kids ...

Results 1 - 6 of 6 — My Hero Academia is one of the most popular shounen anime of ... are kinda cool idk your choi 𝖧𝖮𝖭𝖤𝖸 GOLDEN. quirk Table of ...

- Condense lengthy nested IF functions with the newer IFS ...
- [Instructor] When you use the IF function in Excel, if you're using it with multiple nested IFs, as we're about to show you in this worksheet, the function ...