Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Sum of divide – Array formula Array formula
  • Extract current (Active) workbook name CELL
  • Convert cell into textbox Excel User tips
  • Always get 1st name (or last) Formulas - combined functions
  • DayName Date and Time
  • Searching table in 2 dimensions. Formulas - combined functions
  • Search across sheets in function ADDRESS
  • Count how many times letter found Formulas - combined functions

IFS (and IFNA) to avoid nested IFs

Posted on March 25, 2018 By ANmar

I tend to see more and more the usage of nested IF functions recently

Now, do not get me wrong, IF is great, but come on, are you going to use it for more than 2 conditions? seriously?

Excel 2013 comes with IFS, the perfect alternative to nested IFs

As you can see, takes up to 127 conditions, neat, right?

As you can see, it basically goes as …

If condition1 from Logical_test1 is true, then display value from Value_if_true1

If not…

Then if condition from Logical_test2 is true, then display value from Value_if_true2

if not…

Then if condition from Logical_test3 is true, then display value from Value_if_true3

and so on, until last condition.

Please, make use of that if you know your spreadsheet will not be used in Excel 2010, which I understand is still in the market.

Anyways, screenshots here are from my cell phone while i was playing with IFS

Oh, and of course, if you want to catch the condition of none of these are true, just use IFNA, or you can use IF(ISERROR( too.

give it a shot!

 

Download spreadsheet
Excel Online version
Formulas - combined functions, IFNA, IFS, Logical, Standard functions

Post navigation

Previous Post: Highlight dates dynamically
Next Post: FORMULATEXT to show formulas

Related Posts

  • Workdays – Across months DATE
  • Sorting with functions only CHOOSE
  • GoLast to jump to last cell in a column CELL
  • Dynamic selection list ActiveX controls
  • Extract current (Active) workbook name CELL
  • Insert Blank rows into table using functions CHAR

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Formula Beautifier Excel User tips
  • Always get 1st name (or last) Formulas - combined functions
  • VLookup Lookup and References
  • Excel Limits Excel User tips
  • The Excel “&” bug Excel User tips
  • 0% interest promotion Basic Math
  • GoLast to jump to last cell in a column CELL
  • Middle name + 1st name Basic Math

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme