IFS function

PRO

In Jspreadsheet Formulas Pro, the IFS function is a valuable tool that allows you to assess several conditions and deliver a result that matches the first true condition. Think of it like a series of if-then statements; it checks each condition in the order you list them and once it finds a true one, it gives you the corresponding outcome. This can be particularly useful when you're dealing with complex data sets and need to sort or categorize information based on multiple criteria.

Documentation

The IFS function is used to evaluate multiple conditions and return a value that corresponds to the first true condition.

Syntax

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

Parameter	Description
`logical_test1`	The first logical test to perform.
`value_if_true1`	The value to return if the first logical_test evaluates to TRUE.
`logical_testN`	Optional. The nth logical test to perform.
`value_if_trueN`	Optional. The value to return if the nth logical_test evaluates to TRUE.

Behavior

The IFS function is a logical function that checks multiple conditions and returns a value corresponding to the first TRUE condition. It is typically used to evaluate several conditions and returns the value associated with the first condition that is met.

Empty Cells: If a cell reference in a condition is empty, IFS treats it as a zero (0).
Text: IFS function can handle text if it is part of the logical test. For instance, IFS(A1="Apple", "Fruit", A1="Carrot", "Vegetable"). If a text is provided where a numerical value is expected, the function returns an error.
Booleans: The conditions in the IFS function should return a boolean (TRUE or FALSE). If not, the function will return an error.
Errors: If an error occurs in any of the conditions or return values, IFS will propagate that error. If a condition results in an error, the function stops at that condition and returns the error.

Common Errors

Error	Description
#N/A	Occurs when none of the conditions in the `IFS` function are True.
#VALUE!	Occurs when a value is not the expected type. For example, using text where a number is expected.
#DIV/0!	Occurs if one of your conditions attempts to divide by zero.
#NAME?	Occurs if the spreadsheet does not recognize the function name. This might happen if you misspell "IFS".

Best practices

Always ensure your conditions return a boolean (TRUE or FALSE). Otherwise, you will receive an error.

Use clear and explicit conditions. Ambiguous conditions can lead to unexpected results.

If you have more than one condition that could be true at the same time, order your conditions carefully. IFS will return the value for the first true condition it encounters.

To prevent #N/A errors, you can append a final TRUE condition at the end that acts as a default when none of the previous conditions are met. For example, IFS(A1>10, "More than 10", A1<=10, "10 or less", TRUE, "Invalid").

Usage

A few examples using the IFS function.

// Classify numeric ranges
IFS(A1 > 10, "Greater than 10",
    A1 > 5, "Between 6 and 10",
    A1 > 0, "Between 1 and 5",
    TRUE, "0 or less")

// Calculate commission rate based on response
IFS(B2 = "Yes", C2 * 0.1,
    B2 = "No", C2 * 0.05,
    TRUE, 0) 

// Categorize cell content
IFS(D4 = "", "Blank",
    D4 < 0, "Negative",
    D4 > 0, "Positive",
    TRUE, "Zero")

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('Mzc3ZGVmNTRlOWU5NGY0YmNlNmJjMDUyZjNmM2E4YmFlMWU0NWU5NTRhMzRhOTAyOTcyZWVkNjlmOGNhZWZkYjVhM2I3OTUwZjljODdlMjJhNGNiNjdhNWQyYjFhMzJmYThhMWM0MDlkNzViNWZlNTg4ZmQ0ZGNhZmQyYTYyYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56ZzJOREV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Score",
        "Grade",
        "Performance Level"
    ],
    [
        95,
        "=IFS(A2>=90,\"A\",A2>=80,\"B\",A2>=70,\"C\",A2>=60,\"D\",A2<60,\"F\")",
        "=IFS(A2>=90,\"Excellent\",A2>=80,\"Good\",A2>=70,\"Average\",A2>=60,\"Below Average\",A2<60,\"Poor\")"
    ],
    [
        73,
        "=IFS(A3>=90,\"A\",A3>=80,\"B\",A3>=70,\"C\",A3>=60,\"D\",A3<60,\"F\")",
        "=IFS(A3>=90,\"Excellent\",A3>=80,\"Good\",A3>=70,\"Average\",A3>=60,\"Below Average\",A3<60,\"Poor\")"
    ],
    [
        88,
        "=IFS(A4>=90,\"A\",A4>=80,\"B\",A4>=70,\"C\",A4>=60,\"D\",A4<60,\"F\")",
        "=IFS(A4>=90,\"Excellent\",A4>=80,\"Good\",A4>=70,\"Average\",A4>=60,\"Below Average\",A4<60,\"Poor\")"
    ],
    [
        55,
        "=IFS(A5>=90,\"A\",A5>=80,\"B\",A5>=70,\"C\",A5>=60,\"D\",A5<60,\"F\")",
        "=IFS(A5>=90,\"Excellent\",A5>=80,\"Good\",A5>=70,\"Average\",A5>=60,\"Below Average\",A5<60,\"Poor\")"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('Mzc3ZGVmNTRlOWU5NGY0YmNlNmJjMDUyZjNmM2E4YmFlMWU0NWU5NTRhMzRhOTAyOTcyZWVkNjlmOGNhZWZkYjVhM2I3OTUwZjljODdlMjJhNGNiNjdhNWQyYjFhMzJmYThhMWM0MDlkNzViNWZlNTg4ZmQ0ZGNhZmQyYTYyYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56ZzJOREV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Score",
        "Grade",
        "Performance Level"
    ],
    [
        95,
        "=IFS(A2>=90,\"A\",A2>=80,\"B\",A2>=70,\"C\",A2>=60,\"D\",A2<60,\"F\")",
        "=IFS(A2>=90,\"Excellent\",A2>=80,\"Good\",A2>=70,\"Average\",A2>=60,\"Below Average\",A2<60,\"Poor\")"
    ],
    [
        73,
        "=IFS(A3>=90,\"A\",A3>=80,\"B\",A3>=70,\"C\",A3>=60,\"D\",A3<60,\"F\")",
        "=IFS(A3>=90,\"Excellent\",A3>=80,\"Good\",A3>=70,\"Average\",A3>=60,\"Below Average\",A3<60,\"Poor\")"
    ],
    [
        88,
        "=IFS(A4>=90,\"A\",A4>=80,\"B\",A4>=70,\"C\",A4>=60,\"D\",A4<60,\"F\")",
        "=IFS(A4>=90,\"Excellent\",A4>=80,\"Good\",A4>=70,\"Average\",A4>=60,\"Below Average\",A4<60,\"Poor\")"
    ],
    [
        55,
        "=IFS(A5>=90,\"A\",A5>=80,\"B\",A5>=70,\"C\",A5>=60,\"D\",A5<60,\"F\")",
        "=IFS(A5>=90,\"Excellent\",A5>=80,\"Good\",A5>=70,\"Average\",A5>=60,\"Below Average\",A5<60,\"Poor\")"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('Mzc3ZGVmNTRlOWU5NGY0YmNlNmJjMDUyZjNmM2E4YmFlMWU0NWU5NTRhMzRhOTAyOTcyZWVkNjlmOGNhZWZkYjVhM2I3OTUwZjljODdlMjJhNGNiNjdhNWQyYjFhMzJmYThhMWM0MDlkNzViNWZlNTg4ZmQ0ZGNhZmQyYTYyYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56ZzJOREV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Score",
        "Grade",
        "Performance Level"
    ],
    [
        95,
        "=IFS(A2>=90,\"A\",A2>=80,\"B\",A2>=70,\"C\",A2>=60,\"D\",A2<60,\"F\")",
        "=IFS(A2>=90,\"Excellent\",A2>=80,\"Good\",A2>=70,\"Average\",A2>=60,\"Below Average\",A2<60,\"Poor\")"
    ],
    [
        73,
        "=IFS(A3>=90,\"A\",A3>=80,\"B\",A3>=70,\"C\",A3>=60,\"D\",A3<60,\"F\")",
        "=IFS(A3>=90,\"Excellent\",A3>=80,\"Good\",A3>=70,\"Average\",A3>=60,\"Below Average\",A3<60,\"Poor\")"
    ],
    [
        88,
        "=IFS(A4>=90,\"A\",A4>=80,\"B\",A4>=70,\"C\",A4>=60,\"D\",A4<60,\"F\")",
        "=IFS(A4>=90,\"Excellent\",A4>=80,\"Good\",A4>=70,\"Average\",A4>=60,\"Below Average\",A4<60,\"Poor\")"
    ],
    [
        55,
        "=IFS(A5>=90,\"A\",A5>=80,\"B\",A5>=70,\"C\",A5>=60,\"D\",A5<60,\"F\")",
        "=IFS(A5>=90,\"Excellent\",A5>=80,\"Good\",A5>=70,\"Average\",A5>=60,\"Below Average\",A5<60,\"Poor\")"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('Mzc3ZGVmNTRlOWU5NGY0YmNlNmJjMDUyZjNmM2E4YmFlMWU0NWU5NTRhMzRhOTAyOTcyZWVkNjlmOGNhZWZkYjVhM2I3OTUwZjljODdlMjJhNGNiNjdhNWQyYjFhMzJmYThhMWM0MDlkNzViNWZlNTg4ZmQ0ZGNhZmQyYTYyYzMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56ZzJOREV3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Score",
        "Grade",
        "Performance Level"
    ],
    [
        95,
        "=IFS(A2>=90,\"A\",A2>=80,\"B\",A2>=70,\"C\",A2>=60,\"D\",A2<60,\"F\")",
        "=IFS(A2>=90,\"Excellent\",A2>=80,\"Good\",A2>=70,\"Average\",A2>=60,\"Below Average\",A2<60,\"Poor\")"
    ],
    [
        73,
        "=IFS(A3>=90,\"A\",A3>=80,\"B\",A3>=70,\"C\",A3>=60,\"D\",A3<60,\"F\")",
        "=IFS(A3>=90,\"Excellent\",A3>=80,\"Good\",A3>=70,\"Average\",A3>=60,\"Below Average\",A3<60,\"Poor\")"
    ],
    [
        88,
        "=IFS(A4>=90,\"A\",A4>=80,\"B\",A4>=70,\"C\",A4>=60,\"D\",A4<60,\"F\")",
        "=IFS(A4>=90,\"Excellent\",A4>=80,\"Good\",A4>=70,\"Average\",A4>=60,\"Below Average\",A4<60,\"Poor\")"
    ],
    [
        55,
        "=IFS(A5>=90,\"A\",A5>=80,\"B\",A5>=70,\"C\",A5>=60,\"D\",A5<60,\"F\")",
        "=IFS(A5>=90,\"Excellent\",A5>=80,\"Good\",A5>=70,\"Average\",A5>=60,\"Below Average\",A5<60,\"Poor\")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub