{"id":45581,"date":"2023-10-30T20:17:54","date_gmt":"2023-10-30T19:17:54","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=45581"},"modified":"2025-12-17T19:29:37","modified_gmt":"2025-12-17T18:29:37","slug":"100-fonctions-formules-pour-google-sheets-excel-basics","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/fr\/blog\/excel-features\/100-functions-formulas-for-google-sheets-excel-basics\/","title":{"rendered":"100+ Fonctions et formules pour Google Sheets & Excel basics"},"content":{"rendered":"

[et_pb_section fb_built=”1″ _builder_version=”4.16″ da_disable_devices=”off|off|off” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on”][et_pb_row _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

\u200bThis cheat sheet simplifies the functionalities and capabilities of Google Sheets and Excel, providing a comprehensive guide to master these tools.<\/p>\n

You’ll discover a wide range of topics, from basic mathematical operations to complex financial calculations, ensuring you can navigate your data with ease and precision.<\/p>\n

Explore advanced features like custom function creation, data manipulation, and dynamic arrays to leverage the full potential of both platforms.<\/p>\n

Along the way, you’ll find links to relevant content on our blog.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”getting” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Getting started with spreadsheets<\/span><\/h2>\n

Learn the basic concepts and tools that make spreadsheets powerful for data management, analysis, and visualization.<\/p>\n

If you’re new to spreadsheets, this section is for you. If you’re looking to explore advanced functions, feel free to skip ahead.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”definitions” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Definitions<\/span><\/h3>\n

Spreadsheet software<\/strong>: An application like Microsoft Excel or Google Sheets used to store data, perform calculations, and organize information.<\/p>\n

Worksheet<\/strong>: A single page in a workbook. It is a grid of cells arranged in rows and columns. In Google Sheets, worksheets are sometimes called “sheets.”<\/p>\n

Spreadsheet [Google Sheets]<\/strong>: A file with one or more worksheets.<\/p>\n

Workbook [Excel]<\/strong>: A file containing one or more worksheets.<\/p>\n

Cell<\/strong>: A rectangular box in a worksheet that can store a data value, a formula, or other content.<\/p>\n

Cell reference<\/strong>: The location of a cell. The column is described with letters, and the row is described with numbers. For example, the cell in the 4th column and 7th row is denoted D7.<\/p>\n

Cell range<\/strong>: A group of adjacent cells in a worksheet. It’s expressed with cell references, indicating the upper and lower limits. For example, A1:C3\u00a0refers to cells in columns A, B, and C, and rows 1, 2, and 3. You can use cell ranges to perform calculations on multiple cells or apply formatting to a group.<\/p>\n\n\n\n\n\n\n
<\/td>\nA<\/span><\/td>\nB<\/span><\/td>\nC<\/span><\/td>\n<\/tr>\n
1<\/span><\/td>\nA1<\/span><\/td>\nB1<\/span><\/td>\nC1<\/span><\/td>\n<\/tr>\n
2<\/span><\/td>\nA2<\/span><\/td>\nB2<\/span><\/td>\nC2<\/span><\/td>\n<\/tr>\n
3<\/span><\/td>\nA3<\/span><\/td>\nB3<\/span><\/td>\nC3<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Formula<\/strong>: A piece of code that performs a calculation. Formulas start with an equal sign (=) and can contain functions, mathematical operators, values, and cell references.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”basic” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

<\/span><\/p>\n

Basic features<\/span><\/h3>\n

Getting started with spreadsheets means understanding fundamental features. You can format cells to change their appearance, adjust text alignment, set fonts, and apply colors to distinguish different data types or priorities.<\/p>\n

These features form the foundation of effective spreadsheet use in both Google Sheets and Excel.<\/p>\n

Add comments to cells<\/span><\/h4>\n
    \n
  1. Click on the cell where you want to add a comment.<\/span><\/li>\n
  2. Right click or CTRL+<\/strong>click on the cell and select New Comment<\/strong> from the context menu. You can also click the Insert<\/strong> menu then New Comment<\/strong>.<\/span><\/li>\n
  3. This will open a small text box where you can type your comment.\u00a0<\/span><\/li>\n
  4. Once you have entered your comment, click the green arrow button to save it.
    <\/span><\/li>\n<\/ol>\n

    [\/et_pb_text][et_pb_text module_id=”cell” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Cell references<\/span><\/h3>\n

    When referencing cells in spreadsheets, keep these key points in mind:<\/p>\n\n\n\n\n\n\n\n\n
    <\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\n<\/tr>\n
    Single cell reference<\/span><\/td>\nRefer to a specific cell using its column letter and row number.<\/span><\/td>\n=B2<\/span><\/td>\n<\/tr>\n
    Cell ranges<\/span><\/td>\nUse the start:end format to specify a range of cells in a formula.<\/span><\/td>\n=SUM(B2:B5)<\/span><\/td>\n<\/tr>\n
    Absolute<\/a> cell reference<\/a><\/td>\nA cell address that remains constant, regardless of where the formula is copied or moved. <\/span>Use $ to make a cell reference absolute.<\/span><\/td>\n=$B$2 (Both column and row are absolute)<\/span><\/td>\n<\/tr>\n
    Relative cell reference<\/td>\nA<\/span> cell address that adjusts based on the location where the formula is copied or moved.\u00a0 By default all cell references are relative.<\/span><\/span><\/td>\n\u00a0<\/span><\/td>\n<\/tr>\n
    Mixed cell reference<\/span><\/td>\n A cell reference that combines both absolute and relative references. This means that either the column or the row is fixed with a dollar sign ($).<\/span><\/span><\/td>\n\n

    =$B2 (Column is absolute, row is relative)<\/span><\/p>\n

    =B$2 (Column is relative, row is absolute)<\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”operators” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Operators<\/span><\/h2>\n

    Operators in spreadsheet applications are special symbols or that perform operations on one or more values to produce a result. These tools are essential for building formulas that process data, perform calculations, manipulate text, and make logical decisions within a spreadsheet.
    <\/span><\/p>\n

    [\/et_pb_text][et_pb_text module_id=”arithmetic” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Arithmetic operators<\/span><\/h3>\n

    Arithmetic operators are a subset of operators used specifically for performing basic mathematical calculations. These calculations can be performed on numeric values, cells, and ranges within a spreadsheet.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
    Operator<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Results<\/b><\/td>\n<\/tr>\n
    +<\/span><\/td>\nAdd two values<\/span><\/td>\n3 + 6<\/span><\/td>\n9<\/span><\/td>\n<\/tr>\n
    –<\/span><\/td>\nSubtract one value from another<\/span><\/td>\n10 – 7<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n
    *<\/span><\/td>\nMultiply two values<\/span><\/td>\n21 * 2<\/span><\/td>\n42<\/span><\/td>\n<\/tr>\n
    \/<\/span><\/td>\nDivide one value by another<\/span><\/td>\n28 \/ 7<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n
    %<\/span><\/td>\nConvert a value to a percentage<\/span><\/td>\n3.25%<\/span><\/td>\n0.0325<\/span><\/td>\n<\/tr>\n
    ^<\/span><\/td>\nRaise a value to a power<\/span><\/td>\n2 ^ 6<\/span><\/td>\n64<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”comparison” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Comparison operators<\/span><\/h3>\n

    Comparison operators return logical values (TRUE or FALSE) from a comparison of two values. They form the backbone of logical statements, conditional formatting, and data validation.<\/span><\/p>\n

    Understanding and utilizing comparison operators empower users to create more dynamic, responsive, and useful spreadsheets by harnessing the power of logical testing and condition-based operations.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
    Operator<\/b><\/td>\nComparison<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    =<\/span><\/td>\nEqual<\/span><\/td>\n1 = 2<\/span><\/td>\nFALSE<\/span><\/td>\n<\/tr>\n
    ><\/span><\/td>\nGreater than<\/span><\/td>\n5 > 4<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    <<\/span><\/td>\nLess than<\/span><\/td>\n2 < 3<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    <><\/span><\/td>\nNot equal<\/span><\/td>\n1 <> 1<\/span><\/td>\nFALSE<\/span><\/td>\n<\/tr>\n
    >=<\/span><\/td>\nGreater than or equal to<\/span><\/td>\n5 >= 5<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    <=<\/span><\/td>\nLess than or equal to<\/span><\/td>\n2 <= 3<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”functions” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Functions<\/h2>\n

    [\/et_pb_text][et_pb_text module_id=”core” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Core Spreadsheet Functions<\/span><\/h3>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Math Functions<\/span><\/h4>\n

    Math functions in spreadsheets offer a range of capabilities for performing complex calculations. They can handle tasks from basic arithmetic to advanced statistical analysis, enabling users to work with numbers, calculate statistics, and perform financial modeling.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    LOG()<\/span><\/a><\/td>\nCalculates the logarithm of a number to a specified base.<\/span><\/td>\n=LOG(100, 10)<\/span><\/td>\n2<\/span><\/td>\n<\/tr>\n
    EXP()<\/span><\/td>\nReturns the raised power of a given number.<\/span><\/td>\n=EXP(2)<\/span><\/td>\n7.398<\/span><\/td>\n<\/tr>\n
    MAX()<\/span><\/td>\nFinds the largest value in a set of values.<\/span><\/td>\n=MAX(A1:A6, C1:C3, 12)<\/span><\/td>\n28<\/span><\/td>\n<\/tr>\n
    MIN()<\/span><\/td>\nFinds the smallest value in a set of values.<\/span><\/td>\n=MIN(A1:A6, C1:C3, 12)<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
    MAXA()<\/span><\/td>\nSimilar to MAX, but counts TRUE as 1 and FALSE as 0.<\/span><\/td>\n=MAXA(A1:A6, C1:C3, FALSE)<\/span><\/td>\nValue as MAX(), TRUE as 1<\/span><\/td>\n<\/tr>\n
    MINA()<\/span><\/td>\nSimilar to MIN, but counts TRUE as 1 and FALSE as 0.<\/span><\/td>\n=MINA(A1:A6, C1:C3, FALSE)<\/span><\/td>\nValue as MIN(), TRUE as 1<\/span><\/td>\n<\/tr>\n
    SUM()<\/span><\/td>\nAdds all numbers in a range of cells.<\/span><\/td>\n=SUM(A1:A6, C1:C3, 12)<\/span><\/td>\n108<\/span><\/td>\n<\/tr>\n
    AVERAGE()<\/span><\/td>\nCalculates the mean of a group of numbers.<\/span><\/td>\n=AVERAGE(A1:A6, C1:C3, 12)<\/span><\/td>\n12<\/span><\/td>\n<\/tr>\n
    MEDIAN()<\/span><\/td>\nFinds the median value in a set of numbers.<\/span><\/td>\n=MEDIAN(A1:A6, C1:C3, 12)<\/span><\/td>\n10<\/span><\/td>\n<\/tr>\n
    PERCENTILE.INC()<\/span><\/td>\nCalculates the nth percentile of a data set.<\/span><\/td>\n=PERCENTILE.INC(C1:C6, 0.25)<\/span><\/td>\n22.75<\/span><\/td>\n<\/tr>\n
    CEILING()<\/span><\/td>\nRounds a number up, away from zero, to the nearest multiple of significance.<\/span><\/td>\n=CEILING(PI(), 0.1)<\/span><\/td>\n3.2<\/span><\/td>\n<\/tr>\n
    FLOOR()<\/span><\/td>\nRounds a number down, towards zero, to the nearest multiple of significance.<\/span><\/td>\n=FLOOR(PI(), 0.1)<\/span><\/td>\n3.1<\/span><\/td>\n<\/tr>\n
    VAR.S()<\/span><\/td>\nCalculates the sample variance of a data set.<\/span><\/td>\n=VAR.S(B1:B6)<\/span><\/td>\n19.37<\/span><\/td>\n<\/tr>\n
    STDEV.S()<\/span><\/td>\nCalculates the sample standard deviation of a data set.<\/span><\/td>\n=STDEV.S(B1:B6)<\/span><\/td>\n4.40<\/span><\/td>\n<\/tr>\n
    POWER()<\/span><\/td>\nRaises a number to a specified power.<\/span><\/td>\n=POWER(2,3)<\/span><\/td>\n8<\/span><\/td>\n<\/tr>\n
    SQRT()<\/span><\/td>\nReturns the square root of a number.<\/span><\/td>\n=SQRT(16)<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n
    ABS()<\/span><\/td>\nReturns the absolute value of a number.<\/span><\/td>\n=ABS(-5)<\/span><\/td>\n5<\/span><\/td>\n<\/tr>\n
    MOD()<\/span><\/td>\nReturns the remainder after division.<\/span><\/td>\n=MOD(10,3)<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
    MODE()<\/span><\/td>\nReturns the most frequently occurring number in a range.<\/span><\/td>\n=MODE(A1:A10)<\/span><\/td>\nMost frequent number in A1:A10<\/span><\/td>\n<\/tr>\n
    RAND()<\/span><\/a><\/td>\nGenerates a random number between 0 and 1.<\/span><\/td>\n=RAND()<\/span><\/td>\nRandom number between 0 and 1<\/span><\/td>\n<\/tr>\n
    RANDBETWEEN()<\/span><\/td>\nGenerates a random integer number between specified values.<\/span><\/td>\n=RANDBETWEEN(1,100)<\/span><\/td>\nRandom integer between 1 and 100<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.27.3″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Text functions<\/span><\/h3>\n

    Text functions and operators in spreadsheets are invaluable tools for manipulating and analyzing strings of text. These functions allow users to measure, combine, split, and alter text data in various ways, facilitating the organization, extraction, and transformation of textual information.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    LEN()<\/span><\/a><\/td>\nReturns the length of a string in characters.<\/span><\/td>\n=LEN(D5)<\/span><\/td>\n28<\/span><\/td>\n<\/tr>\n
    & (Concatenate)<\/span><\/td>\nCombines multiple strings into one.<\/span><\/td>\n=”Hello ” & D1 & “!”<\/span><\/td>\n“Hello World!”<\/span><\/td>\n<\/tr>\n
    REPT()<\/span><\/td>\nRepeats text a given number of times.<\/span><\/td>\n=REPT(D6, 3)<\/span><\/td>\n“UniverseUniverseUniverse”<\/span><\/td>\n<\/tr>\n
    TEXTSPLIT()<\/span><\/td>\nSplits a string on a specified delimiter into separate cells.<\/span><\/td>\n=TEXTSPLIT(D4, “o”)<\/span><\/td>\n“L”, “cal Gr”, “up”<\/span><\/td>\n<\/tr>\n
    TRIM()<\/span><\/td>\nRemoves extra spaces from text.<\/span><\/td>\n=TRIM(” Hello World “)<\/span><\/td>\n“Hello World”<\/span><\/td>\n<\/tr>\n
    UPPER()<\/span><\/a><\/td>\nConverts all letters in a text string to uppercase.<\/span><\/td>\n=UPPER(D3)<\/span><\/td>\n“MILKY WAY”<\/span><\/td>\n<\/tr>\n
    LOWER()<\/span><\/a><\/td>\nConverts all letters in a text string to lowercase.<\/span><\/td>\n=LOWER(D3)<\/span><\/td>\n“milky way”<\/span><\/td>\n<\/tr>\n
    PROPER()<\/span><\/td>\nConverts text to title case (the first letter in each word to uppercase).<\/span><\/td>\n=PROPER(“milky way”)<\/span><\/td>\n“Milky Way”<\/span><\/td>\n<\/tr>\n
    ‘ (Apostrophe)<\/span><\/td>\nTreats numbers as text.<\/span><\/td>\n‘12345<\/span><\/td>\n“12345” (as text)<\/span><\/td>\n<\/tr>\n
    SEARCH()<\/span><\/a><\/td>\nFinds the position of a substring, case-insensitive.<\/span><\/td>\n=SEARCH(“world”, “Hello World”)<\/span><\/td>\n7<\/span><\/td>\n<\/tr>\n
    LEFT()<\/span><\/a><\/td>\nReturns characters from the start of a text string.<\/span><\/td>\n=LEFT(“Hello World”, 5)<\/span><\/td>\n“Hello”<\/span><\/td>\n<\/tr>\n
    RIGHT()<\/span><\/td>\nReturns characters from the end of a text string.<\/span><\/td>\n=RIGHT(“Hello World”, 5)<\/span><\/td>\n“World”<\/span><\/td>\n<\/tr>\n
    MID()<\/span><\/a><\/td>\nExtracts a substring from a text string, given a starting position and length.<\/span><\/td>\n=MID(D6, 4, 5)<\/span><\/td>\n“verse”<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.27.3″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Dates<\/span><\/h4>\n

    Date functions in spreadsheets are essential for handling date information. These functions facilitate operations such as creating dates, calculating the difference between dates, and extracting specific components from a date.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    DATE()<\/span><\/a><\/td>\nCreates a date from year, month, and day values.<\/span><\/td>\n=DATE(2023, 12, 23)<\/span><\/td>\nSerial number for December 23, 2023<\/span><\/td>\n<\/tr>\n
    NETWORKDAYS()<\/span><\/a><\/td>\nCounts working days between two dates, excluding weekends and optional holidays.<\/span><\/td>\n=NETWORKDAYS(“2023-01-01”, “2023-01-31”)<\/span><\/td>\nNumber of whole workdays in January 2023, excluding weekends<\/span><\/td>\n<\/tr>\n
    DATEVALUE()<\/span><\/td>\nConverts a date in text format to a serial number.<\/span><\/td>\n=DATEVALUE(“2023-01-01”)<\/span><\/td>\nSerial number for January 1, 2023<\/span><\/td>\n<\/tr>\n
    EOMONTH()<\/span><\/a><\/td>\nFinds the last day of the month a specified number of months before or after a start date.<\/span><\/td>\n=EOMONTH(“2023-01-01”, 1)<\/span><\/td>\nSerial number for the last day of February 2023<\/span><\/td>\n<\/tr>\n
    EDATE()<\/span><\/a><\/td>\nCalculates the serial number of the date a certain number of months before or after a start date.<\/span><\/td>\n=EDATE(“2023-01-01”, -1)<\/span><\/td>\nSerial number for December 1, 2022<\/span><\/td>\n<\/tr>\n
    NOW()<\/span><\/a><\/td>\nReturns the current date and time.<\/span><\/td>\n=NOW()<\/span><\/td>\nCurrent date and time as a serial number<\/span><\/td>\n<\/tr>\n
    TODAY()<\/span><\/a><\/td>\nReturns the current date.<\/span><\/td>\n=TODAY()<\/span><\/td>\nCurrent date as a serial number<\/span><\/td>\n<\/tr>\n
    TIME()<\/span><\/td>\nCreates a time from hour, minute, and second values.<\/span><\/td>\n=TIME(09,45, 52)<\/span><\/td>\n9:45:52 AM<\/span><\/span><\/td>\n<\/tr>\n
    MONTH()<\/span><\/td>\nExtracts the month from a date.<\/span><\/td>\n=MONTH(“2023-03-15”)<\/span><\/td>\n3 (March)<\/span><\/td>\n<\/tr>\n
    YEAR()<\/span><\/a><\/td>\nExtracts the year from a date.<\/span><\/td>\n=YEAR(“2023-03-15”)<\/span><\/td>\n2023<\/span><\/td>\n<\/tr>\n
    WEEKDAY()<\/span><\/a><\/td>\nExtracts the weekday from a date.<\/span><\/td>\n=WEEKDAY(“2023-03-15”)<\/span><\/td>\n4 (Wednesday)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Conditional\/Boolean<\/span><\/h4>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Logical Functions<\/span><\/h3>\n

    Logical functions are used in spreadsheets to perform operations that evaluate to TRUE or FALSE. These functions allow for decision making within formulas based on specific conditions.<\/span><\/p>\n\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    NOT()<\/span><\/a><\/td>\nLogical NOT: returns TRUE if the argument is FALSE, and vice versa.<\/span><\/td>\n=NOT(1 = 2)<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    OR()<\/span><\/a><\/td>\nLogical OR: returns TRUE if any of the arguments are TRUE.<\/span><\/td>\n=OR(1 > 10, 2 < 20)<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    AND()<\/span><\/a><\/td>\nLogical AND: returns TRUE only if all arguments are TRUE.<\/span><\/td>\n=AND(1 > 10, 2 < 20)<\/span><\/td>\nFALSE<\/span><\/td>\n<\/tr>\n
    XOR()<\/span><\/td>\nLogical XOR: returns TRUE if an odd number of arguments are TRUE.<\/span><\/td>\n=XOR(1 > 10, 2 < 20)<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”core” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Data Types<\/span><\/h4>\n

    Counting data<\/span><\/h4>\n

    Counting functions in spreadsheets are essential for aggregating data. They allow users to count the number of cells that meet specific criteria, such as non-empty cells, cells containing numbers, or even blank cells within a specified range.<\/span><\/p>\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    COUNT()<\/span><\/td>\nCounts the number of cells in a range that contain numbers.<\/span><\/td>\n=COUNT(A5:E5)<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n
    COUNTA()<\/span><\/td>\nCounts the number of cells in a range that are not empty.<\/span><\/td>\n=COUNTA(A5:E5)<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n
    COUNTBLANK()<\/span><\/td>\nCounts the number of empty cells in a specified range.<\/span><\/td>\n=COUNTBLANK(A5:E5)<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”data” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Data Manipulation<\/span><\/h3>\n

    Data manipulation functions in spreadsheets enable users to filter, sort, and organize data dynamically. These functions provide powerful ways to view and analyze data sets, making it easier to understand trends, find unique values, and generate sequences based on specific criteria.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    FILTER()<\/span><\/a><\/td>\nRetrieves a subset of data that meets a specified condition.<\/span><\/td>\n=FILTER(A1:B6, C1:C6>100)<\/span><\/td>\nSubset of A1:B6 where corresponding C1:C6 values are > 100<\/span><\/td>\n<\/tr>\n
    SORT()<\/span><\/a><\/td>\nSorts the rows of a data range based on the values in one or more columns.<\/span><\/td>\n=SORT(A1:E6, 4)<\/span><\/td>\nDataset sorted in alphabetical order of the fourth column<\/span><\/td>\n<\/tr>\n
    SORTBY()<\/span><\/td>\nSorts a range based on the values in another range or array.<\/span><\/td>\n=SORTBY(A1:E6, D1:D6)<\/span><\/td>\nDataset sorted based on values in D1:D6<\/span><\/td>\n<\/tr>\n
    UNIQUE()<\/span><\/a><\/td>\nReturns a list of unique values from a specified range.<\/span><\/td>\n=UNIQUE(A1:A6)<\/span><\/td>\nUnique values from A1:A6<\/span><\/td>\n<\/tr>\n
    SEQUENCE()<\/span><\/td>\nGenerates a sequence of numbers based on start value, number of rows, and step.<\/span><\/td>\n=SEQUENCE(5, 1, 3, 2)<\/span><\/td>\n5 rows, 1 column with values 3, 5, 7, 9, 11<\/span><\/td>\n<\/tr>\n
    TRANSPOSE()<\/span><\/td>\nTransposes the rows and columns of a range or array, effectively “flipping” its orientation.<\/span><\/td>\n=TRANSPOSE(A1:B2)<\/span><\/td>\nTransposes the data in the range A1:B2 from rows to columns, or vice versa.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Data types<\/h3>\n

    Data type functions in spreadsheets are used to check or convert the type of data in a cell, such as determining if a cell contains a number, text, or boolean value, or converting between these types.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    ISNUMBER()<\/span><\/td>\nChecks if a cell is a number.<\/span><\/td>\n=ISNUMBER(A1)<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    ISTEXT()<\/span><\/td>\nChecks if a cell is text.<\/span><\/td>\n=ISTEXT(D1)<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    ISLOGICAL()<\/span><\/td>\nChecks if a cell is a boolean (logical value).<\/span><\/td>\n=ISLOGICAL(A1)<\/span><\/td>\nFALSE<\/span><\/td>\n<\/tr>\n
    ISLOGICAL()<\/span><\/td>\nChecks if an expression is a boolean (logical value).<\/span><\/td>\n=ISLOGICAL(A1=A1)<\/span><\/td>\nTRUE<\/span><\/td>\n<\/tr>\n
    N()<\/span><\/td>\nConverts to number. Converts a date to a serial number.<\/span><\/td>\n=N(E1)<\/span><\/td>\n44927<\/span><\/td>\n<\/tr>\n
    TEXT()<\/span><\/td>\nConvert to formatted text.<\/span><\/td>\n=TEXT(C6, “0.00E+0”)<\/span><\/td>\n“4.96E+2”<\/span><\/td>\n<\/tr>\n
    ISBLANK()<\/span><\/td>\nChecks if a cell is empty.<\/span><\/td>\n=ISBLANK(A1)<\/span><\/td>\nTRUE if A1 is empty<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    \nSum<\/h3>\n

    The “Sum” functions in spreadsheets are designed to facilitate the aggregation of values within a range, supporting basic sum operations, condition-based sums, and more complex aggregations like the product sum or subtotals that can selectively ignore hidden rows or filter-applied rows.<\/span><\/p>\n\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    SUM()<\/span><\/td>\nAdds all numbers in a specified range or list of arguments.<\/span><\/td>\n=SUM(A1:A10)<\/span><\/td>\nSums the values in cells A1 through A10<\/span><\/td>\n<\/tr>\n
    SUMIFS()<\/span><\/a><\/td>\nSums the values in a range based on multiple criteria.<\/span><\/td>\n=SUMIFS(B1:B10, A1:A10, “>5”)<\/span><\/td>\nSums values in B1:B10 where corresponding A1:A10 values are greater than 5<\/span><\/td>\n<\/tr>\n
    SUMPRODUCT()<\/span><\/a><\/td>\nCalculates the sum of the products of corresponding numbers in one or more arrays.<\/span><\/td>\n=SUMPRODUCT(A1:A10, B1:B10)<\/span><\/td>\nSums the products of corresponding values in ranges A1:A10 and B1:B10<\/span><\/td>\n<\/tr>\n
    SUBTOTAL()<\/span><\/td>\nCalculates a subtotal using a specified function over a range, optionally ignoring hidden rows.<\/span><\/td>\n=SUBTOTAL(9, A1:A10)<\/span><\/td>\nReturns the sum of A1:A10, ignoring rows hidden by a filter if applied<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Lookups<\/span><\/h4>\n

    Lookup functions are essential for searching and retrieving data from different parts of a spreadsheet. They provide a powerful way to access and compare data across tables and ranges.<\/span><\/p>\n\n\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    LOOKUP()<\/span><\/a><\/td>\nThe LOOKUP function searches for a value in a range and returns a corresponding value from another range.<\/span><\/span><\/td>\n=LOOKUP(“Product”, A1:A10, B1:B10)<\/span><\/span><\/td>\nFinds “Product” in A1 <\/span><\/span>and returns the corresponding value from B1<\/span>
    <\/span><\/td>\n<\/tr>\n
    VLOOKUP()<\/span><\/a><\/td>\nSearches for a value in the first column of a table and returns a value in the same row from a specified column.<\/span><\/td>\n=VLOOKUP(“Value”, A1:B10, 2, FALSE)<\/span><\/td>\nValue from the second column where “Value” is found in the first column<\/span><\/td>\n<\/tr>\n
    HLOOKUP()<\/span><\/a><\/td>\nSearches for a value in the first row of a table and returns a value in the same column from a specified row.<\/span><\/td>\n=HLOOKUP(“Value”, A1:J2, 2, FALSE)<\/span><\/td>\nValue from the second row where “Value” is found in the first row<\/span><\/td>\n<\/tr>\n
    XLOOKUP()<\/span><\/a><\/td>\nSearches a range or an array for a match and returns the corresponding item from a second range or array.<\/span><\/td>\n=XLOOKUP(“Value”, A1:A10, B1:B10)<\/span><\/td>\nValue from range B1:B10 corresponding to “Value” found in A1:A10<\/span><\/td>\n<\/tr>\n
    INDEX() + MATCH()<\/span><\/td>\nCombines to perform flexible lookups by using MATCH to find the position and INDEX to retrieve the value at that position.<\/span><\/td>\n=INDEX(B1:B10, MATCH(“Value”, A1:A10, 0))<\/span><\/td>\nValue from B1:B10 in the row where “Value” is found in A1:A10<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Round<\/span><\/h4>\n

    Rounding functions in spreadsheets are crucial for managing numerical data, especially when preparing financial reports, performing statistical analyses, or simply ensuring data consistency by limiting the number of decimal places. These functions can round numbers in various ways to meet different requirements.<\/span><\/p>\n\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    ROUND()<\/span><\/td>\nRounds a number to a specified number of digits.<\/span><\/td>\n=ROUND(3.14159, 2)<\/span><\/td>\n3.14<\/span><\/td>\n<\/tr>\n
    ROUNDUP()<\/span><\/td>\nRounds a number up to the nearest specified number of decimal places.<\/span><\/td>\n=ROUNDUP(3.14159, 2)<\/span><\/td>\n3.15<\/span><\/td>\n<\/tr>\n
    ROUNDDOWN()<\/span><\/td>\nRounds a number down to the nearest specified number of decimal places.<\/span><\/td>\n=ROUNDDOWN(3.14159, 2)<\/span><\/td>\n3.14<\/span><\/td>\n<\/tr>\n
    INT()<\/span><\/td>\nRounds a number down to the nearest integer.<\/span><\/td>\n=INT(3.14159)<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”advanced” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Advanced Functions<\/span><\/h3>\n

    Flow Control<\/span><\/h4>\n

    Flow control functions in spreadsheets are designed to enhance decision-making processes within formulas, enabling dynamic responses based on various conditions.<\/span><\/p>\n

    These functions allow users to direct the flow of calculations and handle errors more gracefully, ensuring more robust and flexible spreadsheet models.<\/span><\/p>\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    IF()<\/span><\/a><\/td>\nUses a logical condition to return specified values.<\/span><\/td>\n=IF(ISBLANK(A5), “A5 is blank”, “A5 is not blank”)<\/span><\/td>\n“A5 is blank”<\/span><\/td>\n<\/tr>\n
    IFS()<\/span><\/a><\/td>\nEvaluates multiple conditions, returning a value for the first TRUE condition.<\/span><\/td>\n=IFS(A1 > B1, “1st”, A2 > B2, “2nd”)<\/span><\/td>\n“3rd”<\/span><\/td>\n<\/tr>\n
    SWITCH()<\/span><\/a><\/td>\nChooses a return value from a list based on a comparison with a given value.<\/span><\/td>\n=SWITCH(MID(D3, 1, 5), “World”, “planet”)<\/span><\/td>\n“galaxy”<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”advanced” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Conditional Computation<\/span><\/h4>\n

    Conditional computation functions in spreadsheets are crucial for analyzing data based on specific criteria. These functions allow users to count, sum, and calculate averages for cells that meet certain conditions, making data analysis more efficient and targeted.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    COUNTIF()<\/span><\/a><\/td>\nCounts the number of cells that meet a single condition.<\/span><\/td>\n=COUNTIF(A1:A6, “>5”)<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n
    COUNTIFS()<\/span><\/a><\/td>\nCounts the number of cells that meet a multiple conditions.<\/span><\/td>\n=COUNTIF(A1:A6, “>5”, B1:B6, TRUE)<\/span><\/td>\n2<\/span><\/td>\n<\/tr>\n
    SUMIF()<\/span><\/td>\nSums the values in a range that meet a single criterion.<\/span><\/td>\n=SUMIF(A1:A6, “>5”)<\/span><\/td>\n37<\/span><\/td>\n<\/tr>\n
    SUMIFS()<\/span><\/a><\/td>\nSums the values in a range that meet multiple criteria.<\/span><\/td>\n=SUMIFS(B1:B6, A1:A6, “>5”, D1:D6, “<>Local Group”)<\/span><\/td>\n18<\/span><\/td>\n<\/tr>\n
    AVERAGEIF()<\/span><\/a><\/td>\nCalculates the average of cells that meet a condition.<\/span><\/td>\n=AVERAGEIF(A1:A6, “>5”)<\/span><\/td>\n12.33<\/span><\/td>\n<\/tr>\n
    AVERAGEIFS()<\/span><\/a><\/td>\nCalculates the average of cells that meet multiple conditions.<\/span><\/td>\n=AVERAGEIFS(B1:B6, A1:A6, “>5”, D1:D6, “<>Local Group”)<\/span><\/td>\n9<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”advanced” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Custom Function Creation<\/span><\/h4>\n

    Custom function creation in spreadsheets, particularly in Excel, allows users to define their own reusable functions to simplify complex calculations, improve readability, and reduce errors. This is achieved through the use of the <\/span>LAMBDA<\/span> and <\/span>LET<\/span> functions, which enhance formula efficiency and capability.<\/span><\/p>\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    LAMBDA()<\/span><\/td>\nAllows the creation of custom, reusable functions without VBA. Users can define their own parameters and calculations within a single formula.<\/span><\/td>\n=LAMBDA(x, x^2)(5)<\/span><\/td>\n25 (Squares the input value)<\/span><\/td>\n<\/tr>\n
    LET()<\/span><\/td>\nAssigns names to calculation results within a formula. This can reduce repetition of complex expressions and improve performance.<\/span><\/td>\n=LET(x, 5, x^2)<\/span><\/td>\n25 (Assigns 5 to x, then squares x)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”advanced” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Indirect Cell\/Range Reference<\/span><\/h4>\n

    Indirect cell\/range reference functions in spreadsheets allow for dynamic referencing of cells and ranges. This can be particularly useful in scenarios where cell references need to change based on the contents of other cells or complex data manipulation tasks.<\/span><\/p>\n\n\n\n\n\n\n
    Function<\/span><\/td>\nDescription<\/span><\/td>\nExample<\/span><\/td>\nExample Result<\/span><\/td>\n<\/tr>\n
    INDIRECT()<\/span><\/a><\/td>\nReturns the reference specified by a text string. This allows for referencing cells dynamically.<\/span><\/td>\n=INDIRECT(“A1”)<\/span><\/td>\nValue in cell A1<\/span><\/td>\n<\/tr>\n
    ADDRESS()<\/span><\/a><\/td>\nReturns a cell address as a text, based on row and column numbers.<\/span><\/td>\n=ADDRESS(1, 1)<\/span><\/td>\n“A1”<\/span><\/td>\n<\/tr>\n
    OFFSET()<\/span><\/a><\/td>\nReturns a reference shifted a certain number of rows and columns from a starting cell reference.<\/span><\/td>\n=OFFSET(A1, 1, 1)<\/span><\/td>\nValue in B2 (one row down and one column right from A1)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”error” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Error handling functions<\/h3>\n

    These error-handling functions are essential to create robust sheets, as they allow you to identify and manage errors effectively. You can use them to check for specific error types, replace errors with meaningful messages, or perform conditional operations based on error values.<\/p>\n\n\n\n\n\n\n\n\n\n\n
    Function<\/th>\nDescription<\/th>\nExample<\/th>\nExample Result<\/th>\n<\/tr>\n<\/thead>\n
    IFERROR()<\/a><\/td>\nReturns a specified value if the formula evaluates to an error.<\/td>\n=IFERROR(A5 \/ A5, \"Error detected\")<\/code><\/td>\n“Error detected”<\/td>\n<\/tr>\n
    IFNA()<\/a><\/td>\nReturns a specified value if the formula evaluates to #N\/A<\/code>.<\/td>\n=IFNA(VLOOKUP(A1, B1:B10, 1, FALSE), \"Not found\")<\/code><\/td>\n“Not found”<\/td>\n<\/tr>\n
    ISERROR()<\/td>\nChecks if a value is an error.<\/td>\n=ISERROR(A1\/B1)<\/code><\/td>\nTRUE<\/td>\n<\/tr>\n
    ISERR()<\/td>\nChecks if a value is any error except #N\/A<\/code>.<\/td>\n=ISERR(A1\/B1)<\/code><\/td>\nTRUE<\/td>\n<\/tr>\n
    ISNA()<\/td>\nChecks if a value is the #N\/A<\/code> error.<\/td>\n=ISNA(VLOOKUP(A1, B1:B10, 1, FALSE))<\/code><\/td>\nTRUE<\/td>\n<\/tr>\n
    ERROR.TYPE()<\/td>\nReturns a number corresponding to the type of error.<\/td>\n=ERROR.TYPE(A1\/B1)<\/code><\/td>\n2 (#DIV\/0!<\/code> error)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text module_id=”financial” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Financial Analysis<\/span><\/h3>\n

    Finance<\/span><\/h4>\n

    Finance functions in spreadsheets are pivotal for analyzing investments, loans, and other financial scenarios. They enable users to calculate payments, interest, future value, net present value, yield, price, internal rate of return, and the number of payment periods for various financial products.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Function<\/b><\/td>\nDescription<\/b><\/td>\nExample<\/b><\/td>\nExample Result<\/b><\/td>\n<\/tr>\n
    GOOGLEFINANCE()<\/span><\/a><\/td>\nFetches current or historical securities information from Google Finance.<\/span><\/span><\/td>\n=GOOGLEFINANCE(“GOOG”, “price”, DATE(2021,1,1), DATE(2021,12,31), “DAILY”)<\/span><\/span><\/td>\nHistorical daily closing prices for Google stock<\/span><\/span><\/td>\n<\/tr>\n
    PMT()<\/span><\/td>\nCalculates the periodic payment for a loan or investment.<\/span><\/td>\n=PMT(rate, nper, pv)<\/span><\/td>\nPeriodic payment amount<\/span><\/td>\n<\/tr>\n
    IPMT()<\/span><\/td>\nCalculates the interest payment for a specific period.<\/span><\/td>\n=IPMT(rate, per, nper, pv)<\/span><\/td>\nInterest payment for the period<\/span><\/td>\n<\/tr>\n
    PPMT()<\/span><\/td>\nCalculates the principal payment for a specific period.<\/span><\/td>\n=PPMT(rate, per, nper, pv)<\/span><\/td>\nPrincipal payment for the period<\/span><\/td>\n<\/tr>\n
    FV()<\/span><\/td>\nCalculates the future value of an investment or loan.<\/span><\/td>\n=FV(rate, nper, pmt)<\/span><\/td>\nFuture value of the investment or loan<\/span><\/td>\n<\/tr>\n
    NPV()<\/span><\/td>\nCalculates the net present value of an investment.<\/span><\/td>\n=NPV(rate, value1, value2,…)<\/span><\/td>\nNet present value of the cash flows<\/span><\/td>\n<\/tr>\n
    YIELD()<\/span><\/a><\/td>\nCalculates the yield of a security that pays periodic interest.<\/span><\/td>\n=YIELD(settlement, maturity, rate, pr, redemption)<\/span><\/td>\nAnnual interest rate of the security<\/span><\/td>\n<\/tr>\n
    PRICE()<\/span><\/td>\nCalculates the price per $100 face value of a security.<\/span><\/td>\n=PRICE(settlement, maturity, rate, yld, redemption)<\/span><\/td>\nPrice per $100 face value of the security<\/span><\/td>\n<\/tr>\n
    IRR()<\/span><\/td>\nCalculates the internal rate of return of a series of cash flows.<\/span><\/td>\n=IRR(values)<\/span><\/td>\nInternal rate of return for the cash flows<\/span><\/td>\n<\/tr>\n
    NPER()<\/span><\/td>\nCalculates the number of periods for a loan or investment.<\/span><\/td>\n=NPER(rate, pmt, pv)<\/span><\/td>\nNumber of periods to pay off the loan or reach the financial goal<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"

    \u200bThis cheat sheet simplifies the functionalities and capabilities of Google Sheets and Excel, providing a comprehensive guide to master these tools. You’ll discover a wide range of topics, from basic mathematical operations to complex financial calculations, ensuring you can navigate your data with ease and precision. Explore advanced features like custom function creation, data manipulation, […]<\/p>\n","protected":false},"author":2,"featured_media":44471,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"DEFINITIONS<\/b>\n\nThis cheat sheet describes the behavior of the Google Sheets & Excel \/ Microsoft Office 360 Cloud.<\/span>\n\nSpreadsheet<\/b>: An application, like Microsoft Excel, where you can store data, perform calculations, and organize information. Workbook: A file containing a collection of one or more worksheets.<\/span>\n\nWorksheet<\/b>: A single page in a workbook. It is a grid of cells arranged in rows and columns.<\/span>\n\nCell<\/b>: A rectangular box in a worksheet that can store a data value, a formula, or other content.<\/span>\n\nFormula<\/b>: A piece of code to perform a calculation. Formulas start with an equals sign (=), and contain functions, mathematical operators, values, and cell references.<\/span>\n\nCell reference<\/b>: The location of a cell. The column is described with letters and the row is described with numbers. For example, the cell in the 4th column, 7th row would be denoted D7.<\/span>\n\nCell range<\/b>: A group of adjacent cells in a worksheet. A cell range is typically referred to by its upper-left and lower-right cells, such as A1:C3, referring to the cells in columns A, B, and C and rows 1 through 3. You can use cell ranges to perform calculations on\u00a0<\/span>multiple cells at once or to apply formatting to a group of cells.<\/span>\n\n \n\n\n\n\n\n\n
    <\/td>\nA<\/span><\/td>\nB<\/span><\/td>\nC<\/span><\/td>\n<\/tr>\n
    1<\/span><\/td>\nCell A1<\/span><\/td>\nCell B1<\/span><\/td>\nCell C1<\/span><\/td>\n<\/tr>\n
    2<\/span><\/td>\nCell A2<\/span><\/td>\nCell B2<\/span><\/td>\nCell C2<\/span><\/td>\n<\/tr>\n
    3<\/span><\/td>\nCell A3<\/span><\/td>\nCell B3<\/span><\/td>\nCell C3<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nGETTING HELP<\/b>\n\nHow to add a comment to a cell<\/b>\n\nYou can get help by accessing the help menu<\/span>\n
      \n \t
    1. Open Microsoft Excel<\/span><\/li>\n \t
    2. Click on the Help menu at the top of the screen.<\/span><\/li>\n \t
    3. In the Help menu, you will see various options for getting help, including a search bar where you can enter keywords to search for specific topics.<\/span><\/li>\n \t
    4. You can also click on the Help button to open the Help pane, where you can browse through various topics and find answers to common questions.<\/span><\/li>\n<\/ol>\n \n\nHow to add a comment to a cell<\/b>\n
        \n \t
      1. Click on the cell where you want to add a commenT.<\/span><\/li>\n \t
      2. Right click or CTRL+click on the cell and select the New Comment option from the context menu. You can also click on the Insert menu then New Comment\"<\/span><\/li>\n \t
      3. This will open a small text box next to the cell, where you can type your comment.\u00a0<\/span><\/li>\n \t
      4. Once you have entered your comment, click the green arrow button to save it.<\/span><\/li>\n<\/ol>\n \n\nCELLS AND RANGES<\/b>\n\nSpecifying cell locations with column letter, row number format<\/b>\n\n=B2 Here we refer to the cell in column B, row 2<\/span><\/i>\n\nSpecifying absolute cell references with $ prefixes<\/b>\n\nThe $ symbol before the column letter and\/or row number tells Excel that the reference is absolute and should not change when the formula is copied or moved to another cell. The following examples all specify column B, row 2.<\/span>\n\n=$B$2 <\/b>Column and row references are both absolute<\/span>\n\n=$B2 <\/b>Column reference is absolute, row reference is relative<\/span>\n\n=B$2 <\/b>Column reference is relative, row reference is absolute<\/span>\n\nSpecifying ranges with the start:end format<\/b>\n\nThe start:end format is a convenient way to specify a range of cells in a formula.<\/span>\n\nHere is an example of start:end format when using the SUM() formula:<\/span>\n\n=SUM(B2:B5)<\/span>\n\n \n\nEXAMPLE DATASET<\/b>\n\nThroughout most of this cheat sheet, we ll be using this dummy dataset of 5 columns and 6 rows.<\/span>\n\n\n\n\n\n\n\n\n\n
        <\/td>\nA<\/b><\/td>\nB<\/b><\/td>\nC<\/b><\/td>\nD<\/b><\/td>\nE<\/b><\/td>\n<\/tr>\n
        1<\/b><\/td>\n1<\/span><\/td>\n2<\/span><\/td>\n6<\/span><\/td>\nWorld<\/span><\/td>\n1\/1\/2023<\/span><\/td>\n<\/tr>\n
        2<\/b><\/td>\n3<\/span><\/td>\n3<\/span><\/td>\n21<\/span><\/td>\nSolar System<\/span><\/td>\n1\/2\/2023<\/span><\/td>\n<\/tr>\n
        3<\/b><\/td>\n6<\/span><\/td>\n5<\/span><\/td>\n28<\/span><\/td>\nMilky Way<\/span><\/td>\n1\/3\/2023<\/span><\/td>\n<\/tr>\n
        4<\/b><\/td>\n10<\/span><\/td>\n7<\/span><\/td>\n301<\/span><\/td>\nLocal Group<\/span><\/td>\n1\/4\/2023<\/span><\/td>\n<\/tr>\n
        5<\/b><\/td>\n<\/td>\n11<\/span><\/td>\n325<\/span><\/td>\nLaniakea Supercluster<\/span><\/td>\n1\/5\/2023<\/span><\/td>\n<\/tr>\n
        6<\/b><\/td>\n21<\/span><\/td>\n13<\/span><\/td>\n496<\/span><\/td>\nUniverse<\/span><\/td>\n1\/6\/2023<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nOPERATORS<\/b>\n\nArithmetic operators<\/b>\n
          \n \t
        • =A2 + A3 Add two values with +. This example returns 3 + 6 = 9<\/span><\/li>\n \t
        • =A4 - B4 Subtract a value from another with .This example returns 10 - 7 = 3<\/span><\/li>\n \t
        • =A6 * B1 Multiply two values with *. This example returns 21 * 2 = 42<\/span><\/li>\n \t
        • =C3 \/ B4 Divide two values with \/. This example returns 28 \/ 7 = 4<\/span><\/li>\n \t
        • =C5% Convert a value to a percentage with . This example returns 3.25<\/span><\/li>\n \t
        • =B1 ^ C1 Raise a value to power with . This example returns 2 ^ 6 = 64<\/span><\/li>\n<\/ul>\n \n\nNumeric comparison operators<\/b>\n\nTest for equality with =<\/b>\n\n=A1 = B1 Returns 1 = 2 which is FALSE<\/span>\n\n=A2 = B2 Returns 3 = 3 which is TRUE<\/span>\n\nTest greater than with ><\/b>\n\n=A3 > = B3 Returns 5 > 5 which is TRUE<\/span>\n\n=A2 > B2 Returns 3 > 3 which is FALSE<\/span>\n\nTest less than with <<\/b>\n\n=A1 < B1 Returns 1 < 2 which is TRUE<\/span>\n\n=A2 < B2 Returns 3 < 3 which is FALSE<\/span>\n\nTest for inequality with <><\/b>\n\n=A1 < > B1 Returns 1 < > 2 which is TRUE<\/span>\n\n=A2 < > B2 Returns 3 < > 3 which is FALSE<\/span>\n\nTest greater than or equal to with >=<\/b>\n\n=A3 > = B3 Returns 6 > =5 which is TRUE<\/span>\n\n=A2 > B2 Returns 3 > = 3 which is TRUE<\/span>\n\nTest less than or equal to with <=<\/b>\n\n=A1 < = B1 Returns 1 < = 2 which is TRUE<\/span>\n\n=A2 < = B2 Returns 3 < = 3 which is TRUE<\/span>\n\n \n\nLOGICAL FUNCTIONS<\/b>\n\nLogical NOT with NOT()<\/b>\n\n=NOT(A1 = B1) Returns NOT(1 = 2) which is TRUE<\/span>\n\n=NOT(A1 = B1) Returns NOT(2 = 2) which is FALSE<\/span>\n\n \n\nLogical OR with OR()<\/b>\n\n=OR(A1 > 10, B1 < 20) Returns OR(1 > 10, 2 < 20) which is TRUE<\/span>\n\n=OR(A1 < 2, B1 < 20) Returns OR(1 < 2, 2 < 20) which is TRUE<\/span>\n\nLogical AND with AND()<\/b>\n\n=AND(A1 > 10, B1 < 20) Returns AND(1 > 10, 2 < 20) which is FALSE<\/span>\n\n=AND(A1 < 2, B1 < 20) Returns AND(1 < 2, 2 < 20) which is TRUE<\/span>\n\nLogical XOR with XOR()<\/b>\n\n=XOR(A1 > 10, B1 < 20) Returns XOR(1 > 10, 2 < 20) which is TRUE<\/span>\n\n=XOR(A1 > 10, B1 > 20) Returns XOR(1 > 2, 2 > 20) which is FALSE<\/span>\n\nDATA TYPES<\/b>\n
            \n \t
          • =ISNUMBER(A1) Checks if a cell is a number. Returns TRUE<\/span><\/li>\n \t
          • =ISTEXT(D1) Checks if a cell is a text. Returns TRUE<\/span><\/li>\n \t
          • =ISLOGICAL(A1) Checks if a cell is a boolean. Returns FALSE<\/span><\/li>\n \t
          • =ISLOGICAL(A1=A1) Checks if a cell is a boolean. Returns TRUE<\/span><\/li>\n \t
          • =N(E1) Converts to number. Returns 44927: the serial date the date as a number, counting Dec 31st 1899 as 1<\/span><\/li>\n \t
          • =N(D1) Converts to number. Returns an error, since it s not a number<\/span><\/li>\n \t
          • =ALUETOTEXT(A1) Convert to text. Returns \u201c1\u201d<\/span><\/li>\n \t
          • =TEXT(C6, \"0.00E+0\") Convert to formatted text. Returns \"4.96E+2\u201d<\/span><\/li>\n \t
          • =DATEALUE(\"1\/1\/2022\") Convert text to serial. Returns 44927: the serial date<\/span><\/li>\n<\/ul>\n \n\nCOUNTING DATA<\/b>\n
              \n \t
            • =COUNT(A5:E5) Returns 3: the number of cells in the range containing numbers, dates and currencies.<\/span><\/li>\n \t
            • =COUNTA(A5:E5) Returns 4: the number of cells in the range that aren't empty<\/span><\/li>\n \t
            • =COUNTBLANK(A5:E5) Returns 1: the number of cells that are empty or contain the empty string (\"\")<\/span><\/li>\n<\/ul>\n \n\nMATH FUNCTIONS<\/b>\n\n=LOG(100, 10) Returns 2: the base 10 logarithm of 100<\/span>\n\n=EXP(2) Returns e^2 = 7.398<\/span>\n\n=MAX(A1:A6, C1:C3, 12) Returns 28: the largest value in all cell ranges or values inputted<\/span>\n\n=MIN(A1:A6, C1:C3, 12) Returns 1: the smallest value in all cell ranges or values inputted<\/span>\n\n=MAXA(A1:A6, C1:C3, FALSE) Returns same as MAX(), except TRUE is valued at 1 and FALSE is valued at 0<\/span>\n\n=MINA(A1:A6, C1:C3, FALSE) Returns same as MIN(), except TRUE is valued at 1 and FALSE is valued at 0<\/span>\n\n=SUM(A1:A6, C1:C3, 12) Returns 108: the total of all cell ranges or values inputted<\/span>\n\n=AERAGE(A1:A6, C1:C3, 12) Returns 12: the mean of all cell ranges or values inputted<\/span>\n\n=MEDIAN(A1:A6, C1:C3, 12) Returns 10: the median of all cell ranges or values inputted<\/span>\n\n=PERCENTILE.INC(C1:C6, 0.25) Returns 22.75: the 25th percentile of the cell range<\/span>\n\n=ROUND(PI(), 2) Returns 3.14: pi rounded to 2 decimal places<\/span>\n\n=CEILING(PI(), 0.1) Returns 3.2: pi rounded upwards to the nearest 0.1<\/span>\n\n=FLOOR(PI(), 0.1) Returns 3.1: pi rounded downwards to the nearest 0.1<\/span>\n\n=AR.S(B1:B6) Returns 19.37: sample variance of the cell range<\/span>\n\n=STDE.S(B1:B6) Returns 4.40: sample standard deviation of the cell range<\/span>\n\n \n\nFLOW CONTROL<\/b>\n\nUse a logical condition to determine the return value with IF()<\/b>\n\n=IF(cond, return_if_true, return_if_false)<\/span>\n\n=IF(ISBLANK(A5), \"A5 is blank\", \"A5 is not blank\") Returns \"A5 is blank\"<\/span>\n\nTakes a logical condition, cond, as its first argument. If cond is TRUE, IF() returns the value specified in the second argument<\/span>\n\n(return_if_true); if cond is TRUE, IF() returns the value specified in the third argument (return_if_false).<\/span>\n\nUse multiple logical conditions to determine the return value with IFS()<\/b>\n\n=IFS(cond1, return1, cond2, return2)<\/span>\n\n=IFS(A1 > B1, \"1st\", A2 > B2, \"2nd\", A3 > B3, \"3rd\") Returns \"3rd\"<\/span>\n\nSimilar to IF(), but allowing multiple pairs of logical conditions and return values. If the first condition, cond1, is TRUE then the<\/span>\n\nfunction returns the first return value, return1. If the second condition, cond2 is TRUE, the function returns the second return value; <\/span>and so on.<\/span>\n\n \n\nProvide a default value in case of errors with IFERROR()<\/b>\n\n=IFERROR(value, value_if_error)<\/span>\n\n=IFERROR(A5 \/ A5, 1) ivision of two missing values gives an error; this returns 1<\/span>\n\nIf the first input does not result in an error then it is returned. If it does result in an error, the second input is returned.<\/span>\n\nChoose a return value based on a table of inputs with SWITCH()<\/b>\n\n=SWITCH(value, choice1, return1, choice2, return2, ...)<\/span>\n\n=SWITCH(MID(D3, 1, 5), \"World\", \"planet\", \"Solar\", \"planetary system\", \"Milky\", \"galaxy\", \"Local\", \"galaxy group\") Returns \"galaxy\"<\/span>\n\nTakes a value as its first argument, followed by pairs of choices and return values. If the value matches the first choice, the function <\/span>returns the first return value; if the value matches the second choice, the function returns the second return value; and so on. If no <\/span>values match, the function returns an error.<\/span>\n\n \n\nCONDITIONAL COMPUTATION<\/b>\n\nGet the number of cells that meet a condition with COUNTIF()<\/b>\n\n=COUNTIF(A1:A6, \">5\") Returns 3: the number of cells greater than 5, ignoring blanks<\/span>\n\n=COUNTIF(D1:D6, \"Milky Way\") Returns 1: the number of cells equal to \u201cMilky Way\u201d<\/span>\n\n \n\nCalculate the total of cells meeting conditions with SUMIF() and SUMIFS()<\/b>\n\n=SUMIF(A1:A6, \">5\") Returns 37: the sum of elements in A1 to A6 filtered with values greater than 5<\/span>\n\n=SUMIF(A1:A6, \">5\", B1:B6) Returns 25: the sum of elements in B1 to B6 corresponding to values in A1 to A6 that are greater than 5<\/span>\n\n=SUMIFS(B1:B6, A1:A6, \">5\", D1:D6, \"<>Local Group\") Returns 18: the sum of B1:B6 where A1:A6 is greater than 5 and D1:D6 is not equal to \u201cLocal Group\u201d<\/span>\n\n \n\nCalculate the mean of cells meeting conditions with AERAGEIF() & AERAGEIFS()<\/b>\n\n=AERAGEIF(A1:A6, \">5\") Returns 12.33: the mean of elements in A1 to A6 filtered with values greater than 8<\/span>\n\n=AERAGEIF(A1:A6, \">5\", B1:B6) Returns 8.33: the mean of elements in B1 to B6 corresponding to values in A1 to A6 that are greater than 5<\/span>\n\n=AERAGEIFS(B1:B6, A1:A6, \">5\", D1:D6, \"<>Local Group\") Returns 9: the mean of B1:B6 where A1:A6 is greater than 5 and D1:D6 is not equal to \u201cLocal Group\u201d<\/span>\n\n \n\nTEXT FUNCTIONS AND OPERATORS<\/b>\n\nBasics<\/b>\n\n=LEN(D5) Returns the length of a string in characters. This example returns 28.<\/span>\n\n \n\nCombining and splitting strings<\/b>\n\n=\"Hello \" & D1 & \"!\" Returns \"Hello World!\u201d<\/span>\n\n=REPT(D6, 3) Repeats text. This example returns \"UniverseUniverseUniverse\u201d<\/span>\n\n=TEXTSPLIT(D4, \"o\") Splits a string on a delimiter. This example returns \"L\", \"cal Gr\", \"up\" in 3 cells: \"Local Group\" split on the letter \"o\u201d<\/span>\n\n=TEXTSPLIT(D5, {\"a\",\"u\"}) Splits a string on a delimiter. This example returns \"L\", \"ni\", \"ke\", \"S\", \"percl\", \"ster\" in 6 cells: \"Laniakea Supercluster\" split on the letter \"a\" or the letter \"u\".<\/span>\n\n \n\nMutating strings<\/b>\n\n=MID(text, start, [length]) Extracts a substring starting at the position specified in the second argument and with the length specified in the third argument. For example =MID(D6, 4, 5) Returns \"verse\u201d<\/span>\n\n=UPPER(text) Converts the text to uppercase. For example =UPPER(D3) Returns \"MILKY WAY\u201d<\/span>\n\n=LOWER(text) Converts the text to lowercase. For example =LOWER(D3) Returns \"milky way\u201d<\/span>\n\n=PROPER(text) Converts the text to title case. For example =PROPER(\"milky way\") Returns \"Milky Way\"<\/span>\n\n \n\nDATA MANIPULATION<\/b>\n\n=FILTER(A1:B6, C1:C6>100) Gets a subset of the cell range in the first input that meets the condition in the second input.<\/span>\n\n=SORT(A1:E6, 4) Returns the dataset with rows in alphabetical order of the fourth column. Sorts the rows of the data according to values in specified columns.<\/span>\n\n=SORTBY(A1:E6, D1:D6) Returns the same as the SORT() example. Alternate, more flexible, syntax for sorting. Rather than specifying the column number, you specify an array to sort by.<\/span>\n\n=UNIQUE(A1:A6) Gets a list of unique values from the specified data.<\/span>\n\n=SEQUENCE(5, 1, 3, 2) Returns 5 rows and 1 column containing the values 3, 5, 7, 9, 11. Generates a sequence of numbers,starting at the specified start value and with the specified step size.<\/span>\n\n \n\nSUM - These functions help you sum values in range<\/b>\n\n\n\n\n\n\n
              =SUM<\/b><\/td>\nSums the value in a range<\/span><\/td>\n=SUM(number1,[sumber2]...)<\/span><\/td>\n<\/tr>\n
              =SUMIFS<\/b><\/td>\nSums the values in a range that meet multiple criteria<\/span><\/td>\n=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,criteria2],...)<\/span><\/td>\n<\/tr>\n
              =SUMPRODUCT<\/b><\/td>\nCalculates the sum of the products of corresponding arrays or ranges.<\/span><\/td>\n=SUMPRODUCT(array1, [array2], [array3], \u2026)<\/span><\/td>\n<\/tr>\n
              =SUBTOTAL<\/b><\/td>\nReturns a subtotal for a range using a specified function (e.g. SUM, AVERAGE, COUNT) and can ignore other situations within the range.\u00a0<\/span><\/td>\n=SUBTOTAL(function_num, ref1, [ref2]...)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nDATES - These functions help you work with dates<\/b>\n\n\n\n\n\n\n\n\n\n\n\n
              =DATE<\/b><\/td>\nReturns the serial number of a date based on the specified year, month, and day<\/span><\/td>\n=DATE(year,month,day)<\/span><\/td>\n<\/tr>\n
              =NETWORKDAYS<\/b><\/td>\nCalculates the number of the whole workdays between two dates, excluding weekends and optionally considering custom holidays.<\/span><\/td>\n=NETWORKDAYS(start_date_,end_date,[holiday])<\/span><\/td>\n<\/tr>\n
              =DATEVALUE<\/b><\/td>\nConverts a date string in text format to a serial number that Excel recognizes as a date.<\/span><\/td>\n=DATEVALUE(date_text)<\/span><\/td>\n<\/tr>\n
              =EOMONTH<\/b><\/td>\nReturns the serial number of the last day of the month before or after a specified number of months.<\/span><\/td>\n=EOMONTH(start_date,months)<\/span><\/td>\n<\/tr>\n
              =EDATE<\/b><\/td>\nReturns the serial number of the date that is a specified number of months before or after the start date.\u00a0<\/span><\/td>\n=EDATE(start_date,months)<\/span><\/td>\n<\/tr>\n
              =NOW<\/b><\/td>\nReturns the current date and time as a serial number.<\/span><\/td>\n=NOW()<\/span><\/td>\n<\/tr>\n
              =TODAY<\/b><\/td>\nReturns to current date as a serial number (time is set to 0:00:00)<\/span><\/td>\n=TODAY()<\/span><\/td>\n<\/tr>\n
              =MONTH<\/b><\/td>\nReturns the month component (a number between 1 and 12) of a date.\u00a0<\/span><\/td>\n=MONTH(serial_number)<\/span><\/td>\n<\/tr>\n
              =YEAR<\/b><\/td>\nReturns the year component of a date as a four-digit number.\u00a0<\/span><\/td>\n=YEAR(serial_number)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nCONDITIONAL\/BOOLEAN -<\/b> These functions return a value if true\/false<\/b>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
              =IF<\/b><\/td>\nReturns one value if a given condition is TRUE, and another if it\u2019s FALSE.<\/span><\/td>\n=IF(logical_test, value_if_true,value_if_false)<\/b><\/td>\n<\/tr>\n
              =AND<\/b><\/td>\nReturns TRUE if all the supplied conditions are TRUE; otherwise, it returns FALSE.<\/span><\/td>\n=AND(condition1, [condition2], \u2026)<\/b><\/td>\n<\/tr>\n
              =OR<\/b><\/td>\nReturns TRUE if any of the supplied conditions are TRUE; otherwise, it returns FALSE.<\/span><\/td>\n=OR(condition1, [condition2], \u2026)<\/b><\/td>\n<\/tr>\n
              =SUMIF(S)<\/b><\/td>\nAdds the value in a range that meets a single criterion.\u00a0<\/span><\/td>\n=SUMIF(range, criteria, [sum_range])<\/b><\/td>\n<\/tr>\n
              =IFERROR<\/b><\/td>\nReturns a value if a formula evaluates to an error, otherwise, it returns the result of the formula.\u00a0<\/span><\/td>\n=IFERROR(value, value_if_error)<\/b><\/td>\n<\/tr>\n
              =COUNTIF(S)<\/b><\/td>\nCounts the number of cells in a range that meet a single criterion.\u00a0<\/span><\/td>\n=COUNTIF(range, criteria)<\/b><\/td>\n<\/tr>\n
              =AVERAGEIF(S)<\/b><\/td>\nCalculates the average of the values in a range that meet a single criterion.<\/span><\/td>\n=AVERAGEIF(range, criteria, [average_range])<\/b><\/td>\n<\/tr>\n
              =ISBLANK<\/b><\/td>\nChecks if a reference is empty (blank) and returns TRUE or FALSE.<\/span><\/td>\n=ISBLANK(reference)<\/b><\/td>\n<\/tr>\n
              =ISNUMBER<\/b><\/td>\nChecks if the value is a numeric value and returns TRUE or FALSE.<\/span><\/td>\n=ISNUMBER(value)<\/b><\/td>\n<\/tr>\n
              =COUNTIF<\/b><\/td>\nCounts the number of non-empty cells in a range that meet a single criterion.\u00a0<\/span><\/td>\n=COUNTIF(range, criteria)<\/b><\/td>\n<\/tr>\n
              =COUNTA<\/b><\/td>\nCounts the number of non-empty cells in a range.<\/span><\/td>\n=COUNTA(value1, [value2], ...)<\/b><\/td>\n<\/tr>\n
              =NOT<\/b><\/td>\nReverses the logical value of a given conditions (TRUE becomes FALSE, and vice versa)<\/span><\/td>\n=NOT(logical_value)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nTEXT - These functions are helpful when dealing with text<\/b>\n\n \n\n\n\n\n\n\n\n\n\n\n\n\n
              =LEFT<\/b><\/td>\nReturns a specified number of characters from the beginning (lefT) of a text string.\u00a0<\/span><\/td>\n=LEFT(text,num_chars)<\/span><\/td>\n<\/tr>\n
              =RIGHT<\/b><\/td>\nReturns a specified number of characters from the end (right) of text string.<\/span><\/td>\n=RIGHT(text,num_chars)<\/span><\/td>\n<\/tr>\n
              =UPPER<\/b><\/td>\nConverts all letters in a text string to uppercase<\/span><\/td>\n=UPPER(text)<\/span><\/td>\n<\/tr>\n
              =LOWER<\/b><\/td>\nConverts all letters in a text string to lowercase<\/span><\/td>\n=LOWER(text)<\/span><\/td>\n<\/tr>\n
              =LEN<\/b><\/td>\nReturns the number of characters in a text string.<\/span><\/td>\n=LEN(text)<\/span><\/td>\n<\/tr>\n
              =SEARCH<\/b><\/td>\nReturns the position of a substring within a text string, case-insensitive.<\/span><\/td>\n=SEARCH(find_text, within_text, [start_num1])<\/span><\/td>\n<\/tr>\n
              =PROPER<\/b><\/td>\nCapitalizes the first letter of each word in a text string and converts the rest to lowercase.\u00a0<\/span><\/td>\n=PROPER(text)<\/span><\/td>\n<\/tr>\n
              =TRIM()<\/b><\/td>\nRemoves extra spaces from a text string, leaving only a single space between words.\u00a0<\/span><\/td>\n=TRIM(text)<\/span><\/td>\n<\/tr>\n
              =TEXTJOIN()<\/b><\/td>\nConcatenates text strings from a range or array, with optional delimiter and ignoring empty cells.\u00a0<\/span><\/td>\n=TEXTJOIN(delimiter, ignore_empty, text1, [text2],...))<\/span><\/td>\n<\/tr>\n
              =\u2019<\/b><\/td>\nApostrophe (single quote): Used as a prefix to treat a number or a series of numbers as text, preventing Excel from interpreting it as a formula.<\/span><\/td>\nExample: \u201812345 (The number 12345 is treated as text)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nCUSTOM FUNCTION CREATION - These functions help you create functions<\/b>\n\n\n\n
              =LAMBDA<\/b><\/td>\n=LET<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nINDIRECT CELL\/RANGE REFERENCE - These functions refer to cells, both directly and indirectly<\/b>\n\n\n\n
              =INDIRECT<\/b><\/td>\n=ADDRESS<\/b><\/td>\n=OFFSET<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nLOOKUPS - These functions help you look up values in a range<\/b>\n\n\n\n\n\n\n\n
              =VLOOKUP<\/b><\/td>\nSearches for a value in the first column of a table array and returns a value in the same row from a specified column.\u00a0<\/b><\/td>\n=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/b><\/td>\n<\/tr>\n
              =HLOOKUP<\/b><\/td>\nSearches for a value in the top row of a table array and returns a value from the same column in a specified row.<\/b><\/td>\n=HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/b><\/td>\n<\/tr>\n
              =INDEX\/MATCH<\/b><\/td>\nINDEX returns the value of a cell in an array based on the row and column numbers. MATCH returns the relative position of a specified value in a range.\u00a0<\/b><\/td>\n=INDEX(array, row_num, [column_num1])combined with = MATCH(lookup_value,lookup_array, [match_type])<\/b><\/td>\n<\/tr>\n
              =XLOOKUP<\/b><\/td>\nSearches for a value in a range (vertical or horizontal) and returns a corresponding value from another range.\u00a0<\/b><\/td>\n=XLOOKUP(lookup_value, lookup_array, return_array, (if_if not_found), [match_mode], [search_mode])<\/b><\/td>\n<\/tr>\n
              =GETPIVOTDATA<\/b><\/td>\nRetrieves data from a PivotTable report based on specific criteria.<\/b><\/td>\n=GETPIVOTDATA(data_field, pivot_table, [field1,item1],[field2,item2], \u2026)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nROUND - These functions help you round values<\/b>\n\n\n\n\n\n\n
              =ROUND<\/b><\/td>\nRounds a number to a specified number of digits.\u00a0<\/b><\/td>\n=ROUND<\/b><\/td>\n<\/tr>\n
              =ROUNDUP<\/b><\/td>\nRounds a number up to a specified number of decimal places.\u00a0<\/b><\/td>\n=ROUNDUP<\/b><\/td>\n<\/tr>\n
              =ROUNDDOWN<\/b><\/td>\nRounds a number down to a specified number of decimal places.\u00a0<\/b><\/td>\n=ROUNDDOWN<\/b><\/td>\n<\/tr>\n
              =INT<\/b><\/td>\nRounds a number down to the nearest integer.<\/b><\/td>\n=INT<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nSPILL FUNCTIONS - These functions #SPILL! Into other cells<\/b>\n\n\n\n\n\n\n\n
              =FILTER<\/b><\/td>\nFilters a range of data based on specified criteria and returns the filtered results in a new array.<\/b><\/td>\n=FILTER(array, include, [if_empty[)<\/b><\/td>\n<\/tr>\n
              =UNIQUE<\/b><\/td>\nReturns a list of unique values from a range, removing any duplicates.<\/b><\/td>\n=UNIQUE(array, [by_col], [occurs_once])<\/b><\/td>\n<\/tr>\n
              =SORT<\/b><\/td>\nSorts the contents of a range or array in ascending or descending order.<\/b><\/td>\n=SORT(array, [sort_index], [sort_order], [by_col])<\/b><\/td>\n<\/tr>\n
              =SEQUENCE<\/b><\/td>\nGenerates a sequence of numbers as an array based on the specified parameters.\u00a0<\/b><\/td>\n=SEQUENCE(rows, [cols],[starts],[step)<\/b><\/td>\n<\/tr>\n
              =TRANSPOSE<\/b><\/td>\nTransposes the rows and columns of a range or array.\u00a0<\/b><\/td>\n=TRANSPOSE(array)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nGENERAL MATH - These functions are basic math operators<\/b>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
              +<\/b><\/td>\nCalculates the sum of two numbers<\/b><\/td>\nA1 +B1<\/b><\/td>\n<\/tr>\n
              -<\/b><\/td>\nCalculates the difference of two numbers<\/b><\/td>\nA1 - B1<\/b><\/td>\n<\/tr>\n
              *<\/b><\/td>\nMultiplies two or more numbers together<\/b><\/td>\nA1 * B1<\/b><\/td>\n<\/tr>\n
              \/<\/b><\/td>\nPerforms division between two numbers<\/b><\/td>\nA1\/B1 (Divides the value in cell A1 by the value in cell B1)<\/b><\/td>\n<\/tr>\n
              =AVERAGE<\/b><\/td>\nCalculates the arithmetic mean of a range of numbers<\/b><\/td>\n=AVERAGE(A1:A10)<\/b><\/td>\n<\/tr>\n
              =MAX<\/b><\/td>\nReturns the largest value from a range of numbers<\/b><\/td>\n=MAX(A1:A10)<\/b><\/td>\n<\/tr>\n
              =MIN<\/b><\/td>\nReturns the smallest value from a range of numbers<\/b><\/td>\n=MIN(A1:A10)<\/b><\/td>\n<\/tr>\n
              =RAND<\/b><\/td>\nGenerates a random number between 0 and 1 each time the worksheet is recalculated<\/b><\/td>\n=RAND()<\/b><\/td>\n<\/tr>\n
              =RANDBETWEEN<\/b><\/td>\nGenerates a random integer number between a specified minimum and maximum value.<\/b><\/td>\n=RANDBETWEEN(1,100)<\/b><\/td>\n<\/tr>\n
              =ABS<\/b><\/td>\nReturns the absolute value of a number (removes the sign)<\/b><\/td>\n=ABS(-5)<\/b><\/td>\n<\/tr>\n
              =MOD<\/b><\/td>\nReturns the remainder after dividing two numbers.\u00a0<\/b><\/td>\n=MOD(10,3)(Returns 1 because 10 divided by 3 leaves a remainder of 1)<\/b><\/td>\n<\/tr>\n
              (=MODE<\/b><\/td>\nReturns the most frequently occurring number in a range of numbers.\u00a0<\/b><\/td>\n=MODE(A1:A10)<\/b><\/td>\n<\/tr>\n
              =POWER<\/b><\/td>\nRaises a number to a specified power.\u00a0<\/b><\/td>\n=POWER(number,power)<\/b><\/td>\n<\/tr>\n
              =<\/b><\/td>\nDivision operator. Performs division between two numbers.<\/b><\/td>\nExample: A1\/B1 (Divides the value in cell A1 by the value in cell B1)<\/b><\/td>\n<\/tr>\n
              =INT<\/b><\/td>\nRounds a number down to the nearest integer.\u00a0<\/b><\/td>\n=INT(number)<\/b><\/td>\n<\/tr>\n
              =SQRT<\/b><\/td>\nReturns the square root of a number.\u00a0<\/b><\/td>\n=SQRT(number)<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nFINANCE - These functions are useful in the context of finance<\/b>\n\n\n\n\n\n\n\n\n\n\n\n
              =PMT<\/b><\/td>\nCalculates the periodic payment (annuity) for a loan or investment with a fixed rate and fixed periodic payments.<\/b><\/td>\n=PMT(rate,nper,pv,[fv],[type])<\/b><\/td>\n<\/tr>\n
              =IPMT<\/b><\/td>\nCalculates the interest payment for a specific period in an investment or loan with fixed periodic payments.\u00a0<\/b><\/td>\n=IPMT(rate,nper,pv,[fv],[type])<\/b><\/td>\n<\/tr>\n
              =PPMT<\/b><\/td>\nCalculates the principal payment for a specific period in an investment or loan with fixed periodic payments.\u00a0<\/b><\/td>\n=PPMT(rate,nper,pv,[fv],[type])<\/b><\/td>\n<\/tr>\n
              =FV<\/b><\/td>\nCalculates the future value of an investment or loan with fixed periodic constant payments and a constant interest rate.<\/b><\/td>\n=FV(rate,nper,pmt,[pv],[type])<\/b><\/td>\n<\/tr>\n
              =NPV<\/b><\/td>\nCalculates the net present value of an investment based on a series of cash flows and a discount rate.\u00a0<\/b><\/td>\n=NPV(rate,value1,[value2],...)<\/b><\/td>\n<\/tr>\n
              =YIELD<\/b><\/td>\nCalculates the yield (annual interest rate) of a security that pays periodic interest payments.<\/b><\/td>\n=YIELD(settlement, maturity, rate, pr, redemption, [frequency],[basis])<\/b><\/td>\n<\/tr>\n
              =PRICE<\/b><\/td>\nCalculates the price per $100 face value of a security that pays periodic interest payments.\u00a0<\/b><\/td>\n=PRICE(settlement, maturity, rate, yld, redemption, [frequency],[basis])<\/b><\/td>\n<\/tr>\n
              =IRR<\/b><\/td>\nCalculates the internal rate of return of a series of cash flows that may not be periodic.\u00a0<\/b><\/td>\n=IRR(values, [guess])<\/b><\/td>\n<\/tr>\n
              =NPER<\/b><\/td>\nCalculates the number of periods required to pay off a loan or reach a financial goal based on a fixed interest rate and fixed periodic payments.\u00a0<\/b><\/td>\n=NPER(rate, pmt, pv, [fv], [type])<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nCOMBINE - These functions combine values<\/b>\n\n\n\n\n\n
              =CONCAT<\/b><\/td>\nJoins multiple text strings or values into a single text string.\u00a0<\/b><\/td>\n=CONCAT(value1, [value2], \u2026)<\/b><\/td>\n<\/tr>\n
              =TEXTJOIN()<\/b><\/td>\nConcatenates text strings from a range or array, with optional delimiter and ignoring empty cells.\u00a0<\/b><\/td>\n=TEXTJOIN(delimiter, ignore_empty,text1, [text2], \u2026)<\/b><\/td>\n<\/tr>\n
              =&<\/b><\/td>\nAmpersand operator: Joins two or more text strings or values into a single text string.\u00a0<\/b><\/td>\n=\u201dHello\u201d&\u201dWorld\u201d (Returns \u201cHello world\u201d)\u00a0<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n ","_et_gb_content_width":"","footnotes":""},"categories":[51],"tags":[39,48,28],"class_list":["post-45581","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-features","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/45581","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=45581"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/45581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/44471"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=45581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=45581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=45581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}